Select Specific Rows in a Specific Order

Question

I have a script (in PHP) that goes through a bunch of different comparisons to generate an ordered array of entries in a table by the row id. Then I'm imploding the array into a string and using WHERE to select those specific rows, however I don't know how to order them in the same order as they were in the array.

$order_array = [50,49,42,52,53,54,51,48,47]
$order_string = implode(',', $order_array);
// echo $order_string returns '50,49,42,52,53,54,51,48,47'
$sql_todo = "SELECT * FROM todo_list WHERE id IN ({$order_string})";
    if ($result_todo = mysqli_query($link, $sql_todo)) {
        while ($row_todo = mysqli_fetch_assoc($result_todo)) {

This successfully selects the desired rows, but they are not in the same order as the array. I know that I haven't told it to order them that way (so it didn't), but I don't know how to make it happen.

Thanks for your time,

Seth

score 2 · Accepted Answer · answered May 27 '20 at 02:46

You could use the field() function:

"SELECT * FROM todo_list WHERE id IN ({$order_string}) ORDER BY FIELD(id, {$order_string})"

field() returns the index of its first argument in the list, which you can directly use for ordering.

Side note: you should probably use a prepared statement rather than concatenating values in the query string (if your values come from outside your code, this is a must-have).

Select Specific Rows in a Specific Order

1 Answers1