PDO binding values for MySQL IN statement

Question

I have an issue with PDO that I'd really like to get an answer for after being plagued by it for quite some time.

Take this example:

I am binding an array of ID's to a PDO statement for use in a MySQL IN statement.

The array would be say: $values = array(1,2,3,4,5,6,7,8);

The database-safe variable would be $products = implode(',' $values);

So, $products would then be a STRING with a value of: '1,2,3,4,5,6,7,8'

The statement would look like:

SELECT users.id
FROM users
JOIN products
ON products.user_id = users.id
WHERE products IN (:products)

Of course, $products would be bound to the statement as :products.

However, when the statement is compiled and values bound, it would actually look like this:

SELECT users.id
FROM users
JOIN products
ON products.user_id = users.id
WHERE products IN ('1,2,3,4,5,6,7,8')

The problem is it is executing everything inside of the IN statement as a single string, given that I've prepared it as comma-separated values to bind to the statement.

What I actually need is:

SELECT users.id
FROM users
JOIN products
ON products.user_id = users.id
WHERE products IN (1,2,3,4,5,6,7,8)

The only way I can actually do this is by placing the values within the string itself without binding them, however I know for certain there has to be an easier way to do this.

Answer 1

This is the same thing as was asked in this question: Can I bind an array to an IN() condition?

The answer there was that, for a variable sized list in the in clause, you'll need to construct the query yourself.

However, you can use the quoted, comma-separated list using find_in_set, though for large data sets, this would have considerable performance impact, since every value in the table has to be cast to a char type.

For example:

select users.id
from users
join products
on products.user_id = users.id
where find_in_set(cast(products.id as char), :products)

Or, as a third option, you could create a user defined function that splits the comma-separated list for you (cf. http://www.slickdev.com/2008/09/15/mysql-query-real-values-from-delimiter-separated-string-ids/). This is probably the best option of the three, especially if you have a lot of queries that rely on in(...) clauses.

Answer 2

You can do so very easily. If you have an array of values for your IN() statement EG:

$test = array(1,2,3);

You can simply do

$test = array(1,2,3);
$values = count($test);
$criteria = sprintf("?%s", str_repeat(",?", ($values ? $values-1 : 0)));
//Returns ?,?,?
$sql = sprintf("DELETE FROM table where column NOT IN(%s)", $criteria);
//Returns DELETE FROM table where column NOT IN(?,?,?)
$pdo->sth = prepare($sql);
$pdo->sth->execute($test);