Can I bind an array to an IN condition

Question

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 IN   condition     I d like to be able to do something like this    lt  php  ids array 1 2 3 7 8 9    db   new PDO        stmt    db- gt prepare       SELECT        FROM table      WHERE id IN  an array       stmt- gt bindParam  an array   ids    stmt- gt execute      gt    And have PDO bind and quote all the values in the array   At the moment I m doing    lt  php  ids   array 1 2 3 7 8 9    db   new PDO       foreach  ids as  amp  val       val  db- gt quote  val     iterate through array and quote  in   implode      ids     create comma separated list  stmt    db- gt prepare       SELECT        FROM table      WHERE id IN    in         stmt- gt execute      gt    Which certainly does the job  but just wondering if there s a built in solution I m missing

User · Answer

I extended PDO to do something similar to what stefs suggests, and it was easier for me in the long run:

class Array_Capable_PDO extends PDO {
    /**
     * Both prepare a statement and bind array values to it
     * @param string $statement mysql query with colon-prefixed tokens
     * @param array $arrays associatve array with string tokens as keys and integer-indexed data arrays as values 
     * @param array $driver_options see php documention
     * @return PDOStatement with given array values already bound 
     */
    public function prepare_with_arrays($statement, array $arrays, $driver_options = array()) {

        $replace_strings = array();
        $x = 0;
        foreach($arrays as $token => $data) {
            // just for testing...
            //// tokens should be legit
            //assert('is_string($token)');
            //assert('$token !== ""');
            //// a given token shouldn't appear more than once in the query
            //assert('substr_count($statement, $token) === 1');
            //// there should be an array of values for each token
            //assert('is_array($data)');
            //// empty data arrays aren't okay, they're a SQL syntax error
            //assert('count($data) > 0');

            // replace array tokens with a list of value tokens
            $replace_string_pieces = array();
            foreach($data as $y => $value) {
                //// the data arrays have to be integer-indexed
                //assert('is_int($y)');
                $replace_string_pieces[] = ":{$x}_{$y}";
            }
            $replace_strings[] = '('.implode(', ', $replace_string_pieces).')';
            $x++;
        }
        $statement = str_replace(array_keys($arrays), $replace_strings, $statement);
        $prepared_statement = $this->prepare($statement, $driver_options);

        // bind values to the value tokens
        $x = 0;
        foreach($arrays as $token => $data) {
            foreach($data as $y => $value) {
                $prepared_statement->bindValue(":{$x}_{$y}", $value);
            }
            $x++;
        }

        return $prepared_statement;
    }
}

You can use it like this:

$db_link = new Array_Capable_PDO($dsn, $username, $password);

$query = '
    SELECT     *
    FROM       test
    WHERE      field1 IN :array1
     OR        field2 IN :array2
     OR        field3 = :value
';

$pdo_query = $db_link->prepare_with_arrays(
    $query,
    array(
        ':array1' => array(1,2,3),
        ':array2' => array(7,8,9)
    )
);

$pdo_query->bindValue(':value', '10');

$pdo_query->execute();

User · Answer

Solution from EvilRygy didn t worked for me  In Postgres you can do another workaround     ids   array 1 2 3 7 8 9    db   new PDO        stmt    db- prepare       SELECT        FROM table      WHERE id   ANY  string to array  an array             stmt- bindParam   an array   implode       ids     stmt- execute

User · Answer

When you have other parameter  you may do like this    ids   array 1 2 3 7 8 9    db   new PDO        query    SELECT               FROM table            WHERE X    x              AND id IN     comma       for  i 0   i lt count  ids    i        query     comma   p   i            p0   p1         comma           query           stmt    db- gt prepare  query    stmt- gt bindValue   x   123       some value for  i 0   i lt count  ids    i        stmt- gt bindValue   p   i   ids  i       stmt- gt execute

User · Answer

Looking at  PDO  Predefined Constants there is no PDO  PARAM ARRAY which you would need as is listed on PDOStatement- bindParam      bool PDOStatement  bindParam   mixed  parameter   mixed  amp  variable    int  data type    int  length    mixed  driver options         So I don t think it is achievable

User · Answer

What database are you using  In PostgreSQL I like using ANY array   So to reuse your example    lt  php  ids array 1 2 3 7 8 9    db   new PDO        stmt    db- gt prepare       SELECT        FROM table      WHERE id   ANY   an array       stmt- gt bindParam  an array   ids    stmt- gt execute      gt    Unfortunately this is pretty non-portable    On other databases you ll need to make up your own magic as others have been mentioning  You ll want to put that logic into a class function to make it reusable throughout your program of course  Take a look at the comments on mysql query page on PHP NET for some more thoughts on the subject and examples of this scenario

User · Answer

I took it a bit further to get the answer closer to the original question of using placeholders to bind the params     This answer will have to make two loops through the array to be used in the query   But it does solve the issue of having other column placeholders for more selective queries      builds placeholders to insert in IN   foreach  array as  key  gt  value         in query    in query      val      key              gets rid of trailing comma and space  in query   substr  in query  0  -2     stmt    db- gt prepare       SELECT        WHERE id IN  in query       pind params for your placeholders  foreach   array as  key  gt  value         stmt- gt bindParam   val      key   array  key       stmt- gt execute

User · Answer

It s not possible to use an array like that in PDO   You need to build a string with a parameter  or use    for each value  for instance    an array 0   an array 1   an array 2   an array 3   an array 4   an array 5  Here s an example    lt  php  ids   array 1 2 3 7 8 9    sqlAnArray   join                array map          function  index                return   an array  index                      array keys  ids            db   new PDO       mysql dbname mydb host localhost        user        passwd      stmt    db- gt prepare       SELECT        FROM table      WHERE id IN    sqlAnArray        foreach   ids as  index   gt   id         stmt- gt bindValue  an array  index    id       If you want to keep using bindParam  you may do this instead   foreach   ids as  index   gt   id         stmt- gt bindParam  an array  index    ids  id        If you want to use   placeholders  you may do it like this    lt  php  ids   array 1 2 3 7 8 9    sqlAnArray         str repeat        count  ids -1    db   new PDO       mysql dbname dbname host localhost        user        passwd      stmt    db- gt prepare       SELECT        FROM phone number lookup      WHERE country code IN    sqlAnArray         stmt- gt execute  ids     If you don t know if  ids is empty  you should test it and handle that case accordingly  return an empty array  or return a Null Object  or throw an exception

User · Answer

Since I do a lot of dynamic queries  this is a super simple helper function I made    public static function bindParamArray  prefix   values   amp  bindArray         str           foreach  values as  index   gt   value            str         prefix  index               bindArray  prefix  index     value            return rtrim  str                Use it like this    bindString   helper  bindParamArray  id     GET  ids     bindArray    userConditions      AND users id IN  bindString      Returns a string  id1  id2  id3 and also updates your  bindArray of bindings that you will need when it s time to run your query  Easy

User · Answer

For me the sexier solution is to construct a dynamic associative array  amp  use it     A dirty array sent by user  dirtyArray     Cecile    Gilles    Andre    Claude        we construct an associative array like this        name 0    gt   Cecile           name 3    gt   Claude     params   array combine      array map             construct param name according to array index         function   v   return   name   v                 get values of users         array keys  dirtyArray              dirtyArray        construct the query like     WHERE name IN    name 1        name 3     query    SELECT   FROM user WHERE name IN      implode      array keys  params              here we go  stmt     db- gt prepare  query    stmt- gt execute  params

User · Answer

As I know there is no any possibility to bind an array into PDO statement   But exists 2 common solutions    Use Positional Placeholders           or Named Placeholders   id1   id2   id3    whereIn   implode      array fill 0  count  ids          Quote array earlier   whereIn   array map array  db   quote     ids     Both options are good and safe   I prefer second one because it s shorter and I can var dump parameters if I need it  Using placeholders you must bind values and in the end your SQL code will be the same    sql    SELECT   FROM table WHERE id IN   whereIn      And the last and important for me is avoiding error  number of bound variables does not match number of tokens   Doctrine it s great example of using positional placeholders  only because it has internal control over incoming parameters

User · Answer

If the column can only contain integers  you could probably do this without placeholders and just put the ids in the query directly  You just have to cast all the values of the array to integers  Like this    listOfIds   implode     array map  intval    ids     stmt    db- gt prepare       SELECT        FROM table      WHERE id IN  listOfIds       stmt- gt execute      This shouldn t be vulnerable to any SQL injection

User · Answer

you first set number of     in query  and then by a  for  send parameters like this    require  dbConnect php    db new dbConnect     array     array push  array  value1    array push  array  value2     query  SELECT   FROM sites WHERE kind IN      foreach   array as  field        query           query substr  query 0 strlen  query -1    query        tbl  db- gt connection- gt prepare  query   for  i 1  i lt  count  array   i         tbl- gt bindParam  i  array  i-1  PDO  PARAM STR    tbl- gt execute     row  tbl- gt fetchAll PDO  FETCH OBJ   var dump  row

User · Answer

A little editing about the code of Schnalle   lt  php  ids       array 1  2  3  7  8  9    inQuery   implode      array fill 0  count  ids -1           db     new PDO        stmt    db- gt prepare       SELECT        FROM table      WHERE id IN      inQuery           foreach   ids as  k   gt   id       stmt- gt bindValue   k 1    id     stmt- gt execute      gt     implode      array fill 0  count  ids -1                 this should be inside the array fill    stmt- gt bindValue   k 1    in       instead of  in  it should be  id

User · Answer

For something quick      db   new PDO          ids   array         qMarks   str repeat       count  ids  - 1          sth    db- gt prepare  SELECT   FROM myTable WHERE id IN   qMarks      sth- gt execute  ids

User · Answer

here is my solution  I have also extended the PDO class   class Db extends PDO                   SELECT     WHERE fieldName IN   paramName  workaround                param array   array         param string  prefix                return string             public function CreateArrayBindParamNames array  array   prefix    id                   newparams               foreach   array as  n   gt   val                         newparams          prefix  n                    return implode        newparams                         Bind every array element to the proper named parameter                param PDOStatement  stmt         param array         array         param string        prefix             public function BindArrayParam PDOStatement  amp  stmt  array  array   prefix    id                  foreach  array as  n   gt   val                         val   intval  val                stmt - gt  bindParam      prefix  n   val  PDO  PARAM INT                       Here is a sample usage for the above code    idList    1  2  3  4    stmt    this - gt  db - gt  prepare     SELECT      Name    FROM      User    WHERE       ID  IN     this - gt  db - gt  CreateArrayBindParamNames  idList          this - gt  db - gt  BindArrayParam  stmt   idList    stmt - gt  execute    foreach  stmt as  row        echo  row  Name        Let me know what you think

User · Answer

I also realise this thread is old but I had a unique problem where  while converting the soon-to-be deprecated mysql driver to the PDO driver I had to make a function which could build  dynamically  both normal params and INs from the same param array  So I quickly built this          mysql  pdo query  SELECT   FROM TBL WHOOP WHERE type of whoop IN  param AND siz of whoop    size   array   param    gt  array 1 2 3     size    gt  3          param  query     param  params     function pdo query  query   params   array          if   query          trigger error  Could not query nothing            Lets get our IN fields first      in fields   array        foreach  params as  field   gt   value           if is array  value                for  i 0  size sizeof  value   i lt  size  i                     in array      field  i                query   str replace  field      implode       in array        query      Lets replace the position in the query string with the full version              in fields  field     value     Lets add this field to an array for use later             unset  params  field       Lets unset so we don t bind the param later down the line                       query obj    this- gt pdo link- gt prepare  query        query obj- gt setFetchMode PDO  FETCH ASSOC           Now lets bind normal params      foreach  params as  field   gt   value   query obj- gt bindValue  field   value           Now lets bind the IN params     foreach  in fields as  field   gt   value           for  i 0  size sizeof  value   i lt  size  i                 query obj- gt bindValue  field  i   value  i       Both the named param index and this index are based off the array index which has not changed   hopefully             query obj- gt execute         if  query obj- gt rowCount    lt   0          return null       return  query obj      It is still untested however the logic seems to be there   Hope it helps someone in the same position   Edit  After some testing I found out    PDO does not like     in their names  which is kinda stupid if you ask me  bindParam is the wrong function  bindValue is the right function    Code edited to working version

User · Answer

very clean way for postgres is using the postgres-array           ids   array 1 4 7 9 45    param       implode       ids        cmd    db- gt prepare  SELECT   FROM table WHERE id   ANY         result    cmd- gt execute array  param

User · Answer

You ll have to construct the query-string   lt  php  ids       array 1  2  3  7  8  9    inQuery   implode      array fill 0  count  ids            db   new PDO        stmt    db- gt prepare       SELECT        FROM table      WHERE id IN      inQuery              bindvalue is 1-indexed  so  k 1 foreach   ids as  k   gt   id       stmt- gt bindValue   k 1    id     stmt- gt execute      gt   Both chris  comments  and somebodyisintrouble suggested that the foreach-loop              bindvalue is 1-indexed  so  k 1 foreach   ids as  k   gt   id       stmt- gt bindValue   k 1    id     stmt- gt execute         might be redundant  so the foreach loop and the  stmt- gt execute could be replaced by just      lt  php             stmt- gt execute  ids

User · Answer

After going through the same problem  i went to a simpler solution  although still not as elegant as an PDO  PARAM ARRAY would be      given the array  ids   array 2  4  32      newparams   array    foreach   ids as  n   gt   val    newparams       id  n      try        stmt    conn- gt prepare  DELETE FROM  table WHERE   table id IN      implode       newparams              foreach   ids as  n   gt   val            stmt- gt bindParam   id  n   intval  val   PDO  PARAM INT              stmt- gt execute          and so on  So if you are using a mixed values array  you will need more code to test your values before assigning the type param      inside second foreach     valuevar    is float  val    floatval  val    is int  val    intval  val     is string  val    strval  val     val     stmt- gt bindParam   id  n    valuevar   is int  val    PDO  PARAM INT    is string  val    PDO  PARAM STR   NULL       But i have not tested this one

User · Answer

Here is my solution    total items   count  array of items    question marks   array fill 0   total items         sql    SELECT   FROM foo WHERE bar IN      implode       question marks           stmt    dbh- gt prepare  sql    stmt- gt execute array values  array of items      Note the use of array values   This can fix key ordering issues   I was merging arrays of ids and then removing duplicate items   I had something like    ids   array 0   gt  23  1   gt  47  3   gt  17     And that was failing

User · Answer

Is it so important to use IN statement  Try to use FIND IN SET op   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- gt 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 SET doesn 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 implode since 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

[php] Can I bind an array to an IN() condition?

Examples related to php

Examples related to arrays

Examples related to pdo

Examples related to prepared-statement

Examples related to where-in