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
– user3783243, Commented Apr 2, 2020 at 2: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
– user3783243, Commented Apr 2, 2020 at 2:02
@user3783243 Sorry, I don't know much more SQL... What would be the best practice way of doing this? — bicycle4431
– bicycle4431, Commented Apr 2, 2020 at 2:04
It's PHP syntax, not SQL. The SQL is "right" if $catid is an integer. — user3783243
– user3783243, Commented Apr 2, 2020 at 2: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
– user3783243, Commented Apr 2, 2020 at 2:13

Nick · Accepted Answer · 2020-04-02 02:13:48Z

1

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
}

answered Apr 2, 2020 at 2:13

Nick

147k23 gold badges67 silver badges106 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Correct Syntax to Add ORDER BY to SQL Query

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related