17

My code:

$myArray = implode($myArray, ',');
$sth = $dbh->prepare('SELECT foo FROM bar WHERE ids IN (:ids)');
$sth->bindParam(':ids', $myArray);
$sth->execute();
$result = $sth->fetch();
echo $sth->rowCount();

Always shows a count of 1, but when I skip the parametrization and just add the variable itself in it's place, I get an accurate count. What's going on here?

Radu
  • 8,561
  • 8
  • 55
  • 91

3 Answers3

11

I know this question is old, but this works for me: 

$arrayOfValues = array(1,2,3,4,5);
$questionMarks = join(",", array_pad(array(), count($arrayOfValues), "?"));
$stmt = $dbh->prepare("update some_table set end_date = today where value_no in ($questionMarks)");
$stmt->execute($arrayOfValues);
gusknows
  • 121
  • 1
  • 5
11

You can't bind a parameter for the IN clause like that. The $myArray string will only count as one value, like if you did this:

SELECT foo FROM bar WHERE ids IN ('1,2,3')

Even though there are three comma delimited values, the database reads them as only one string value.

You need to manually insert the IN list into the query, the old-school way.

'SELECT foo FROM bar WHERE ids IN (' . $myArray .')'

There is unfortunately no other way. At least for now.

Atli
  • 7,855
  • 2
  • 30
  • 43
  • 14
    Seriously? Prepared statements can't handle that? How lame is that :/ – hoppa Aug 12 '11 at 17:55
  • 5
    @hoppa: they can handle it like this: `id IN (:id1, :id2, :id3...)`, you get the pattern. You're better off with the oldschool method. – Maerlyn Aug 12 '11 at 17:57
  • 2
    This question confirms it: http://stackoverflow.com/questions/920353/php-pdo-can-i-bind-an-array-to-an-in-condition – Jared Ng Aug 12 '11 at 17:58
  • @Jared, I guess my question is a duplicate then. Should've searched more :( – Radu Aug 12 '11 at 18:00
  • No worries, that question was from 2009 anyway. For all you know PDO might have patched in support for this. I wouldn't consider it a duplicate considering the age of the other question :) – Jared Ng Aug 12 '11 at 18:03
  • Yes you can do it, just use array_fill to make an array of parameter length with ? and implode it into the query – ladieu May 23 '22 at 17:29
  • Remember it's just a string.. its up to you to build it correctly – ladieu May 23 '22 at 17:50
-2

You simply need a string with as many question marks as you have parameters for your in clause... easily possible

    <?php 
        
        $sql = "select * from something where id in (".implode(",", array_fill(0, count($parameters), "?")).")";
    
$sth = $db->prepare($sql);
        
        $sth->execute( $parameters);
        
        ?>
ladieu
  • 127
  • 5
  • 17