Stupid Syntax Problem

TPhyl

I have come across this problem more than I can count, and every time it happens the result is different. I think it has something to do with the various versions of server/php/mysql I am using.

This script is running on Goddaddy Linux servers & using PHP/MySQL 5.

The database is fine, it connects and I can run SELECT commands.


$order="12345678";
$contractor="John Smith";
$client="Ocwen"
$date="2010-01-01";

$do = "INSERT INTO orders (order, contractor, client, date) VALUES ('$order','$contractor','$client','$date')";

mysql_query($do) or die("Query failed : " . mysql_error());

ERROR

Query failed : 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, contractor, client, date) VALUES ('12345678','John Smith','altisource','2' at line 1

I have tried it with every combination of single/double quotes I could. I also tried it by replacing variables with the actual values.

If it matters at all, the cell types are as follows:
order - varchar(32)
contractor - varchar(32)
client - varchar(32)
date - date

Any ideas?

nevvermind

Bah, nevermind. I tried your syntax. It did worked.
Check your tables. Try some backticks on tables: eg. orders.

EDIT: Something like this:

$do = "INSERT INTO `orders` (`order`, `contractor`, `client`, `date`) VALUES ('$order','$contractor','$client','$date')";

I think some words in the table name or columns were reserved for mysql, hence the error. But I've searched high and low about the correct syntax. I've seed this:

// just an example, w\o specifying the columns
$do = "INSERT INTO `orders` VALUES ('$order')";

// now with columns:
$do = "INSERT INTO `orders` (`order`, `contractor`, `client`, `date`) VALUES ('$order','$contractor','$client','$date')";

We must wrap the table name and the column names in backticks? I seldom see when using "insert into". What's the best practice here?

sneakyimp

'order' is a reserved word in mysql. you should probably pick a different name for your database column, but you can fix the problem by using backticks around order (order).

TPhyl

Wow you guys rock. I would have never suspected such a thing.

I changed "orders" to "worders" and it works perfect.

Thanks!