By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
458,122 Members | 1,559 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 458,122 IT Pros & Developers. It's quick & easy.

SQL syntax error in delete statement

P: 28
"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, this error message will be display when that locater shows negative stock and Receive new stocks to that locater.

[PHP]
<?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(),'$Ref erence','$username')";
mysql_query($q)or die(mysql_error()) ;

$q = "update remotestatus set step=6 where ip='$ip'";
mysql_query($q)or die(mysql_error()) ;
?>
[/PHP]

Just wandering if someone has similar issue. please advise
Mar 3 '08 #1
Share this Question
Share on Google+
3 Replies


ronverdonk
Expert 2.5K+
P: 4,258
No the correct delete syntax is 'delete from', ie.
Expand|Select|Wrap|Line Numbers
  1. ..delete from stock where stock_id=...
Ronald
Mar 3 '08 #2

P: 28
No the correct delete syntax is 'delete from', ie.
Expand|Select|Wrap|Line Numbers
  1. ..delete from stock where stock_id=...
Ronald
Thank you for your help, problem solved. Sorry, my brain was not working today :P

Best Regards
Mar 4 '08 #3

ronverdonk
Expert 2.5K+
P: 4,258
Thank you for your help, problem solved. Sorry, my brain was not working today :P

Best Regards
Happens to me frequently. See you again next time.

Ronald
Mar 4 '08 #4

Post your reply

Sign in to post your reply or Sign up for a free account.