PDO binding values for MySQL IN statement

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.

---

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.

---

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

---

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.

链接地址: http://www.djcxy.com/p/93656.html

上一篇: 这是正常的吗？

下一篇: MySQL IN语句的PDO绑定值