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

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.

Had the same problem and in addition to the answer of @sled 7 years ago, here is a possibility without making the call_user_func_array(array($stmt, 'bind_param'), $ids); step, but only call bind_params once:

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

// creates a string containing ?,?,? 
$bindClause = implode(',', array_fill(0, count($ids), '?'));
//create a string for the bind param just containing the right amount of iii
$bindString = str_repeat('i', count($ids));

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

$stmt->bind_params($bindString, ...$ids);
$stmt->execute();

I believe this is the simplest possible answer :

$ids = [1,2,3,4,5];
$pdos = $pdo->prepare("SELECT * FROM somwhere WHERE id IN (:"
        . implode(',:', array_keys($ids)) . ") ORDER BY id");

foreach ($ids as $k => $id) {
    $pdos->bindValue(":". $k, $id);
}

$pdos->execute();
$results = $pdos->fetchAll();

So long your array of Ids does not contain keys or keys with illegal characters, it wil work.

I'll add a 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 TABLE `ids`( `id` INT );
2. INSERT INTO `ids` VALUES(?); this will insert your IDs
3. SELECT `id` FROM `ids` LEFT JOIN .... ; use data from other tables to sort the ids list
4. SELECT `id` FROM `ids`; 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.

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

See this: Sort Object in PHP