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
759 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 142

Accepted Solution

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

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
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 
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 108

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

Part of the Global Positioning System A geocode (https://developers.google.com/maps/documentation/geocoding/) is the major subset of a GPS coordinate (http://en.wikipedia.org/wiki/Global_Positioning_System), the other parts being the altitude and t…
This article discusses how to create an extensible mechanism for linked drop downs.
The viewer will learn how to count occurrences of each item in an array.
The viewer will learn how to create and use a small PHP class to apply a watermark to an image. This video shows the viewer the setup for the PHP watermark as well as important coding language. Continue to Part 2 to learn the core code used in creat…

867 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now