Correct Syntax to Add ORDER BY to SQL Query

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

Answer

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
}