Another PHP quandry with updating tables

[Ravinos]

I ran into an interesting problem today when trying to create a query that updates a table with arrays from a form....i thought it would be an easy task so I jumped right into it. The below script is what I came up with thinking that it should work instantly....

Code: Select all

$size = count($_POST['tags']);

$i = 0;
while ($i < $size)
 {
$order= $_POST['order'][$i];
$hpid = $_POST['tags'][$i];
		
$updateposts = "UPDATE posts SET order='$order' WHERE hpid='$hpid' ";
mysql_query($query) or die ("an error has occured!");
++$i;
}

It always resulted in an error. It said there was an error. After doing a little research I cam across this fix

Code: Select all

$size = count($_POST['tags']);

$i = 0;
while ($i < $size)
 {
$order= $_POST['order'][$i];
$hpid = $_POST['tags'][$i];
		
$updateposts = "UPDATE `posts` SET `order`='$order' WHERE `hpid`='$hpid' ";
mysql_query($query) or die ("an error has occured!");
++$i;
}

I am dumbfounded why I need single quotes around the fields. I've never needed them before now. Why are ` needed?

[Jackolantern]

Technically, they are always supposed to be there. That is the way MySQL queries are written. I have never actually seen it cause an error, though, to not use them. My best guess is that one of the form fields had a space or something else that caused confusion to the query, and I guess single-quotes around the values didn't cut it? I really don't get it, either, as single-quotes have usually done the trick just fine for me up to this point.

[Ravinos]

Yeah I've completely rebuilt everything by scratch made sure there were no errant spaces and made sure when the array is generated it doesn't have spaces. I even when and built my own array and assigned it to a variable without passing it through the form processor. I get the same result each time.

[Jackolantern]

So you mean even now, using ` ` you are still getting errors? Or you mean you keep getting errors without them?

[Winawer]

Order is a reserved word in MySQL, so it needs to be in quotes if used as a column (or table, etc.) name.

[Jackolantern]

Order is a reserved word in MySQL, so it needs to be in quotes if used as a column (or table, etc.) name.

Ohhhhh, yeah, I missed that. Ok, that makes sense that it screws up when not in ` `.

@Ravinos:

I highly suggest you change the name of that column in the database. If you use it all over your scripts, do a Find and Replace in your editor (or just a basic Find and manually change it if your editor doesn't support F'n'R) to change all occurrences to the new name.

[Xaleph]

ORDER is a reserved word, but regardless:

Code: Select all

$updateposts = "UPDATE `posts` SET `order`='$order' WHERE `hpid`='$hpid' ";
mysql_query($query) or die ("an error has occured!");

You update a $var called query, however the actual query variable is named $updateposts. Maybe change that as well?