Bind an Array to SQL IN Operator Using PDO
September 14, 2020 • PHP, SQL • Published By Josh • 2 minute read
When using PDO, binding a specific number of values to the IN operator isn’t difficult. What if the number of values isn’t a fixed number though? Maybe it’s an array that could have any number of entries. Let’s learn how to bind an array of values when using the IN operator in a WHERE clause.
Table of Contents
SQL IN Operator
The SQL IN operator allows you to specify multiple values in a WHERE clause. It’s basically a shorthand for multiple OR conditions. It expects comma separated values.
WHERE column_name IN (value1, value2, ...);
Users Table
Let’s say our database has a table called users that has thousands of rows of data. We’re interested in selecting specific users by their ids in a WHERE clause using the IN operator.
| id | firstName | lastName |
|---|---|---|
| 1 | First | Last |
| 2 | First | Last |
| 3 | First | Last |
User IDs
Maybe you have a form where a list of users are being selected. When the form is submitted, it sends an array of the selected user ids.
$ids = $_POST['ids'];
The $ids variable might look like the following:
[1, 2, 3]
Placeholders
Passing in user input directly to a SQL query is dangerous due to SQL injection. The safer option is to use prepared statements and bind values by named variables or placeholders using PDO. We’ll use the placeholder option.
What we’re looking for is something like the following query where the number of placeholders (question marks) are equal to the number of user ids we’re interested in:
SELECT * FROM users WHERE id IN (?, ?, ?)
Build an array of placeholders (question marks) using the array_fill function based on the number of user ids in the $ids array using the count function.
$placeholders = array_fill( 0, count($ids), '?' );
Convert the array of placeholders (question marks) to a string of comma separated placeholders (question marks) using the implode function. The string of placeholders will be used in the IN operator of our WHERE clause.
$placeholderString = implode( ',', $placeholders );
SQL Query
Build the query including the placeholder string.
$query = 'SELECT * FROM users WHERE id IN (' . $placeholderString . ')';
Prepare the query.
$connection = new PDO( $dsn, $username, $password );
$stmt = $connection->prepare($query);
Bind the user id values to the placeholders. Placeholders start their count at 1 not 0!
$i = 1;
foreach( $ids AS $id ) {
$stmt->bindValue( $i++, $id );
}
All that’s left to do is execute the query and fetch the results!
$stmt->execute();
$users = $stmt->fetchAll(PDO::FETCH_ASSOC);
Related Articles
