Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
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
Medium Priority
?
784 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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 1800 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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
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 111

Assisted Solution

by:Ray Paseur
Ray Paseur earned 200 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

Fill in the form and get your FREE NFR key NOW!

Veeam® is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

Question has a verified solution.

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

3 proven steps to speed up Magento powered sites. The article focus is on optimizing time to first byte (TTFB), full page caching and configuring server for optimal performance.
In this blog post, we’ll look at how using thread_statistics can cause high memory usage.
The viewer will learn how to count occurrences of each item in an array.
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
Suggested Courses

604 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