PDO binding values for MySQL IN statement [duplicate]

by

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.

More Related Contents:

  1. mysqli or PDO – what are the pros and cons? [closed]
  2. PDOException SQLSTATE[HY000] [2002] No such file or directory
  3. How to get numeric types from MySQL using PDO?
  4. How can I use PDO to fetch a results array in PHP?
  5. Why I am getting Cannot pass parameter 2 by reference error when I am using bindParam with a constant value?
  6. Causes of MySQL error 2014 Cannot execute queries while other unbuffered queries are active
  7. mysql PDO how to bind LIKE
  8. Use bound parameter multiple times
  9. What is the difference between MySQL, MySQLi and PDO? [closed]
  10. how safe are PDO prepared statements
  11. Can I use a PDO prepared statement to bind an identifier (a table or field name) or a syntax keyword?
  12. PHP PDO – Num Rows
  13. How to solve General error: 2006 MySQL server has gone away
  14. Replacing mysql_* functions with PDO and prepared statements
  15. Understanding pdo mysql transactions
  16. Calling stored procedure with Out parameter using PDO
  17. When *not* to use prepared statements?
  18. How to insert an array into a single MySQL Prepared statement w/ PHP and PDO
  19. Setting a connect timeout with PDO
  20. In PHP with PDO, how to check the final SQL parametrized query? [duplicate]
  21. What is the best way to insert multiple rows in PHP PDO MYSQL?
  22. PDO MySQL: Insert multiple rows in one query
  23. How to bind LIKE values using the PDO extension?
  24. Error while using PDO prepared statements and LIMIT in query [duplicate]
  25. Warning: PDO::__construct(): [2002] No such file or directory (trying to connect via unix:///tmp/mysql.sock) in
  26. SQLSTATE[HY093]: Invalid parameter number: parameter was not defined
  27. PDO::rowCount VS COUNT(*)
  28. PHP 7 RC3: How to install missing MySQL PDO
  29. PHP: mysql v mysqli v pdo [closed]
  30. PDO and MySQL ‘between’

Leave a Comment