?
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
?
778 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
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 
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

NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

Question has a verified solution.

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

Build an array called $myWeek which will hold the array elements Today, Yesterday and then builds up the rest of the week by the name of the day going back 1 week.   (CODE) (CODE) Then you just need to pass your date to the function. If i…
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.
The viewer will learn how to count occurrences of each item in an array.
The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.
Suggested Courses

764 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