"You have an error in your SQL syntax;" error

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
cyberlei
Forum Commoner
Posts: 27
Joined: Tue Oct 16, 2007 6:19 pm
Location: Milpitas, CA

"You have an error in your SQL syntax;" error

Post by cyberlei »

"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 stock_id=19721' at line 1"
I have been banging my head trying to find out where is it :banghead: , this error message will be display when that locater shows negative stock and Receive new stock to that locater.

Code: Select all

 
<?php
    $q ="select stock_id,quantity from stock where item_id=$item_id and locator_id=$locator_id";
    $result = mysql_query($q)or die(mysql_error()) ;
    if(!$result || (mysql_numrows($result) < 1))
   {
        
        $q ="insert into stock (item_id,locator_id,quantity) values($item_id,$locator_id,$qty)";
        mysql_query($q)or die(mysql_error()) ;
   }
   else
   {
        
        $row = mysql_fetch_row($result);
        mysql_free_result($result);
        $total=$qty+$row[1];
        $q ="update stock set quantity=$total where stock_id=$row[0]";
        mysql_query($q)or die(mysql_error()) ;
   }
    $q ="select stock_id,quantity from stock where item_id=$item_id and locator_id=$locator_id";
    $result = mysql_query($q)or die(mysql_error()) ;
    $row = mysql_fetch_row($result);
    if($row[1]==0)  
    {   
        $q ="delete stock where stock_id=$row[0]";
        mysql_query($q)or die(mysql_error()) ;
    }
   
   
   $q="insert into log (Log_Type_ID,item_id,Locator_ID,Quantity,Date_Time,thrid_reference,User_ID) values(6,$item_id,$locator_id,$qty,sysdate(),'$Reference','$username')";
    mysql_query($q)or die(mysql_error()) ;
 
    $q = "update remotestatus set step=6 where ip='$ip'";
        mysql_query($q)or die(mysql_error()) ;
        ?>
 
Just wandering if someone has similar issue. please advise :)
Last edited by cyberlei on Mon Mar 03, 2008 3:05 pm, edited 1 time in total.
User avatar
Christopher
Site Administrator
Posts: 13596
Joined: Wed Aug 25, 2004 7:54 pm
Location: New York, NY, US

Re: "You have an error in your SQL syntax;" error

Post by Christopher »

You may want to try"

Code: Select all

$q ="update stock set quantity=$total where stock_id={$row[0]}";
It is a good practice to wrap arrays and object embeded in strings with braces to make it clear to the parser.
(#10850)
cyberlei
Forum Commoner
Posts: 27
Joined: Tue Oct 16, 2007 6:19 pm
Location: Milpitas, CA

Re: "You have an error in your SQL syntax;" error

Post by cyberlei »

arborint wrote:You may want to try"

Code: Select all

$q ="update stock set quantity=$total where stock_id={$row[0]}";
It is a good practice to wrap arrays and object embeded in strings with braces to make it clear to the parser.
Thanks for your advise, however the error message still displaying. :oops:
User avatar
Benjamin
Site Administrator
Posts: 6935
Joined: Sun May 19, 2002 10:24 pm

Re: "You have an error in your SQL syntax;" error

Post by Benjamin »

My hunch is that total is a decimal and this is causing the query to fail. Just to eliminate every point of failure you can use the query below. You may want to even take it a step further and run total and stock_id through mysql_real_escape_string.

Code: Select all

 
UPDATE `stock` SET `quantity`='$total' WHERE `stock_id`='{$row[0]}'
 
cyberlei
Forum Commoner
Posts: 27
Joined: Tue Oct 16, 2007 6:19 pm
Location: Milpitas, CA

Re: "You have an error in your SQL syntax;" error

Post by cyberlei »

astions wrote:My hunch is that total is a decimal and this is causing the query to fail. Just to eliminate every point of failure you can use the query below. You may want to even take it a step further and run total and stock_id through mysql_real_escape_string.

Code: Select all

 
UPDATE `stock` SET `quantity`='$total' WHERE `stock_id`='{$row[0]}'
 
Yes, you got the point, total is a decimal and this is causing the query to fail. the error message still showing up after I replaced query. i`m dying about this :banghead:
"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 stock_id=19723' at line 1"
User avatar
Benjamin
Site Administrator
Posts: 6935
Joined: Sun May 19, 2002 10:24 pm

Re: "You have an error in your SQL syntax;" error

Post by Benjamin »

echo the query.
cyberlei
Forum Commoner
Posts: 27
Joined: Tue Oct 16, 2007 6:19 pm
Location: Milpitas, CA

Re: "You have an error in your SQL syntax;" error

Post by cyberlei »

Thank you for all your support and help, problem solved. should be

Code: Select all

 
$q ="DELETE FROM stock WHERE stock_id=$row[0]";
 
:oops:
Best Regards
Post Reply