SELECT id FROM table_name WHERE column = $variable

Question

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 1

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.

Answer 2

Since ORDER is a reserved word, you must use backticks on your sql query. And after that use the mysql_fetch_assoc in order to get the id.

$result = mysql_query("SELECT `id` FROM `order` WHERE `orderNumber` = '$orderNumber'") or die (mysql_error());
$row = mysql_fetch_assoc($result);
$id = $row['id'];

On the other hand, you should be using PDO or Mysqli, the mysql_* functions are deprecated and considered a bad practice.

If you plan on keep using mysql_query, at least make sure to sanitize the $orderNumber variable with mysql_real_escape_String.