UPDATE query doing nothing!

PHP
1

Does anyone know what is wrong with this code. I am getting no errors or anything, the page is simply refreshing without updating anything.

  error_reporting(E_ALL);
  ini_set("display_errors", 1);
include ('../library/config.php');
include ('../library/opendb.php');
$id = $_GET['id'];
if(isset($_POST['submit']))
			  {
			  $postage_id = addslashes($_POST['postage_id']);
			  $country = addslashes($_POST['country']);
			  $band = addslashes($_POST['band']);
			  $start = addslashes($_POST['start']);
			  $end = addslashes($_POST['end']);
			  $postage = addslashes($_POST['postage']);

$postage_result = mysql_query("UPDATE postage SET country='$country', band='$band', start='$start', end='$end', postage='$postage' WHERE id='$postage_id' " ,$conn) or die('Error, query failed');

			  header("location: status.php?id=1");
			  }
			  elseif($id)
			  {
      <?php
			 }
          	$postage_result = mysql_query("SELECT * FROM postage",$conn);
        	while($myrow = mysql_fetch_assoc($postage_result))
             {
			$postage_id = $myrow['id'];
			$country = $myrow['country'];
			$band = $myrow['band'];
			$start = $myrow['start'];
			$end = $myrow['end'];
			$postage = $myrow['postage'];
?>
<?php echo "<tr><td><input name='postage_id' type='hidden' value='".$postage_id."'></input></td>";
	echo "<td><input name='country' type='text' value='".$country."' size='50'></input></td><td><input name='band' type='text' value='".$band . "' size='2'></input></td><td><input name='start' type='text' value='".$start . "' size='2'></input></td><td><input type='text' name='end' value='".$end . "' size='2'></input></td><td><input name='postage' type='text' value='".$postage . "' size='3'></input></td></tr>"; 

			 }
}
?>

I’ve checked over everything so many times but can’t seem to find the problem!!!

2

I’m not saying I know whats wrong, just suggesting how you isolate WHERE your code is going wrong, debugging you might say.

Try this:

Put your sql statement into a variable, then echo it onto the screen, copy it into whatever you use to manage your database and check the results actually bring something from your db.

$sql = "UPDATE postage SET country=‘$country’ …

// a line of debug
echo $sql ;

3

The code you posted probably has a parse error. Looks like you have a stray <?php tag about halfway down.

4

Aargh sorry, I copied it down slightly wrong. This is how it should look:

<?php
error_reporting(E_ALL);
ini_set(“display_errors”, 1);
include (‘…/library/config.php’);
include (‘…/library/opendb.php’);
$id = $_GET[‘id’];
if(isset($_POST[‘submit’]))
{
$postage_id = addslashes($_POST[‘postage_id’]);
$country = addslashes($_POST[‘country’]);
$band = addslashes($_POST[‘band’]);
$start = addslashes($_POST[‘start’]);
$end = addslashes($_POST[‘end’]);
$postage = addslashes($_POST[‘postage’]);

$postage_result = mysql_query("UPDATE postage SET country=‘$country’, band=‘$band’, start=‘$start’, end=‘$end’, postage=‘$postage’ WHERE id=‘$postage_id’ " ,$conn) or die(‘Error, query failed’);

       header("location: status.php?id=1");
       }
       elseif($id)
       {
         $postage_result = mysql_query("SELECT * FROM postage",$conn);
       while($myrow = mysql_fetch_assoc($postage_result))
         {
     $postage_id = $myrow['id'];
     $country = $myrow['country'];
     $band = $myrow['band'];
     $start = $myrow['start'];
     $end = $myrow['end'];
     $postage = $myrow['postage'];

?>
<?php echo “<tr><td><input name=‘postage_id’ type=‘hidden’ value='”.$postage_id.“'></input></td>”;
echo “<td><input name=‘country’ type=‘text’ value='”.$country.“’ size=‘50’></input></td><td><input name=‘band’ type=‘text’ value='”.$band . “’ size=‘2’></input></td><td><input name=‘start’ type=‘text’ value='”.$start . “’ size=‘2’></input></td><td><input type=‘text’ name=‘end’ value='”.$end . “’ size=‘2’></input></td><td><input name=‘postage’ type=‘text’ value='”.$postage . “’ size=‘3’></input></td></tr>”;

      }

}
?>

There was a rogue open bracket and php tag in there, but have corrected this now. Is there anything wrong with this code???

5

You can debug your code. What you basically do is discover how your code is working, instead of assuming how it is working.

You should check the values of your variables. A good way to do this is by using var_dump(). The value should be what you expect. This includes stuff like your sql query. Assign the string to a variable so that you can echo it, and see what it looks like after the variables are expanded.

Check conditional statements too. For example, if statements, and loops. You can echo something inside of them to see if the block of code is ever actually run. For example


if ($foo) {
    echo "entering the if(foo) block'"
}

For debugging an sql query, you might want to use something other than php. Like, phpmyadmin for example. You can just cut and paste the sql query to test the results.

At some point, you will come across a condition, or maybe a value that wasn’t what you expected. If your approach this strategically, you can eliminate large amounts of code as being suspect very quickly, and narrow down to your problem area in short time.

6

What exactly do you mean by assigning the value to a string? Would it not be a case of having:

$postage_result = mysql_query("UPDATE postage SET country=‘$country’, band=‘$band’, start=‘$start’, end=‘$end’, postage=‘$postage’ WHERE id=‘$postage_id’ " ,$conn) or die(‘Error, query failed’);

and then doing echo $postage_result;

and then var_dump($postage_result);

Or am I thinking things are a lot simpler than they are?

7 

$sql = "UPDATE postage SET country='$country', band='$band', start='$start', end='$end', postage='$postage' WHERE id='$postage_id' ";
echo $sql;
$postage_result = mysql_query($sql, $conn) or die('Error, query failed');
8

Thanks! It looks so easy when you do that!

Okay, now I know the problem, but I’m unsure whether this is because I am doing something wrong. The code that I wrote above basically produces a list of all rows in the table as input boxes, and so I wanted all of those rows to be editable and then once the Submit button is pressed all of the rows are updated. However, it seems that with this code only the last of all the rows is being updated.

Is this because it is not possible to do this or is there a different way of doing it? Would I need to put my update query into a WHILE loop to make it work or something?

Thanks for the help so far!

9

If you’re ok with just updating a single record per form submission, you can just give each record it’s own form and submit button.

If you want to be able to edit multiple(potentially all) of the records with a single form submission, then it will be a bit more complicated.

10

Cool!
I’ll go with the simple method!

Thanks again for all the help!

11

You can do it with a loop, but the down side is that if the user changes only 1 out of 100 values, the script won’t know that and will issue 99 unnecessary UPDATE statements. You could get more sophisticated by storing the current value in a hidden input field, comparing the text box and the hidden field and only executing the SQL if the values are different.