SELECT id FROM table_name WHERE column = $variable

SELECT id FROM table_name WHERE column = $variable

  1. Blog
  2. 15 November 2026
  3. 3 min read

Stefan Bogdanescu

Founder & Senior Architect

SELECT id FROM table_name WHERE column = $variable

The snippet provided attempts to fetch a specific record ID based on an orderNumber, but it fails immediately due to two critical issues: improper variable handling and the use of deprecated functions. The error message "SQL syntax; check the manual..." is misleading because the SQL string itself is syntactically correct for MySQL, but the way the data is inserted into that string causes the parser to choke on what looks like invalid characters.

In the example $result = mysql_query("SELECT id FROM order WHERE orderNumber = '$orderNumber'"), the variable $orderNumber contains the value 5b0ea3. When concatenated directly into the query string, the resulting SQL becomes:

SELECT id FROM order WHERE orderNumber = '5b0ea3'

If the database column orderNumber is defined as a numeric type (e.g., INT or BIGINT) rather than a string (e.g., VARCHAR), MySQL will interpret the single quotes around the value as part of the number, which is invalid syntax. Even if the column is a string, direct concatenation opens the door to SQL injection, allowing an attacker to bypass filters by injecting characters like ' OR '1'='1.

The solution involves separating the data from the logic using prepared statements. This approach sends the SQL query structure separately from the user-provided data to the database engine. The engine then treats the data strictly as a value, not executable code. Here is how you should rewrite the logic:

$stmt = $db->prepare('SELECT id FROM `order` WHERE orderNumber = ?');
$stmt->bind_param('s', $orderNumber); // 's' indicates a string type
$stmt->execute();
$result = $stmt->get_result();
$row = $result->fetch_assoc();

if ($row) {
    $id = $row['id'];
} else {
    // Handle case where no record was found
}

There are several important details to note in this implementation. First, the table name order is a reserved keyword in MySQL. When referencing it as a table, you must wrap it in backticks (`order`) to ensure the parser treats it as an identifier rather than trying to interpret it as a command like ORDER BY.

Second, the mysql_query() function belongs to the legacy MySQL extension. It is deprecated since PHP 5.5 and removed in PHP 7.0. Relying on it guarantees your application will crash if you upgrade your PHP version. You must migrate to either mysqli or PDO. The code above uses the PDO interface, which offers superior exception handling capabilities compared to the procedural style of the original example.

When assigning the result to $id, the original code treated the query resource object itself as the ID. In MySQL's old API, a successful SELECT returns a result set, not the scalar value inside it. You must always fetch the row using fetch() or fetch_assoc() before accessing the column. If you need the raw integer directly without fetching an associative array, PDO provides the fetchColumn() method:

$stmt = $db->prepare('SELECT id FROM `order` WHERE orderNumber = ?');
$stmt->execute([$orderNumber]);
$id = $stmt->fetchColumn();

This returns the ID as a string or integer depending on your configuration, which you can then cast if necessary.

For developers moving to modern stacks like Laravel, this pattern is abstracted further using Eloquent ORM or query builder:

$order = Order::where('orderNumber', $orderNumber)->first();
$id = $order ? $order->id : null;

This approach handles the prepared statements automatically and provides a safer, more readable way to access the data. Whether you are maintaining legacy code or building something new, always ensure that table names are escaped with backticks if they conflict with reserved keywords, and never concatenate user input directly into SQL strings.

Our network