{"id":2185,"date":"2021-07-08T07:50:44","date_gmt":"2021-07-08T07:50:44","guid":{"rendered":"https:\/\/phptutorial.net\/?page_id=2185"},"modified":"2021-07-08T07:57:43","modified_gmt":"2021-07-08T07:57:43","slug":"pdo-in","status":"publish","type":"page","link":"https:\/\/www.phptutorial.net\/php-pdo\/pdo-in\/","title":{"rendered":"PDO IN"},"content":{"rendered":"\n

Summary<\/strong>: in this tutorial, you’ll learn how to use PDO to execute a query with the IN<\/code> operator.<\/p>\n\n\n\n

A quick introduction to the IN operator #<\/a><\/h2>\n\n\n\n

The IN<\/code> operator returns true if a value is in a set of values. The IN<\/code> operator can be used in the WHERE<\/code> clause of the SELECT<\/code>, UPDATE<\/code> and DELETE<\/code> statement.<\/p>\n\n\n\n

For example, to get a list of books from the books<\/code> table with id is either 1, 2, or 3, you can use the IN<\/code> operator like this:<\/p>\n\n\n

SELECT<\/span> book_id, title\nFROM<\/span> books\nWHERE<\/span> book_id IN<\/span> (1<\/span>,2<\/span>,3<\/span>);<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
It’s equivalent to the = and OR operator:<\/p>\n\n\n
SELECT<\/span> book_id, title\nFROM<\/span> books\nWHERE<\/span> book_id = 1<\/span> OR<\/span> \n      book_id = 2<\/span> OR<\/span> \n      book_id = 3<\/span>;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Execute an SQL statement with the IN operator using PDO #<\/a><\/h2>\n\n\n\n
To execute this SQL statement in PDO, you need to construct a statement with the placeholders (?) like this:<\/p>\n\n\n
$sql = 'SELECT book_id, title\n        FROM books\n        WHERE book_id IN (?,?,?)'<\/span>;<\/code><\/span>Code language:<\/span> PHP<\/span> (<\/span>php<\/span>)<\/span><\/small><\/pre>\n\n\n
$statement = $pdo->prepare($sql);\n$statement->execute([1<\/span>,2<\/span>,3<\/span>]);<\/code><\/span>Code language:<\/span> PHP<\/span> (<\/span>php<\/span>)<\/span><\/small><\/pre>\n\n\n
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<\/code> and execute()<\/code> method.<\/p>\n\n\n\n
If you use one placeholder like the following, it won’t work:<\/p>\n\n\n
$sql = 'SELECT book_id, title\n        FROM books\n        WHERE book_id IN ?'<\/span>;<\/code><\/span>Code language:<\/span> PHP<\/span> (<\/span>php<\/span>)<\/span><\/small><\/pre>\n\n\n
The reason is that when you bind the values, the statement will look like this:<\/p>\n\n\n
SELECT<\/span> book_id, title\nFROM<\/span> books \nWHERE<\/span> book_id IN<\/span> ('1,2,3'<\/span>);<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
However, the correct query is:<\/p>\n\n\n
SELECT<\/span> book_id, title\nFROM<\/span> books \nWHERE<\/span> book_id IN<\/span> ('1'<\/span>,'2'<\/span>,'3'<\/span>);<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
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:<\/p>\n\n\n
<?php<\/span>\n\n\/**\n* Return an array of books with the book id in the $list\n*\/<\/span>\nfunction<\/span> get_book_list<\/span>(\\PDO $pdo, array $list)<\/span>: array<\/span>\n<\/span>{\n    $placeholder = str_repeat('?,'<\/span>, count($list) - 1<\/span>) . '?'<\/span>;\n\n    $sql = \"SELECT book_id, title \n            FROM books \n            WHERE book_id in ($placeholder)\"<\/span>;\n\n    $statement = $pdo->prepare($sql);\n    $statement->execute($list);\n\n    return<\/span>  $statement->fetchAll(PDO::FETCH_ASSOC);\n}\n\n\/\/ connect to the database<\/span>\n$pdo = require<\/span> 'connect.php'<\/span>;\n\n\/\/ get a list of book<\/span>\n$books = get_book_list($pdo, [1<\/span>, 2<\/span>, 3<\/span>]);\n\nprint_r($books);<\/code><\/span>Code language:<\/span> PHP<\/span> (<\/span>php<\/span>)<\/span><\/small><\/pre>\n\n\n
How it works.<\/p>\n\n\n\n
The get_book_list()<\/code> function accepts a PDO object and an array of book id. It returns an array of books.<\/p>\n\n\n\n
First, generate a list of the placeholders (?) based on the number of elements in the $list<\/code> array:<\/p>\n\n\n
$placeholder = str_repeat('?,'<\/span>, count($list) - 1<\/span>) . '?'<\/span>;<\/code><\/span>Code language:<\/span> PHP<\/span> (<\/span>php<\/span>)<\/span><\/small><\/pre>\n\n\n
For example, if the $list has three elements, the placeholder wil be ‘?,?,?’;<\/p>\n\n\n\n
Next, use the placeholder to construct the SQL statement:<\/p>\n\n\n
$sql = \"SELECT book_id, title \n        FROM books \n        WHERE book_id in ($placeholder)\"<\/span>;<\/code><\/span>Code language:<\/span> PHP<\/span> (<\/span>php<\/span>)<\/span><\/small><\/pre>\n\n\n
Then, prepare the statement for execution:<\/p>\n\n\n
$statement = $pdo->prepare($sql);<\/code><\/span>Code language:<\/span> PHP<\/span> (<\/span>php<\/span>)<\/span><\/small><\/pre>\n\n\n
After that, execute the statement by passing the $list<\/code>:<\/p>\n\n\n
$statement->execute($list);<\/code><\/span>Code language:<\/span> PHP<\/span> (<\/span>php<\/span>)<\/span><\/small><\/pre>\n\n\n
Finally, fetch all rows from the result set and return an associative array:<\/p>\n\n\n
return<\/span>  $statement->fetchAll(PDO::FETCH_ASSOC);<\/code><\/span>Code language:<\/span> PHP<\/span> (<\/span>php<\/span>)<\/span><\/small><\/pre>\n\n\n
The following code connects to the database and uses the get_book_list()<\/code> function to get the book with id in the set 1, 2, and 3:<\/p>\n\n\n
\/\/ connect to the database <\/span>\n$pdo = require<\/span> 'connect.php'<\/span>;\n\n\/\/ get a list of book<\/span>\n$books = get_book_list($pdo, [1<\/span>, 2<\/span>, 3<\/span>]);\n\nprint_r($books);<\/code><\/span>Code language:<\/span> PHP<\/span> (<\/span>php<\/span>)<\/span><\/small><\/pre>\n\n\n
Summary #<\/a><\/h2>\n\n\n\n
  • Generate placeholders (?<\/code>) and construct the SQL statement with the IN<\/code> operator.<\/li>
  • Use a prepared statement to execute the SQL statement by passing an array of values.<\/li><\/ul>\n
    \n\t
    \n\t\t
    Did you find this tutorial useful?<\/div>\n\t\t
    \n\t\t\t\n\t\t\t\t\n\t\t\t\t\t<\/path>\n\t\t\t\t<\/svg>\n\t\t\t\t Yes <\/span>\n\t\t\t<\/button>\n\n\t\t\t\n\t\t\t\t\n\t\t\t\t\t<\/path>\n\t\t\t\t<\/svg>\n\t\t\t\t No <\/span>\n\t\t\t<\/button>\n\t\t<\/div>\n\t<\/header>\n\n\t
    \n\t\t
    \n\t\t\t
    <\/div>\n\t\t\t\n\t\t\t
And use a prepared statement<\/a> to bind the values from the array<\/a>:<\/p>\n\n\n