PHP - Binding array of strings to IN() clause using PDO

Question

I am struggling to bind an array of strings to an IN() clause in a MySQL statement. I have found a lot of info on this with regards to integers, but all using methods which don't work with strings.

Here's my code so far:

$dbconnect = new PDO(...);

$brands = array('Nike', 'Adidas', 'Puma');
$i = 1;

try {
    $dbconnect->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);    
    $data = $dbconnect->prepare("SELECT * FROM clothing WHERE brand IN (:brands)");
    $data->bindParam(':brands', $brands);
    $data->execute();

        while($row = $data->fetch()) {
            echo $i++ . " - ". $row['brand'] . "<br>";
        }

    } catch(PDOException $er) {
        echo 'Error: ' . $er->getMessage();

}

Thanks in advance for any help!

You could try [this answer's solution](http://stackoverflow.com/a/920523/2812842) but wrap strings around the values — scrowler, Jul 08 '14 at 22:57
possible duplicate http://stackoverflow.com/questions/920353/can-i-bind-an-array-to-an-in-condition/920523#920523 — ArtisticPhoenix, Jul 08 '14 at 23:02
Works great - thanks. Did try this briefly before but didn't seem to work. Must have been an error on my part! Thanks again — Ross, Jul 08 '14 at 23:07

Ray · Answer 1 · 2014-07-08T23:12:57.300

I like to loop through and create an associative array of the values. Then concatenate the keys to a string of params to bind. Then loop through the associative array to bind them to the prepared statement. Something along the following (there may be some syntax errors, but this is the gist):

 $in_clause_array  = array();
 foreach($brands as $index => $brand) {
      $in_clause_array[':brand_'.$index] = $brand;
 } 

 //Below creates a string like ":brand_1 , :brand_2 , :brand_3"
 $in_clause_string = implode(" , ", array_keys($in_clause_array));

 $data = $dbconnect->prepare("SELECT * FROM clothing WHERE brand IN ( $in_clause_string )");

 //now bind the params to values in the associative array
 foreach($in_clause_array as $key=>$brand) {
         $data->bindValue("$key", $brand);
 }

score -1 · Answer 2 · answered Jul 08 '14 at 23:03

-1

    $array_fields = array();
    $i=0;
    $res = $db->query("yourkey ".TABLE_ADS);
    while($row = mysql_fetch_row($res)) { 
        $array_fields[$i] = $row[0];
        $i++;
    }

answered Jul 08 '14 at 23:03

John Stamoutsos

353
1
3
17

PHP - Binding array of strings to IN() clause using PDO

2 Answers2