PHP PDO: Prepared Statements with IN clause

Hi everyone,
I have been using PDO to handle my database querying, and as part of this, I have made use of prepared statements. My problem is, i’m unsure how I would use the IN clause in a prepared statement?

As far as I know, you have to prepare arrays and loop for multiple IN(?,?,?..) and values!

Yep or just revert to escaping the input and directly inserting a comma delimited list. In most cases I deal with numbers so it’s not so bad.

Really wish the designers of prepared statements had allowed for arrays.

Thanks for the responses, seems odd how the developers overlooked the IN clause when it comes to prepared statements.

Well, it is a pretty big can of worms. Also, in many cases a subquery will work better than having PHP handle the ids at all.

Id’s can be kept in an array. To include the array

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

If you are paranoid about the values you can walk them first. For example, if they must be integers…

array_walk($array, function( &$value ) { $value = (int) $value });

If you need to quote wrap them.

array_walk($array, function( &$value ) { $value = PDO::quote($value) });

And yes, that’s one of the rare instances where you’d need to use PDO::quote()

Great post @Michael Morris; I did not know how to do the last example so thanks.