Correct Syntax to Add ORDER BY to SQL Query

Question

How can I add ORDER BY field to the end of this SQL query $sql = "SELECT item_id,field FROM item WHERE department=".$catid;? I can't get the syntax right due to the PHP variable at the end...

I tried $sql = "SELECT item_id,field FROM item WHERE department=".$catid ORDER BY field; but obviously that didn't work

Please show what you've tried. Variables don't go in SQL, parameterize your query. — user3783243, Apr 02 '20 at 02:00
`ORDER BY field` is an obvious syntax error. Are you using error reporting? It is the same as you wrote your other string encapsulation, `"SELECT ...` BUT realllly you shouldn't be doing this at all. The query should be `SELECT item_id,field FROM item WHERE department= ? ORDER BY field` then bind `$catid`. — user3783243, Apr 02 '20 at 02:02
@user3783243 Sorry, I don't know much more SQL... What would be the best practice way of doing this? — bicycle4431, Apr 02 '20 at 02:04
It's PHP syntax, not SQL. The SQL is "right" if `$catid` is an integer. — user3783243, Apr 02 '20 at 02:04
I don't know what driver your using with PHP so I can't tell you but look up `prepared statements` with the driver you are using. — user3783243, Apr 02 '20 at 02:13
I was just posting an answer to the question you just deleted... — Nick, Apr 11 '20 at 01:40

score 1 · Accepted Answer · answered Apr 02 '20 at 02:13

You can fix your syntax error like this, using another concatenation operator . to append the ORDER BY clause:

$sql = "SELECT item_id,field FROM item WHERE department=".$catid." ORDER BY field";

As long as $catid is an integer, that will work, but it may leave you open to SQL injection, dependent on the source of the value in $catid.

Best practice is to use a prepared query. For MySQLi, something like this:

$sql = "SELECT item_id,field FROM item WHERE department=? ORDER BY field";
$stmt = $conn->prepare($sql);
$stmt->bind_param('i', $catid);  // change to 's' if $catid is a string
$stmt->execute();
$result = $stmt->get_result();
while ($row = $result->fetch_assoc()) {
    // do something with results
}

Correct Syntax to Add ORDER BY to SQL Query

1 Answers1