SELECT id FROM table_name WHERE column = $variable

I’m trying to retrieve the id number of a record to use as a variable elsewhere. I only want the id from a record that has a particular value in another column. here is my code:

$result = mysql_query("SELECT id FROM order WHERE orderNumber = '$orderNumber'") or die (mysql_error());
$id = $result;

When I test I get: SQL syntax; check the manual that corresponds to your MySQL server versi0on for the right syntax to use near ‘order WHERE orderNumber = ‘5b0ea3’

Any ideas?

Answer

SELECT id FROM order WHERE orderNumber = '$orderNumber'

ORDER is a reserver word in SQL; I’d recommend you change the name of the table to something else.

If you really can’t, then you can escape column and table names with backticks:

SELECT `id` FROM `order` WHERE `orderNumber` = '$orderNumber'

Or

SELECT `id` FROM `order` WHERE `orderNumber` = '".$orderNumber."'

And also see the comments about stopping using mysqli_ functions – they’re insecure, and being deprecated.