PDO binding values for MySQL IN statement

Question

Hi,

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.

I must be completely missing the mark here and I know I'm going to kick myself when I see what the answer is!

Thanks in advance.

Answer 1

The best prepared statement you could probably come up with in a situation like this is something resembling the following:

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

You would then loop through your values and bind them to the prepared statement making sure that there are the same number of question marks as values you are binding.

Answer 2

This is the same thing as was asked in this question: http://stackoverflow.com/questions/920353/php-pdo-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.