MySQL SELECT in order of array values

Can anyone help me with a Query query?

I have an array of IDs $IDvalues = array("128", "159", "7", "81", "82", "83");

And need to retrieve data from another table in the order of the array. At the moment, I have this query:

$detailsQuery = mysqli_query($conn, "SELECT details FROM detailsTable WHERE id IN (".implode(',', $IDvalues).")");

But it’s getting it in numerical order (7, 81, 82, 83, 128, 159). I need 128 first, then 159… Is there anything I can use in the query to retain the order?

Thanks all!

Answer

Without another column in the table from which to sort in an ORDER BY, you cannot get a determinate sort order back from the SQL query. It is possible to concoct an ORDER BY using an elaborate chain like

ORDER BY 
CASE
  WHEN id = 128 THEN 1
  WHEN id = 159 THEN 2
 ....
END

But that is a terrible idea.

Instead, I would recommend storing the fetched rows in an array indexed by their id column, then using your original $IDvalues array to iterate it:

// Empty array to hold your result rows
$rows = [];
while ($row = mysqli_fetch_assoc($detailsQuery)) {
  // Append the fetched row to your result array using its id as index
  $rows[$row['id']] = $row;
}

// Output your rows using the original $IDvalues
// to lookup rows by index.
// Looping over $IDvalues ensures you get its order
// back out.
foreach ($IDvalues as $id) {
  // Retrieve from your $rows array by id index
  // Output them however you need
  print_r($rows[$id]);
}

This method will not be efficient if the size of $IDvalues is many thousands because it requires fetching all rows before writing them out, but it seems like you are dealing with a smaller array.