0

Possible Duplicate:
PHP PDO: Can I bind an array to an IN() condition?

Alright, this is really bothering me. How can I bind a paramater (which has multiple values) for an SQL "IN" statment in PHP's PDO?

Here are the basics...

$allow = "'red', 'blue'";

SELECT * FROM colors WHERE type IN (:allow);

$stmt->bindParam(':allow', $allow);

This works fine when I plug in $allow by itself, but when trying to bind it and use :allow it fails to work. Does anyone know why this is?

Note: I do have the rest of the PDO properly set with other variables (not strings) working, I just didn't include it because it's unnecessary.

halfer
  • 19,824
  • 17
  • 99
  • 186
Ian
  • 1,850
  • 6
  • 23
  • 38
  • 3
    See [PHP PDO: Can I bind an array to an IN() condition?](http://stackoverflow.com/questions/920353/php-pdo-can-i-bind-an-array-to-an-in-condition). You have to bind each value individually, otherwise the database will try to match on the string "'red', 'blue'", not the two strings "red" and "blue". – deceze May 30 '12 at 00:51
  • 3
    Any here's why people who claim that parameterized queries are the solutions to all of live's problems quietly slink off into the darkness... – Marc B May 30 '12 at 01:18

2 Answers2

3

What deceze said in the comments is correct. Here is a way i've done this before.

Basically you create the IN part of the sql string by looping the array values and adding in a binded name.

$allow = array( 'red', 'blue' );

$sql = sprintf(
    "Select * from colors where type in ( %s )",
    implode(
        ',',
        array_map(
            function($v) {
                static $x=0;
                return ':allow_'.$x++;
            },
            $allow
        )
    )
);

This results in Select * from colors where type in ( :allow_0,:allow_1 )

Then just loop the $allow array and use bindValue to bind each variable.

foreach( $allow as $k => $v ){
    $stmnt->bindValue( 'allow_'.$k, $v );
}

I added this before realizing deceze linked to a question that gave a similar example. Ill leave this here because it shows how to do it with named binded variables and not ?s

Galen
  • 29,976
  • 9
  • 71
  • 89
-1

The bind functions will treat the entire list as a string, not multiple discrete values.

Ray
  • 40,256
  • 21
  • 101
  • 138