PHP mySql error: 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 'WHERE ProductId='x'' at line x
I have create a 'product.php' file that links succesfully to a mySql database. Also, there are 2 other file: an 'add_product.php' file that adds to mySql database through a form; and, a one_product.php file that edit product existing in the database. The add product works; but the edit returns an error after clicking submit button. Error message: 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 'WHERE ProductId='x'' at line x. (the error was not there when I tested how the files work together with 4 columns before I started the actual work yesterday.)
see this line:
mysql_query("UPDATE products SET ProductName='$ProductName', SupplierId='$SupplierId' ,CategoryId='$CategoryId', QuantityPerUnit='$QuantityPerUnit',UnitPrice='$UnitPrice',UnitsInStock='$UnitsInStock',UnitsOnOrder='$UnitsOnOrder',ReorderLevel='$ReorderLevel',Discontinued='$Discontinued'
,LastModifiedDateTime='$LastModifiedDateTime', WHERE ProductId='$ProductId'")
should be:
mysql_query("UPDATE products SET ProductName='$ProductName', SupplierId='$SupplierId' ,CategoryId='$CategoryId', QuantityPerUnit='$QuantityPerUnit',UnitPrice='$UnitPrice',UnitsInStock='$UnitsInStock',UnitsOnOrder='$UnitsOnOrder',ReorderLevel='$ReorderLevel',Discontinued='$Discontinued'
,LastModifiedDateTime='$LastModifiedDateTime' WHERE ProductId='$ProductId'")
Pratima
also I think ' ' not required for Product Id as it in integer data type
Should be like this
mysql_query("UPDATE products SET ProductName='$ProductName', SupplierId='$SupplierId' ,CategoryId='$CategoryId', QuantityPerUnit='$QuantityPerUnit',UnitPrice='$UnitPrice',UnitsInStock='$UnitsInStock',UnitsOnOrder='$UnitsOnOrder',ReorderLevel='$ReorderLevel',Discontinued='$Discontinued'
,LastModifiedDateTime='$LastModifiedDateTime' WHERE ProductId=$ProductId")
Thank you AngelIII, can't believe I struggled 3 hours for a comma! I also have to thank Ray for the recommended query arrangement. It inspired me that a sql querry function page (another .php file)is a neat idea. I'll post a new question about function page as I'm new to that.