[SOLVED] PHP Sql Syntax Error on Update Query (maybe a reserved word?)

[jaxdevil]

I copied the error and my code below. I do not understand what the problem is? I tried encapsulating the word mod in quotes like this `mod` but that didn't help/ Any ideas?

 

An error has ocured: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '= 'WCCD-WCC650CX1'' at line 1:1064

A

 

<?php
$query = "UPDATE products SET supplier='$supplier',cat='$cat',subcat='$subcat',`man`='$man',`mod`=$mod,name='$name',list='$list',price='$price',specs='$specs',pdf='$pdf',active='$active' WHERE mod= '$mod'";
mysql_query($query) or die( "An error has ocured: " .mysql_error (). ":" .mysql_errno ());
print "<h1>Inventory Update Successful</h1><br>data<br /><br /><em>Updated!</em><br /><br />";
?>

[DarkWater]

Which column gets that value?

[Symmetry]

You're trying to set `mod` with $mod while at the same time match `mod` with $mod in your query? That's likely your problem =]

[DarkWater]

That wouldn't give a syntax error.

[jaxdevil]

The column named mod is the one that has the value of WCCD-WCC650CX1 (the variable for that is $mod), which is why I gathered from the sql error the fault was somewhere around there. I have this code on multiple places in mutliple sites, I modified this one slightly but this portion of the code is almost exactly to the other ones and they all work, I don't know why this one is not. I mean if it is a reserved word I should be able to close it off with the `` at the beginning and end of the word but that isn't working.

 

I appreciate your help

[jaxdevil]

Yes but I use the same variables in many php scripts I have made, this update function I use on multiple things and change the variables for the particular database, but I always use the same variable as the column name, for simplicity and clean coding. I haven't had that problem before. Any ideas?

[DarkWater]

You don't have $mod enclosed in quotes (it's a string).

`mod`='$mod'

[Symmetry]

Okay from what I understand $mod = WCCD-WCC650CX1

 

What is the current value of `mod` in your table before you update it?

 

I'll try to help.

[Symmetry]

You don't have $mod enclosed in quotes (it's a string).

`mod`='$mod'

 

While it's good practice to enclose them in strings, it's not imperative. and the error he's getting it at the end of his query.. this part- WHERE mod= '$mod'

[jaxdevil]

The value before is WCCD-WCC650CX , I was just running the first test of it after copying the code over and modifying it for this purpose. So I just added a 1 on the end of the model number. So the script is trying to change from WCCD-WCC650CX -> WCCD-WCC650CX1

 

Thanks guys!

 

SK

[jaxdevil]

You don't have $mod enclosed in quotes (it's a string).

`mod`='$mod'

 

I thought about that too, thats actually why the quotes are off of it, if you can see I had them on everything (I always put them on everything) but since I couldn't figure it out I just tried every combination of ways to format the string trying to 'get lucky'. So $mod is actually the only one I removed the quotes on. I just put them back and its still the same error.

 

Thanks for the idea though.

 

SK

[Symmetry]

Sometimes I find it helps to echo the $query to see exactly what it's trying to do...

 

if the value of $mod is WCCD-WCC650CX1

and the row `mod`before the update is WCCD-WCC650CX

 

then this would present a problem because you're trying to UPDATE ... WHERE mod= '$mod'

 

in other words WHERE mod=WCCD-WCC650CX1 which I don't think would be found?

 

I would try making another variable here,

 

<?php
$oldmod = substr($mod,0,strlen($mod)-1); // removes the '1' at the end of $mod.
$query = "UPDATE products SET supplier='$supplier',cat='$cat',subcat='$subcat',`man`='$man',`mod`=$mod,name='$name',list='$list',price='$price',specs='$specs',pdf='$pdf',active='$active' WHERE mod= '$oldmod'";
mysql_query($query) or die( "An error has ocured: " .mysql_error (). ":" .mysql_errno ());
print "<h1>Inventory Update Successful</h1><br>data<br /><br /><em>Updated!</em><br /><br />";
?>

[jaxdevil]

Thank you Symmetry!

 

That didn't work, but it put me in the right area to see where the problem is. It was the WHERE statement that was the problem, the mod='$mod' was without quotes, I made it `mod`='$mod' and it works now! YAY!!

 

Thanks everyone for the help, I wouldn't have figured it out with out you.

 

Thanks Symmetry!

[Symmetry]

Glad that worked, nice to see people are here to help.

[jaxdevil]

Oh great, something else now. Well the function is processing, it has no more sql errors, but the data is posting incorrectly. You see the 11 fields, right? Well it is posting the $price variable into the `man` column, and posting the $list variable into the `price` column, and is posting nothing into the `list` column, or rather it is erasing the data that was in the `list` column since it is leaving nothing in that field after the updating has processed.

 

Please look at the revised code and see if you see anything. I put quotes on everything now, just to be safe, but that didn't do it.

 

<?php
$query = "UPDATE products SET `supplier`='$supplier',`cat`='$cat',`subcat`='$subcat',`man`='$man',`mod`='$mod',`name`='$name',`list`='$list',`price`='$price',`specs`='$specs',`pdf`='$pdf',`active`='$active' WHERE `mod`='$oldmod'";
mysql_query($query) or die( "An error has ocured: " .mysql_error (). ":" .mysql_errno ());
print "<h1>Inventory Update Successful</h1><br>data<br /><br /><em>Updated!</em><br /><br />";
?>