How to pass an array to a query using a WHERE clause with PHP?

Spread the love

Sometimes, we want to pass an array to a query using a WHERE clause with PHP.

In this article, we’ll look at how to pass an array to a query using a WHERE clause with PHP.

How to pass an array to a query using a WHERE clause with PHP?

To pass an array to a query using a WHERE clause with PHP, we can create a string with question marks separated by commas.

For instance, with PDO, we write

$in = join(',', array_fill(0, count($ids), '?'));
$select = <<<SQL
    SELECT *
    FROM galleries
    WHERE id IN ($in);
SQL;
$statement = $pdo->prepare($select);
$statement->execute($ids);

to call join with ',' and the count($ids) and '?' to return a string with the number of question marks we want in the parentheses.

And then we call prepare to prepare the statement.

Then we call execute to run the query with the $ids array.

Likewise, with MySQLi, we write

$in = join(',', array_fill(0, count($ids), '?'));
$select = <<<SQL
    SELECT *
    FROM galleries
    WHERE id IN ($in);
SQL;
$statement = $mysqli->prepare($select);
$statement->bind_param(str_repeat('i', count($ids)), ...$ids);
$statement->execute();
$result = $statement->get_result();

to do something similar.

We call bind_params to with a string with 'i' repeated count($ids) times.

And we unpack the entries in the $ids array as arguments.

Then we run the query with execute and get the results with get_result.

Conclusion

To pass an array to a query using a WHERE clause with PHP, we can create a string with question marks separated by commas.

Leave a Reply

Your email address will not be published. Required fields are marked *