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

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 2

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 s 
$bindString = str_repeat('s', count($ids));

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

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

Answer 3

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.