PDO Binding Values ​​for MySQL IN

Question

I have a problem with PDO that I really would like to get an answer for having tormented you for so long.

Take this example:

I am associating an ID array with a PDO statement for use in a MySQL IN statement.

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

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

So $ products will then be STRING with the value: '1,2,3,4,5,6,7,8'

The operation will look like this:

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

Of course, $ products will be tied to an expression like : products .

However, when the operator compiles and values ​​are bound, it will look something 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 that it executes everything inside the IN statement as a single line, given that I prepared it as comma-separated values ​​for binding to the instruction.

I really need:

 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 really do this is to put the values ​​inside the string itself without linking them, however I know that there must be an easier way to do this for this.

Answer 1

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

The answer was that for a variable-sized list in the in clause, you would need to create a query yourself.

However, you can use a comma-separated list with codes using find_in_set , although for large datasets, this will have a significant performance impact, since each value in the table must be cast for char.

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 can create a user-defined function that splits a comma-separated list (see http://www.slickdev.com/2008/09/15/mysql-query-real-values-from-delimiter- separated-string-ids / ). This is probably the best option for the three, especially if you have a lot of queries based on in(...) clauses.

Answer 2

A good way to handle this situation is to use str_pad to host ? for each value in the SQL query. Then you can pass an array of values ​​(in your case $values ) as an 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(); 

Thus, you get more benefits from using prepared statements, rather than pasting values ​​directly into SQL.

PS. Results return duplicate user identifiers if products with the specified identifiers share user identifiers. If you need only unique user identifiers, I suggest changing the first line of the query to SELECT DISTINCT users.id

Answer 3

The best prepared statement that you could probably find in such a situation is similar to the following:

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

Then you skip your values ​​and bind them to the prepared statement, making sure that there are as many question marks as the values ​​that you associate.

Answer 4

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

can this be done easily by creating an array? s how

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

and use this $ array in your IN section

Will it dynamically provide you with an individual array ? s according to your changing $ values ​​array

Answer 5

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

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

You can just 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); 

Answer 6

If the expression is based on user input without binding values ​​with bindValue (), experimental SQL may not be a great choice. But you can make it safe by checking input syntax with MySQL REGEXP.

For example:

 SELECT * FROM table WHERE id IN (3,156) AND '3,156' REGEXP '^([[:digit:]]+,?)+$' 

Answer 7

Here's an example of binding an unknown number of record columns to values ​​to insert.

 public function insert($table, array $data) { $sql = "INSERT INTO $table (" . join(',', array_keys($data)) . ') VALUES (' . str_repeat('?,', count($data) - 1). '?)'; if($sth = $this->db->prepare($sql)) { $sth->execute(array_values($data)); return $this->db->lastInsertId(); } } $id = $db->insert('user', array('name' => 'Bob', 'email' => '[e-mail]')); 

Answer 8

Please try like this:

 WHERE products IN(REPLACE(:products, '\'', '')) 

Hello