why is PDO returning only one row here?

Question

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 !

hav you try with argument PDO::PARAM_STR in bindValue()

xkeshav
– xkeshav

2012-08-29 04:53:48 +00:00
Commented Aug 29, 2012 at 4:53 — xkeshav
– xkeshav, Commented Aug 29, 2012 at 4:53

msEmmaMays · Accepted Answer · 2012-08-29 04:53:18Z

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

answered Aug 29, 2012 at 4:53

msEmmaMays

1,0637 silver badges7 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

johowie Over a year ago

Hmmm, the number of ids varies, so should i create a loop to dynamically create all those placeholders. Or is there a better way

Ja͢ck · Accepted Answer · 2012-08-29 05:12:00Z

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

answered Aug 29, 2012 at 5:12

Ja͢ck

174k39 gold badges271 silver badges318 bronze badges

4 Comments

xkeshav Over a year ago

isn't PDO is so complex and confusing at some point than mysql_*queries?

Ja͢ck Over a year ago

@diEcho arguable yes, but this "complexity" does come with the goodness of preventing sql injection :)

johowie Over a year ago

on similar note the answer by 'Chris' on a similar question looks good: PHP PDO: Can I bind an array to an IN() condition?

Ja͢ck Over a year ago

@johowie Good find! I tried to look for it as well but your search terms were probably better :)

Collectives™ on Stack Overflow

why is PDO returning only one row here?

2 Answers 2

1 Comment

4 Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

4 Comments

Linked

Related