1

when binding a comma separated list of id's to my prepared statement i only get one row returned, whereas I expected 3.

<?php

$dbh = new PDO("mysql:host=127.0.0.1;dbname=database", "user", "password");
$stmt = $dbh->prepare('SELECT * FROM Person WHERE PersonID IN (:p)');
$stmt->bindValue(":p", "3,4,5");
$stmt->execute();
$result =  $stmt->fetchAll(PDO::FETCH_ASSOC);

?>

<pre>
<?php print_r($result); ?>
</pre>

if I change the stmt to 

SELECT * FROM Person WHERE PersonID IN (3,4,5)

i get back 3 rows as expected, I'm confused !

johowie
  • 2,475
  • 6
  • 26
  • 42

2 Answers2

3

Because bind will essentially wrap that in quotes and treat it as a single value. Then MySQL is converting it back to an integer so it still finds a match for the first item.

You need to do IN (:p1, :p2, :p3) and bind value each separately

msEmmaMays
  • 1,073
  • 7
  • 7
  • Hmmm, the number of ids varies, so should i create a loop to dynamically create all those placeholders. Or is there a better way – johowie Aug 29 '12 at 05:00
2

This should work, it dynamically builds the place holders that you should use in the statement:

$idList = array(3, 4, 5);
$argList = join(',', array_fill(0, count($idList), '?'));

$stmt = $dbh->prepare("SELECT * FROM Person WHERE PersonID IN ($argList)");
$stmt->execute($idList);
$result =  $stmt->fetchAll(PDO::FETCH_ASSOC);
Ja͢ck
  • 170,779
  • 38
  • 263
  • 309
  • isn't `PDO` is so complex and confusing at some point than `mysql_*`queries? – xkeshav Aug 29 '12 at 05:19
  • 1
    @diEcho arguable yes, but this "complexity" does come with the goodness of preventing sql injection :) – Ja͢ck Aug 29 '12 at 05:21
  • on similar note the answer by 'Chris' on a similar question looks good: [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) – johowie Aug 29 '12 at 05:45
  • @johowie Good find! I tried to look for it as well but your search terms were probably better :) – Ja͢ck Aug 29 '12 at 06:49