A prepared statement, `WHERE .. IN(..)` query and sorting — with MySQL

Question

Imagine we have a query:

SELECT * FROM somewhere WHERE `id` IN(1,5,18,25) ORDER BY `name`;

and an array of IDs to fetch: $ids = array(1,5,18,25)

With prepared statements it's adviced to prepare one statement and call it multiple times:

$stmt = $mysqli->prepare('SELECT * FROM somewhere WHERE `id`=?;');
foreach ($ids as $id){
    $stmt->bind_params('i', $id);
    $stmt->exec();
    }

But now I'll have to sort the results manually. Do I have any nice alternatives?

Answer 1

No, it's not recommended, if you are to fetch certain records from the database using ORDER BY clause.

Answer 2

Hi,

you could do it this way:

$ids = array(1,5,18,25);

// creates a string containing ?,?,? 
$clause = implode(',', array_fill(0, count($ids), '?'));


$stmt = $mysqli->prepare('SELECT * FROM somewhere WHERE `id` IN (' . $clause . ') ORDER BY `name`;');

call_user_func_array(array($stmt, 'bind_param'), $ids);
$stmt->execute();

// loop through results

Using this you're calling bind_param for each id and you have sorting done by mysql.

Answer 3

An alternative would be to use PHP usort function on the result object, but this is "manual."

See this: http://stackoverflow.com/questions/124266/sort-object-in-php

Answer 4

I'll add an ultimately slow & ugly solution which nevertheless uses prepared statements for ANY number of array items :) 3 statements are universal for any case and can be reused everywhere.

1. CREATE TEMPORARY TABLEids(idINT );
2. INSERT INTOidsVALUES(?); this will insert your IDs
3. SELECTidFROMidsLEFT JOIN .... ; use data from other tables to sort the ids list
4. SELECTidFROMids; select everything back

Otherwise you'll have to use IN (?,?,?,.... or sort the rows manually. The best idea is to use simple MySQL-queries, or, try to get the list of IDs already sorted in the way you like.