Page 1 of 1

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

Posted: Mon Mar 03, 2008 12:24 pm
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 :)

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

Posted: Mon Mar 03, 2008 12:42 pm
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.

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

Posted: Mon Mar 03, 2008 1:27 pm
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:

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

Posted: Mon Mar 03, 2008 1:33 pm
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]}'
 

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

Posted: Mon Mar 03, 2008 4:23 pm
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"

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

Posted: Mon Mar 03, 2008 4:40 pm
by Benjamin
echo the query.

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

Posted: Mon Mar 03, 2008 6:36 pm
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