PDO - queries using IN

[Albright]

Using PDO's prepare() and bindParam() functions, is it possible to execute a SQL query using IN? For example:

 

SELECT * FROM table WHERE id IN (0, 1, 2);

 

Now of course this is simple if the number of values in the parentheses is constant, but it won't always be.

 

In other words, I want to know the best way to do something like…

 

$query = "SELECT * FROM table WHERE id IN (" . implode(',', $ids) . ")";

 

…but sticking to PDO conventions.

[trq]

I would think....

 

<?php

$stmnt = $db->prepare("SELECT * FROM table WHERE id IN (?)";
$stmnt->execute(implode(',',$ids));

?>

[Albright]

Sorry for the late reply, but I took a day off from this project and there was a bit of work I had to get through before I could check if that worked. Which, by the way, it doesn't…

 

I'm guessing it's because PDO is being too smart and seeing that implode(',', $ids) is returning a string, and therefore escaping the string -- so the query that's hitting the database server is "SELECT * FROM table WHERE id IN ('1,2,3')". That ain't gonna work!

 

Hmm. I'm going to see if I can maybe extend PDOStatement and add a bindArray() function or something. Hopefully that won't be too difficult. It would be great if this functionality were built in, though.

[Albright]

By the way, the execute call expects an array, so the code looked more like…

<?php
$stmnt->execute(array(implode(',', $ids)));

 

It looks like trying to extend PDOStatement will require extending PDO and so on and so forth… and be more trouble than it's worth. What I guess I'll do instead is something like…

 

<?php
$qmarks = "";
$until = count($ids) - 1;
if ($until > -1) {
for ($x = 0; $x < $until; $x++) {
  $qmarks .= "?,";
}
$qmarks .= "?";
}
$stmnt = $db->prepare("SELECT * FROM table WHERE id IN ({$qmarks})");
$stmnt->execute($ids);