By Andru


2009-05-28 11:17:57 8 Comments

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?

21 comments

@RousseauAlexandre 2017-09-13 15:31:35

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

@Alive to Die 2019-04-24 05:01:56

Hard to be certain without trying it in real scenario, but seems fine.+1

@Pedro Amaral Couto 2018-03-22 13:29:00

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

@Ali Chegini 2017-08-24 07:13:08

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

@Daniel Miloca - Brazil 2015-03-18 19:16:52

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

@Piet 2016-05-03 22:42:39

Thanks for the great answer. This was the only one of all which actually did work for me. However, I saw 1 mistake. The variable $rs should be $stmt

@Daniel Miloca - Brazil 2017-06-19 16:57:18

Correct. I edited it.

@Kodos Johnson 2017-03-14 00:22:55

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.

@Aaron Angelo Vicuna 2010-02-18 15:18:29

A little editing about the code of Schnalle

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

$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

@Marcel Burkhard 2016-11-15 14:57:23

I had to remove -1 after count($ids) for it to work for me or there would always be one placeholder missing.

@Dmitry Tonkonogov 2013-10-02 23:22:49

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.

@Bill Karwin 2013-10-03 00:59:26

IN() can use an index, and counts as a range scan. FIND_IN_SET() can't use an index.

@Dmitry Tonkonogov 2013-10-08 00:03:12

That's a point. I didn't know this. But any way there is no any requirements for performance in the question. For not so big tables it's much more better and cleaner than separate class for generating query with different numbers of placeholders.

@Bill Karwin 2013-10-08 00:17:27

Yes, but who has a not-so-big table these days? ;-)

@VaL 2015-02-17 15:41:10

Another problem with this approach that what if there will be string with comma inside? For example... FIND_IN_SET(description,'simple,search') will work, but FIND_IN_SET(description,'first value,text, with coma inside') will fail. So the function will search "first value", "text", "with coma inside" instead of desired "first value", "text, with coma inside"

@Sammaye 2012-07-12 14:44:44

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

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.

@Progrock 2015-01-21 12:15:25

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.

@stefs 2009-05-28 12:02:04

i think soulmerge is right. you'll have to construct the query-string.

<?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 . ')'
);

// bindvalue is 1-indexed, so $k+1
foreach ($ids as $k => $id)
    $stmt->bindValue(($k+1), $id);

$stmt->execute();
?>

fix: dan, you were right. fixed the code (didn't test it though)

edit: both chris (comments) and somebodyisintrouble suggested that the foreach-loop ...

(...)
// bindvalue is 1-indexed, so $k+1
foreach ($ids as $k => $id)
    $stmt->bindValue(($k+1), $id);

$stmt->execute();

... might be redundant, so the foreach loop and the $stmt->execute could be replaced by just ...

<?php 
  (...)
  $stmt->execute($ids);
?>

(again, i didn't test it)

@Andru 2009-05-28 12:49:01

That's an interesting solution, and while I prefer it to iterating over the ids and calling PDO::quote(), I think the index of the '?' placeholders is going to get messed up if any other placeholders occure elsewhere in the query first, right?

@stefs 2009-05-28 13:31:25

yes, that would be a problem. but in this case you could create named parameters instead of ?'s.

@Dan 2010-07-30 09:46:49

In your second line of that code, there's a mismatched ")". The ) after the 1 should be at the end. Thanks though - very useful! :-)

@Chris 2012-05-30 03:47:50

Old question, but worth noting I believe, is that the $foreach and bindValue() is not required - just execute with the array. E.g: $stmt->execute($ids);

@Mortimer 2012-06-08 15:18:32

you might also want to check that the IDs in the array have the right type if they come from a request parameter. Using something like is_numeric($id) in the foreach.

@Xeoncross 2012-11-24 20:13:12

Generating the placeholders should be done like this str_repeat('?,', count($array) - 1). '?';

@Bill Karwin 2014-07-08 23:16:06

+1 but as a suggestion: $stmt->execute(array_values($ids)) because if you used positional parameters but $ids is an associative array it will break.

@justinl 2014-10-22 01:25:38

Just a tip for those unaware, you cannot mix named and unnamed parameters. Therefore if you use named parameters in your query, switch them out to ?'s and then augment your bindValue index offset to match the position of the IN ?'s with wherever they are relative to your other ? params.

@fyrye 2015-02-18 17:03:49

One issue to using IN in this manner is you that must know how many values there are when preparing the query. This means you must prepare another statement anytime the number of array values change.

@stefs 2015-02-20 11:36:40

@fyrye: this is true, but honestly, i've rarely got to reuse prepared statements during a single page generation cycle - i mostly use them for security reasons, not necessarily performance (i'm not sure if prep statements make a difference outside of long running applications). you could always use an array for caching them.

@fyrye 2015-02-20 22:24:07

@stefs I use it in this manner as well, providing the comment as a warning to those expecting for it to work when they change the number of values. Such as in complex object models. I also recommend your suggestion of storing the statements in an array as a workaround to the issue.

@Reality-Torrent 2016-02-05 14:44:43

If I need to insert two different comma seperated strings into two different IN statments in the sql, how do I do it then?

@cyclone200 2016-05-15 22:08:04

I had a variable $varID = '1, 2, 3, 4'; but it didn't work when I set $ids = array($varID); I used instead $ids = explode(',', $varID); and it worked. Thank you for your help.

@Eslam Sameh Ahmed 2016-07-30 03:35:28

What if i have two in clauses?

@stefs 2016-08-01 11:10:07

@EslamSamehAhmed same principle applies. construct both inQueries and array-append the ids.

@showdev 2019-06-07 09:04:17

When executing with an array of numeric IDs, "[a]ll values are treated as PDO::PARAM_STR" (PDOStatement::execute) and msql has to cast them as integers.

@Saleh Mosleh 2019-06-14 14:52:15

sorry for PDO. it does not support array values. i think they have to update it.

@Joseph_J 2014-06-26 11:12:17

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

@Oleg Matei 2014-06-20 08:41:42

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.

@ESCOBAR 2014-04-15 13:07:04

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

@Fábio Zangirolami 2018-02-22 14:10:05

it block sql injection?

@ESCOBAR 2018-02-23 12:56:21

@FábioZangirolami it is PDO, so yes.

@Fábio Zangirolami 2018-02-23 15:57:57

YES, PDO! after 4 years. Your response was fine for me, very simple and effective. thank you!!!

@prograhammer 2014-03-26 14:10:24

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!

@Dimitar Darazhanski 2015-12-06 21:04:01

This is a much better solution, since it does not break the binding of parameters rule. This is much safer than having inline sql as proposed by some others here.

@Ricalsin 2017-04-23 04:55:51

Awesomeness. Elegant. Perfect.

@Lippai Zoltan 2013-05-30 13:18:13

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 => $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'];
}

Let me know what you think

@Lippai Zoltan 2013-05-30 13:19:26

Forgot to mention that this is based on the answer of user2188977, below.

@Lippai Zoltan 2013-06-01 14:20:07

I'm not sure what is getOne(), it doesn't seem to be part of PDO. I've seen it only in PEAR. What does it do exactly?

@nullability 2013-07-12 15:25:40

@YourCommonSense can you post your user-defined function as an answer?

@Ian Brindley 2013-10-14 11:04:49

I would suggest passing the data type to BindArrayParam in the associative array as you seem to be limiting this to integers.

@alan_mm 2013-04-04 20:32:00

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.

@Chris 2012-02-18 22:00:59

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

@mpen 2012-10-21 20:35:03

why not drop an argument and do $db_link->prepare_with_arrays(array('array1'=>array(1,2,3),'‌​array2'=>array(7,8,9‌​)))? it's still unsafe if your :array is inside an SQL string (it shouldn't be replaced).

@Chris 2012-10-22 02:09:30

Yeah, that sounds better, reduces the possibility that your tokens and your data arrays don't line up. I'll probably rewrite this soon because it's getting some notice.

@Chris 2013-02-25 21:24:02

I've addressed the first part of Mark's comment, but as he pointed out, it's still not safe if a token like :array is in a string in the query.

@Your Common Sense 2013-03-30 01:56:58

A note to all future readers: This solution should never be used. Asserts are not intended for the production code

@Chris 2013-03-30 13:55:41

YCS: thanks for the feedback, interested in your opinion of the approach outside of the suitability of asserts.

@Your Common Sense 2013-03-30 14:31:52

The idea is pretty the same but just without asserts and more straightforward and explicit way - not as an exception for just a single case but as a general way of building every query. Every placeholder is marked with it's type. It makes guesswork (like if (is_array($data)) one) unnecessary yet makes data processing way more accurate.

@user2428118 2014-12-05 13:37:51

To any people reading the comments: the issue mentioned by @Your Common Sense has been fixed in revision 4.

@DonVaughn 2012-05-23 15:10:49

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

@orca 2012-05-31 02:34:11

Excellent, I had not thought to use the input_parameters argument in this way. For those whose queries have more parameters than the IN list, you can use array_unshift and array_push to add the necessary arguments to the front and end of the array. Also, I prefer $input_list = substr(str_repeat(',?', count($ids)), 1);

@orca 2012-06-01 23:30:42

You could also try str_repeat('?,', count($ids) - 1) . '?'. One less function call.

@techouse 2012-09-11 11:38:48

I used this with an array_diff to get an array which I wanted removed and it gave me an error, cause the 1st array element did not have the index value 0, therefore I had to use array_values on it first in order to reset the keys.

@erfling 2015-11-29 21:17:59

Not show sure about the security of this one. It will work, but kind of defeats the purpose of prepared statements and looks vulnerable to injection, to me.

@DonVaughn 2015-11-30 18:06:27

@erfling, this is a prepared statement, where's the injection going to come from? I'll be more than happy to make any corrections if you can you back that up with some actual proof of that.

@erfling 2015-11-30 22:43:01

Preparing the statement doesn't prevent injection. You have to bind the params. If one of the indices of $ids where DROP table users;, it might very well be executed. The most upvoted answer prevents this by looping and binding.

@DonVaughn 2015-12-01 18:18:34

@erfling, yes, that is correct, and binding the params is exactly what we are doing in this example by sending execute an array of ids

@erfling 2015-12-01 23:28:03

Oh indeed. Somehow missed the fact that you were passing the array. This does indeed appear to be safe and a good answer. My apologies.

@vinidog 2016-05-31 23:46:50

Yeahhh, worked 4 me!! thx

@Robert Rocha 2016-06-08 21:44:21

Why count ($variables) - 1) . '?'; Why not just count($variable)

@kit 2016-11-03 20:04:46

@RobertRocha because he appends '?' at the end (without the comma). Without the -1 it would be something like ... IN (?,?,?,?,?,) which is syntactically incorrect

@Alex G 2017-06-12 02:02:37

What's about multiple WHERE statements? Like "SELECT * FROM myTable WHERE name = ? AND id IN ($qMarks)"? execute(array($name, $ids)) doesn't seem to work at all....

@Miguel 2017-07-10 11:04:53

this goes against the purpouse of "safe strings" and injection...

@DonVaughn 2017-07-10 11:10:18

@Miguel, that's not correct, this is what's called using prepared statements to ensure type safety and prevent sql injection. Check out how PDOStatement::execute works here in the PHP docs

@Sergey Galkin 2010-08-18 07:14:05

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

@collimarco 2013-12-15 17:39:25

This doesn't work: ERROR: operator does not exist: integer = text. At least you need to add explicit casting.

@user83632 2009-05-28 11:48:06

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.

@soulmerge 2009-05-28 11:50:24

I don't know if that works. I would guess that the imploded string gets quoted.

@user83632 2009-05-28 13:44:58

You're correct, the quotes get escaped so that won;t work. I have removed that code.

@Ryan Bair 2009-05-28 12:08:51

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.

Related Questions

Sponsored Content

46 Answered Questions

38 Answered Questions

[SOLVED] Deleting an element from an array in PHP

  • 2008-12-15 20:28:55
  • Ben
  • 2288665 View
  • 2255 Score
  • 38 Answer
  • Tags:   php arrays

79 Answered Questions

[SOLVED] How do I remove a particular element from an array in JavaScript?

  • 2011-04-23 22:17:18
  • Walker
  • 5774576 View
  • 7205 Score
  • 79 Answer
  • Tags:   javascript arrays

39 Answered Questions

[SOLVED] Loop through an array in JavaScript

32 Answered Questions

[SOLVED] Create ArrayList from array

33 Answered Questions

[SOLVED] For-each over an array in JavaScript?

18 Answered Questions

[SOLVED] Reference — What does this symbol mean in PHP?

44 Answered Questions

[SOLVED] How to check if an object is an array?

30 Answered Questions

[SOLVED] How to append something to an array?

14 Answered Questions

[SOLVED] How to insert an item into an array at a specific index (JavaScript)?

Sponsored Content