Use an array in a mysqli prepared statement: `WHERE .. IN(..)` query

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.

Answer 4

For the task of executing a secure mysqli query with a dynamic number of incoming values to be fed into the sql string, a prepared statement is the professional technique to implement.

Let's assume that the incoming data payload is user-supplied data -- this means that we cannot guarantee the integrity of the data nor can we guarantee the volume of data. In fact, the expected array of data might be empty. The below snippet will demonstrate how to pass an array of ids to the IN () condition in the WHERE clause of a prepared statement. If there are no values in the array, then a prepared statement provides no benefit and should not be used.

MySQLi result set objects can be immediately iterated by a foreach() loop. Therefore, it is not necessary to make iterated fetch calls; just access the rows' data using array syntax.

The array of ids means that the sql will expect integer values. When calling bind_param(), the first parameter will be a single string of repeated i characters. For general use, if the data will be strings or you might have a mix of data types (e.g. integers, floats/doubles, or strings), then is simpler to just use repeated s characters instead of i characters.

Code: (PHPize.online Demo with SQL)

$ids = [1, 5, 18, 25];  // this could be, for example: $_GET['ids']
$count = count($ids);

$sql = 'SELECT name FROM somewhere';
$orderBy = 'ORDER BY name';
if ($count) {
    $placeholders = implode(',', array_fill(0, $count, '?'));
    $stmt = $mysqli->prepare("$sql WHERE id IN ($placeholders) $orderBy");
    $stmt->bind_param(str_repeat('i', $count), ...$ids);
    $stmt->execute();
    $result = $stmt->get_result();
} else {
    $result = $mysqli->query("$sql $orderBy"); // a prepared statement is unnecessary
}
foreach ($result as $row) {
    echo "<div>{$row['name']}</div>\n";
}

Output from my PHPize demo:

<div>Alan</div>
<div>Bill</div>
<div>Chad</div>
<div>Dave</div>

---

If you don't need to iterate the result set for any reason, then you can fetch_all(). This is commonly used when immediately echoing or returning a json-encoded string (say, as the response to an ajax call). In this case, you replace the foreach() block with: (PHPize.online Demo with SQL)

echo json_encode($result->fetch_all(MYSQLI_ASSOC));

or simply dump the multidimensional array:

var_export($result->fetch_all(MYSQLI_ASSOC));

Output from my PHPize demo:

[{"name":"Alan"},{"name":"Bill"},{"name":"Chad"},{"name":"Dave"}]

---

From PHP8.1 and higher, it is no longer necessary to call bind_param() because the execute() method can receive the payload of parameters as an array (like PDO).

This means that...

$stmt->bind_param(str_repeat('i', $count), ...$ids);
$stmt->execute();

can be replaced with...

$stmt->execute($ids);

Here's a complete, basic example: (PHPize.online Demo)

$ids = [1, 2, 3, 4, 5];
$stmt = $mysqli->prepare("SELECT * FROM somewhere WHERE id IN (" . rtrim(str_repeat('?,', count($ids)), ',') . ") ORDER BY id");
$stmt->execute($ids);
var_export($stmt->get_result()->fetch_all(MYSQLI_ASSOC));

Topical Resources:

• php.net

• The RFC was authored by our very own Dharman ♦ and implemented as part of PHP8.1 after a unanimous vote on 2021-03-27.

• phpbackend.com article from 24, October 2021

• Reddit thread

• PDO can do this concisely.

Answer 5

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.

Answer 6

Have you considered rewriting you original query using a JOIN and WHERE clause to get the IDS you need to avoid the need for a WHERE IN clause? I came here with the same question and after reviewing the possible solutions I realized an INNER JOIN was my solution.

Answer 7

Copied from my answer here How to use PDO prepared statements with IN clause?

using named place holders

$values = array(":val1"=>"value1", ":val2"=>"value2", ":val2"=>"$value3");
$statement = 'SELECT * FROM <table> WHERE `column` in(:'.implode(', :',array_keys($values)).') ORDER BY `column`';

using ??

$values = array("value1", "value2", "$value3");
$statement = 'SELECT * FROM <table> WHERE `column` in('.trim(str_repeat(', ?', count($values)), ', ').')  ORDER BY `column`';

Answer 8

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

See this: Sort Object in PHP