PHP PDO: Prepared Statements with IN clause

PHP
1

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?
Thanks.

2

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

3

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.

4

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

5

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

6

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