Getting error in SQL syntax and PHP

Question

I am getting the following error and can't figure out what the problem is.

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 'order (orderid, customerid, productid, brand, model, price, amount, totalcost) V' at line 1

//connect to database
$connection = mysql_connect("localhost","root","") or die ("Can't connect");
mysql_select_db("shoppingcart", $connection) or die ("Can't connect");

//get order id
$vol = mysql_query("SELECT orderid FROM ordertracking WHERE email='$email'");
while($volume=mysql_fetch_array($vol)) {
  $orderid = $volume['orderid'];
}
echo $orderid;
// add new order
$order = "INSERT INTO order (orderid, customerid, productid, brand, model, price, amount, totalcost) VALUES ('$orderid', '$customerid', '$productid', '$brand' , '$model', '$price', '$amount', '$totalcost')";
if (!mysql_query($order,$connection)) {
  die('Error: ' . mysql_error());
  echo "Sorry, there was an error";
}
echo "New order added" . "<br />";
mysql_close($connection);

score 2 · Accepted Answer · answered Jun 30 '12 at 11:41

2

ORDER is a mysql resered word enclose it in backticks ``.

You should not have a table or a column name conflicting with mysql reserved words otherwise you must have to enclose those in backticks.

$order = "INSERT INTO `order` (orderid, customerid,...

answered Jun 30 '12 at 11:41

Shakti Singh

84,385
21
134
153

Oh I see, thank you it worked. But why does it sometimes work without back quotes? Because exactly the same structure is setup for ordertracking database and there I have no back quotes on ordertracking, it is like `"INSERT INTO ordertracking (orderid, ...` and it works, so why is this? – MOTIVECODEX Jun 30 '12 at 11:44
1

@f4llcon: Because `ordertracking` is not a mysql reserved word – Shakti Singh Jun 30 '12 at 11:46
I see, will look into mysql reserved later on. Thank you. – MOTIVECODEX Jun 30 '12 at 11:50

score 2 · Answer 2 · answered Jun 30 '12 at 11:44

2

Like this:

INSERT INTO `order` (...) (ALT Gr+7)

If this solves the problem, give the credits to Shakti Singh.

answered Jun 30 '12 at 11:44

ZeeCoder

1,001
9
17

yes the solution resolved my problem, but it is really strange, the same structure in my previous code works without back quotes. – MOTIVECODEX Jun 30 '12 at 11:47
Quite simple: if the name you use in your code is not a valid SQL code, like: DESC, ORDER, GROUP etc., then the database recognises it as table/field name. Not as strange if you think about it. :) – ZeeCoder Jun 30 '12 at 15:24

score 1 · Answer 3 · edited May 23 '17 at 11:56

The keyword ORDER is a reserved keyword in sql. Means you cannot use it

so this will produce an error:

$order = "INSERT INTO order (orderid, customerid, productid, brand, model, price, amount, totalcost) VALUES ('$orderid', '$customerid', '$productid', '$brand' , '$model', '$price', '$amount', '$totalcost')";

Insert into ORDER

the order in the above statement should either be in backticks (as Mentioned by shakti) like

INSERT INTO 'order' (orderid,......

or you can enclose the reserved keywords in square brackets like

INSERT INTO [order] (orderid,......

for more information check this thread stack overflow question

score 0 · Answer 4 · answered Jun 30 '12 at 11:53

something else is that any code after die will not work i mean :

if (!mysql_query($order,$connection)) {
 die('Error: ' . mysql_error());
 echo "Sorry, there was an error";}

this code

echo "Sorry, there was an error";

will not work.and using this code :

die('Error: ' . mysql_error());

is not recomended at all due to security reasons

Getting error in SQL syntax and PHP

4 Answers4