PDO binding values for MySQL IN statement [duplicate]

Solution 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.

Solution 2:

A good way to handle this situation is to use str_pad to place a ? for every value in the SQL query. Then you can pass the array of values (in your case $values) as the argument to execute:

$sql = '
SELECT users.id
FROM users
JOIN products
ON products.user_id = users.id
WHERE products.id IN ('.str_pad('',count($values)*2-1,'?,').')
';

$sth = $dbh->prepare($sql);
$sth->execute($values);
$user_ids = $sth->fetchAll();

This way you get more benefit from using prepared statements rather than inserting the values directly into the SQL.

PS - The results will return duplicate user ids if the products with the given ids share user ids. If you only want unique user ids I suggest changing the first line of the query to SELECT DISTINCT users.id

Solution 3:

you need to provide same number of ?s in IN as the number of values in your $values array

this can be done easily by creating an array of ?s as

 $in = join(',', array_fill(0, count($values), '?'));

and use this $in array in your IN clause

THis will dynamically provide you with tailor made array of ?s as per your changiing $values array

Solution 4:

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.