Andru
Andru

Reputation: 7131

Can I bind an array to an IN() condition in a PDO query?

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:

<?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?

Upvotes: 640

Views: 255950

Answers (24)

user1719210
user1719210

Reputation: 328

If you want to use bindParam, you can do it like this :

$values = array(1,2,3); //ie -> your own values
$pfx= "id";
$in= "";
$valuestobind = array();


foreach($values as $k => $v){
$in.= ":".$pfx.$k.",";
$valuestobind[$pfx.$k] = $v;
}

$in = rtrim($in,",");  

$request = "SELECT * FROM yourtable WHERE id IN ($in)";

Then when you will bind the params, don't forget to pass the values by reference (if you don't you will only get the last result) :

foreach($valuestobind as $k=>&$v){
$b->bindParam(':'.$k, $v,PDO::PARAM_INT);
}

Upvotes: 0

Aaron Angelo Vicuna
Aaron Angelo Vicuna

Reputation: 89

A little editing about the code of Schnalle

<?php
$ids     = array(1, 2, 3, 7, 8, 9);
$inQuery = implode(',', array_fill(0, count($ids), '?'));

$db   = new PDO(...);
$stmt = $db->prepare(
    'SELECT *
     FROM table
     WHERE id IN(' . $inQuery . ')'
);

foreach ($ids as $k => $id)
    $stmt->bindValue(($k+1), $id);

$stmt->execute();
?>

//implode(',', array_fill(0, count($ids)-1), '?')); 
//'?' this should be inside the array_fill
//$stmt->bindValue(($k+1), $in); 
// instead of $in, it should be $id

Upvotes: 8

stefs
stefs

Reputation: 18549

You'll have to construct the list of placeholders manually, adding a placeholder for each array member.

<?php
$ids     = [1, 2, 3, 7, 8, 9];
$inQuery = str_repeat('?,', count($arr) - 1) . '?'; // gets ?,?,?,?,?,?

$stmt = $db->prepare("SELECT * FROM table WHERE id IN($inQuery)");
$stmt->execute($ids);
$data = $stmt->fetchAll();

Given $inQuery doesn't take any input and fully constructed from constant values (?, parts), it's safe to add such a variable in the query.

In case there are other placeholders in the query, you could use array_merge() function to join all the variables into a single array, adding your other variables in the form of arrays, in the order they appear in your query:

$arr = [1,2,3];
$in  = str_repeat('?,', count($arr) - 1) . '?';
$sql = "SELECT * FROM table WHERE foo=? AND column IN ($in) AND bar=? AND baz=?";
$stmt = $db->prepare($sql);
$params = array_merge([$foo], $arr, [$bar, $baz]);
$stmt->execute($params);
$data = $stmt->fetchAll();

In case you are using named placeholders, the code would be a little more complex, as you have to create a sequence of the named placeholders, e.g. :id0,:id1,:id2. So the code would be:

// other parameters that are going into query
$params = ["foo" => "foo", "bar" => "bar"];

$ids = [1,2,3];
$in = "";
$i = 0; // we are using an external counter 
        // because the actual array keys could be dangerous
foreach ($ids as $item)
{
    $key = ":id".$i++;
    $in .= ($in ? "," : "") . $key; // :id0,:id1,:id2
    $in_params[$key] = $item; // collecting values into a key-value array
}

$sql = "SELECT * FROM table WHERE foo=:foo AND id IN ($in) AND bar=:bar";
$stmt = $db->prepare($sql);
$stmt->execute(array_merge($params, $in_params)); // just merge two arrays
$data = $stmt->fetchAll();

Luckily, for the named placeholders we don't have to follow the strict order, so we can merge our arrays in any order.

Upvotes: 305

Lippai Zoltan
Lippai Zoltan

Reputation: 298

Here is my solution, based on alan_mm's answer. 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 => $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 &$stmt, array $array, $prefix = 'id_')
    {
        foreach($array as $n => $val)
        {
            $val = intval($val);
            $stmt -> bindParam(":".$prefix.$n, $val, PDO::PARAM_INT);
        }
    }
}

Here is a sample usage for the above code:

$idList = [1, 2, 3, 4];
$stmt = $this -> db -> prepare("
  SELECT
    `Name`
  FROM
    `User`
  WHERE
    (`ID` IN (".$this -> db -> CreateArrayBindParamNames($idList)."))");
$this -> db -> BindArrayParam($stmt, $idList);
$stmt -> execute();
foreach($stmt as $row)
{
    echo $row['Name'];
}

Upvotes: 2

Mague
Mague

Reputation: 9

You could convert this:

$stmt = $db->prepare('SELECT * FROM table WHERE id IN('.$in.')');

In this:

$stmt = $db->prepare('SELECT * FROM table WHERE id IN(:id1, :id2, :id3, :id7, :id8, :id9)');

And execute it with this array:

$stmt->execute(array(
        :id1 =>1, :id2 =>2, :id3 =>3, :id7 =>7, :id8 =>8, :id9 => 9
    )
);

Thus:

$in = array();
$consultaParam = array();
foreach($ids as $k => $v){
    $in[] = ':id'.$v;
    $consultaParam[':id'.$v] = $v;
}

Final code:

$ids = array(1,2,3,7,8,9);
$db = new PDO(...);

$in = array();
$consultaParam = array();
foreach($ids as $k => $v){
    $in[] = ':id'.$v;
    $consultaParam[':id'.$v] = $v;
}

$stmt = $db->prepare(
    'SELECT *
     FROM table
     WHERE id IN('.$in.')'
);
$stmt->execute($consultaParam);

Upvotes: -1

Sammaye
Sammaye

Reputation: 43884

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 parameters and INs from the same parameter 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' => array(1,2,3), ':size' => 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 => $value){
        if(is_array($value)){
            for($i=0,$size=sizeof($value);$i<$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->pdo_link->prepare($query);
    $query_obj->setFetchMode(PDO::FETCH_ASSOC);

    // Now lets bind normal params.
    foreach($params as $field => $value) $query_obj->bindValue($field, $value);

    // Now lets bind the IN params
    foreach($in_fields as $field => $value){
        for($i=0,$size=sizeof($value);$i<$size;$i++)
            $query_obj->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->execute();

    if($query_obj->rowCount() <= 0)
        return null;

    return $query_obj;
}

It is still untested however the logic seems to be there.

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.

Upvotes: 9

JCH77
JCH77

Reputation: 1363

With MySQL and PDO we can use a JSON array and JSON_CONTAINS() (https://dev.mysql.com/doc/refman/8.0/en/json-search-functions.html#function_json-contains) to search in.

$ids = [123, 234, 345, 456]; // Array of users I search
$ids = json_encode($ids); // JSON conversion

$sql = <<<SQL
    SELECT ALL user_id, user_login
    FROM users
    -- Cast is mandatory beaucause JSON_CONTAINS() waits JSON doc candidate
    WHERE JSON_CONTAINS(:ids, CAST(user_id AS JSON))
    SQL;

$search = $pdo->prepare($sql);
$search->execute([':ids' => $ids]);
$users = $search->fetchAll();

Whe can also use JSON_TABLE() (https://dev.mysql.com/doc/refman/8.0/en/json-table-functions.html#function_json-table) for more complex cases and JSON data exploration :

$users = [
    ['id' => 123, 'bday' => ..., 'address' => ...],
    ['id' => 234, 'bday' => ..., 'address' => ...],
    ['id' => 345, 'bday' => ..., 'address' => ...],
]; // I'd like to know their login

$users = json_encode($users);

$sql = <<<SQL
    SELECT ALL user_id, user_login
    FROM users
    WHERE user_id IN (
        SELECT ALL user_id
        FROM JSON_TABLE(:users, '$[*]' COLUMNS (
            -- Data exploration...
            -- (if needed I can explore really deeply with NESTED kword)
            user_id INT PATH '$.id',
            -- I could skip these :
            user_bday DATE PATH '$.bday',
            user_address TINYTEXT PATH '$.address'
        )) AS _
    )
    SQL;

$search = $pdo->prepare($sql);
$search->execute([':users' => $users]);
...

Upvotes: 2

alexandre-rousseau
alexandre-rousseau

Reputation: 2860

For me the sexier solution is to construct a dynamic associative array & use it

// A dirty array sent by user
$dirtyArray = ['Cecile', 'Gilles', 'Andre', 'Claude'];

// we construct an associative array like this
// [ ':name_0' => 'Cecile', ... , ':name_3' => '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->prepare($query);
$stmt->execute($params);

Upvotes: 12

Pedro Amaral Couto
Pedro Amaral Couto

Reputation: 2125

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:

<?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->prepare(
    'SELECT *
     FROM table
     WHERE id IN('.$sqlAnArray.')'
);
foreach ($ids as $index => $id) {
    $stmt->bindValue("an_array_$index", $id);
}

If you want to keep using bindParam, you may do this instead:

foreach ($ids as $index => $id) {
    $stmt->bindParam("an_array_$index", $ids[$id]);
}

If you want to use ? placeholders, you may do it like this:

<?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->prepare(
    'SELECT *
     FROM phone_number_lookup
     WHERE country_code IN('.$sqlAnArray.')'
);
$stmt->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, ...).

Upvotes: 5

Ali Chegini
Ali Chegini

Reputation: 59

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->connection->prepare($query);
for($i=1;$i<=count($array);$i++)
    $tbl->bindParam($i,$array[$i-1],PDO::PARAM_STR);
$tbl->execute();
$row=$tbl->fetchAll(PDO::FETCH_OBJ);
var_dump($row);

Upvotes: 0

Daniel Miloca - Brazil
Daniel Miloca - Brazil

Reputation: 155

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<count($ids); $i++){
  $query .= $comma.':p'.$i;       // :p0, :p1, ...
  $comma = ',';
}
$query .= ')';

$stmt = $db->prepare($query);
$stmt->bindValue(':x', 123);  // some value
for($i=0; $i<count($ids); $i++){
  $stmt->bindValue(':p'.$i, $ids[$i]);
}
$stmt->execute();

Upvotes: 12

Cave Johnson
Cave Johnson

Reputation: 6788

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->prepare(
    "SELECT *
     FROM table
     WHERE id IN($listOfIds)"
);
$stmt->execute();

This shouldn't be vulnerable to any SQL injection.

Upvotes: 7

Tim Tonkonogov
Tim Tonkonogov

Reputation: 1117

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->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.

  1. 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.
  2. 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.

Upvotes: 48

Progrock
Progrock

Reputation: 7485

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->prepare($sql);
$stmt->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 => 23, 1 => 47, 3 => 17);

And that was failing.

Upvotes: 16

Joseph_J
Joseph_J

Reputation: 3669

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=>$value) {
    $in_query = $in_query . ' :val_' . $key . ', ';
}

//gets rid of trailing comma and space
$in_query = substr($in_query, 0, -2);

$stmt = $db->prepare(
    "SELECT *
     WHERE id IN($in_query)";

//pind params for your placeholders.
foreach ($array as $key=>$value) {
    $stmt->bindParam(":val_" . $key, $array[$key])
}

$stmt->execute();

Upvotes: -1

Oleg Matei
Oleg Matei

Reputation: 906

As I know there is no any possibility to bind an array into PDO statement.

But exists 2 common solutions:

  1. Use Positional Placeholders (?,?,?,?) or Named Placeholders (:id1, :id2, :id3)

    $whereIn = implode(',', array_fill(0, count($ids), '?'));

  2. 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.

Upvotes: 5

Maik
Maik

Reputation: 616

very clean way for postgres is using the postgres-array ("{}"):

$ids = array(1,4,7,9,45);
$param = "{".implode(', ',$ids)."}";
$cmd = $db->prepare("SELECT * FROM table WHERE id = ANY (?)");
$result = $cmd->execute(array($param));

Upvotes: 21

prograhammer
prograhammer

Reputation: 20630

Since I do a lot of dynamic queries, this is a super simple helper function I made.

public static function bindParamArray($prefix, $values, &$bindArray)
{
    $str = "";
    foreach($values as $index => $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!

Upvotes: 44

alan_mm
alan_mm

Reputation: 39

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 => $val){ $newparams[] = ":id_$n"; }

try {
    $stmt = $conn->prepare("DELETE FROM $table WHERE ($table.id IN (" . implode(", ",$newparams). "))");
    foreach ($ids as $n => $val){
        $stmt->bindParam(":id_$n", intval($val), PDO::PARAM_INT);
    }
    $stmt->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->bindParam(":id_$n", $valuevar, (is_int($val) ? PDO::PARAM_INT :  is_string($val) ? PDO::PARAM_STR : NULL ));

But i have not tested this one.

Upvotes: 3

Chris
Chris

Reputation: 7298

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();

Upvotes: 12

uɥƃnɐʌuop
uɥƃnɐʌuop

Reputation: 15163

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);

Upvotes: 195

Sergey Galkin
Sergey Galkin

Reputation: 179

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();

Upvotes: 17

user83632
user83632

Reputation:

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 &$variable [, int $data_type [, int $length [, mixed $driver_options ]]] )

So I don't think it is achievable.

Upvotes: 12

Ryan Bair
Ryan Bair

Reputation: 2634

What database are you using? In PostgreSQL I like using ANY(array). So to reuse your example:

<?php
$ids=array(1,2,3,7,8,9);
$db = new PDO(...);
$stmt = $db->prepare(
    'SELECT *
     FROM table
     WHERE id = ANY (:an_array)'
);
$stmt->bindParam('an_array',$ids);
$stmt->execute();
?>

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.

Upvotes: 6

Related Questions