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
764 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
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 
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 109

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Nothing in an HTTP request can be trusted, including HTTP headers and form data.  A form token is a tool that can be used to guard against request forgeries (CSRF).  This article shows an improved approach to form tokens, making it more difficult to…
This article discusses how to create an extensible mechanism for linked drop downs.
Explain concepts important to validation of email addresses with regular expressions. Applies to most languages/tools that uses regular expressions. Consider email address RFCs: Look at HTML5 form input element (with type=email) regex pattern: T…
The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.

861 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