Solved

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

Posted on 2012-12-31
6
765 Views
Last Modified: 2012-12-31
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.)

The three files, plus the includ files are attached for reference.  Please advise what correction should be done
product.php
add-product.php
one-product.php
common.php
0
Comment
Question by:causewaybay
6 Comments
 

Author Comment

by:causewaybay
ID: 38732368
Please also see the attached .sql database for the above .php files
products.sql
0
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 450 total points
ID: 38732381
you have a comma too much before the WHERE ...
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 38732382
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'")
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 39

Expert Comment

by:Pratima Pharande
ID: 38732389
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")
0
 
LVL 110

Assisted Solution

by:Ray Paseur
Ray Paseur earned 50 total points
ID: 38732679
I think it is OK to quote integer fields, but not necessary.  However an extraneous comma anywhere in the query string is trouble.

A good strategy for writing queries is to create a separate variable for the query string.  Then you can visualize the query string in case there is an error.  Here is a code example of how I might do it.
// NOT 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'") 

// BUT THIS
$sql
=
"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'
"
;
$res = mysql_query($sql);
if (!$res)
{
    echo "FAIL: $sql ";
    echo "BECAUSE: " . mysql_error();
    /* HANDLE ERROR CONDITION HERE */
}

Open in new window

Obviously this is untested code (I don't have your data base) but hopefully it makes for a good example of how to write very readable query strings.  Want to find your queries in a large script file?  Look for the equal sign in column 1.  Need to add columns to the query -- easy!  You might want to try deliberately introducing an error into the query and look at the error message output.  I think you'll find that "neatness counts" and pays some big benefits when you're debugging.

Best to all for a good New Year, ~Ray
0
 

Author Closing Comment

by:causewaybay
ID: 38733222
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.
0

Featured Post

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Developers of all skill levels should learn to use current best practices when developing websites. However many developers, new and old, fall into the trap of using deprecated features because this is what so many tutorials and books tell them to u…
When table data gets too large to manage or queries take too long to execute the solution is often to buy bigger hardware or assign more CPUs and memory resources to the machine to solve the problem. However, the best, cheapest and most effective so…
The viewer will learn how to count occurrences of each item in an array.
This tutorial will teach you the core code needed to finalize the addition of a watermark to your image. The viewer will use a small PHP class to learn and create a watermark.

756 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question