PDO IN
Summary: in this tutorial, you’ll learn how to use PDO to execute a query with the IN operator.
A quick introduction to the IN operator
The IN operator returns true if a value is in a set of values. The IN operator can be used in the WHERE clause of the SELECT, UPDATE and DELETE statement.
For example, to get a list of books from the books table with id is either 1, 2, or 3, you can use the IN operator like this:
SELECT book_id, title
FROM books
WHERE book_id IN (1,2,3);Code language: SQL (Structured Query Language) (sql)
It’s equivalent to the = and OR operator:
SELECT book_id, title
FROM books
WHERE book_id = 1 OR
book_id = 2 OR
book_id = 3;Code language: SQL (Structured Query Language) (sql)
Execute an SQL statement with the IN operator using PDO
To execute this SQL statement in PDO, you need to construct a statement with the placeholders (?) like this:
$sql = 'SELECT book_id, title
FROM books
WHERE book_id IN (?,?,?)';Code language: PHP (php)
And use a prepared statement to bind the values from the array:
$statement = $pdo->prepare($sql);
$statement->execute([1,2,3]);Code language: PHP (php)
In practice, the size of the id list is dynamic. Typically, you don’t know how many values will be passed to both the $sql and execute() method.
If you use one placeholder like the following, it won’t work:
$sql = 'SELECT book_id, title
FROM books
WHERE book_id IN ?';Code language: PHP (php)
The reason is that when you bind the values, the statement will look like this:
SELECT book_id, title
FROM books
WHERE book_id IN ('1,2,3');Code language: SQL (Structured Query Language) (sql)
However, the correct query is:
SELECT book_id, title
FROM books
WHERE book_id IN ('1','2','3');Code language: SQL (Structured Query Language) (sql)
To fix this, you need to construct the SQL statement based on the number of elements in the array. The following example illustrates the solution:
/**
* Return an array of books with the book id in the $list
*/
function get_book_list(\PDO $pdo, array $list): array
{
$placeholder = str_repeat('?,', count($list) - 1) . '?';
$sql = "SELECT book_id, title
FROM books
WHERE book_id in ($placeholder)";
$statement = $pdo->prepare($sql);
$statement->execute($list);
return $statement->fetchAll(PDO::FETCH_ASSOC);
}
// connect to the database
$pdo = require 'connect.php';
// get a list of book
$books = get_book_list($pdo, [1, 2, 3]);
print_r($books);
Code language: PHP (php)
How it works.
The get_book_list() function accepts a PDO object and an array of book id. It returns an array of books.
First, generate a list of the placeholders (?) based on the number of elements in the $list array:
$placeholder = str_repeat('?,', count($list) - 1) . '?';Code language: PHP (php)
For example, if the $list has three elements, the placeholder wil be ‘?,?,?’;
Next, use the placeholder to construct the SQL statement:
$sql = "SELECT book_id, title
FROM books
WHERE book_id in ($placeholder)";Code language: PHP (php)
Then, prepare the statement for execution:
$statement = $pdo->prepare($sql);Code language: PHP (php)
After that, execute the statement by passing the $list:
$statement->execute($list);Code language: PHP (php)
Finally, fetch all rows from the result set and return an associative array:
return $statement->fetchAll(PDO::FETCH_ASSOC);Code language: PHP (php)
The following code connects to the database and uses the get_book_list() function to get the book with id in the set 1, 2, and 3:
// connect to the database
$pdo = require 'connect.php';// get a list of book
$books = get_book_list($pdo, [1, 2, 3]);
print_r($books);
Code language: PHP (php)
Summary
- Generate placeholders (
?) and construct the SQL statement with theINoperator. - Use a prepared statement to execute the SQL statement by passing an array of values.