I’m curious to know if it’s possible to bind an array of values to a placeholder using PDO. The use case here is attempting to pass an array of values for use with an
I’d like to be able to do something like this:
<?php $ids=array(1,2,3,7,8,9); $db = new PDO(...); $stmt = $db->prepare( 'SELECT * FROM table WHERE id IN(:an_array)' ); $stmt->bindParam('an_array',$ids); $stmt->execute(); ?>
And have PDO bind and quote all the values in the array.
At the moment I’m doing:
<?php $ids = array(1,2,3,7,8,9); $db = new PDO(...); foreach($ids as &$val) $val=$db->quote($val); //iterate through array and quote $in = implode(',',$ids); //create comma separated list $stmt = $db->prepare( 'SELECT * FROM table WHERE id IN('.$in.')' ); $stmt->execute(); ?>
Which certainly does the job, but just wondering if there’s a built in solution I’m missing?
For something quick:
//$db = new PDO(...); //$ids = array(...); $qMarks = str_repeat('?,', count($ids) - 1) . '?'; $sth = $db->prepare("SELECT * FROM myTable WHERE id IN ($qMarks)"); $sth->execute($ids);
Is it so important to use
IN statement? Try to use
For example, there is a query in PDO like that
SELECT * FROM table WHERE FIND_IN_SET(id, :array)
Then you only need to bind an array of values, imploded with comma, like this one
$ids_string = implode(',', $array_of_smth); // WITHOUT WHITESPACES BEFORE AND AFTER THE COMMA $stmt->bindParam('array', $ids_string);
and it’s done.
UPD: As some people pointed out in comments to this answer, there are some issues which should be stated explciitly.
FIND_IN_SETdoesn’t use index in a table, and it is still not implemented yet – see this record in the MYSQL bug tracker. Thanks to @BillKarwin for the notice.
- You can’t use a string with comma inside as a value of the array for search. It is impossible to parse such string in the right way after
implodesince you use comma symbol as a separator. Thanks to @VaL for the note.
In fine, if you are not heavily dependent on indexes and do not use strings with comma for search, my solution will be much easier, simpler, and faster than solutions listed above.