MySQL Prepared statements with a variable size variable list

by

I can think of a couple solutions.

One solution might be to create a temporary table. Do an insert into the table for each parameter that you would have in the in clause. Then do a simple join against your temporary table.

Another method might be to do something like this.

$dbh=new PDO($dbConnect, $dbUser, $dbPass);
$parms=array(12, 45, 65, 33);
$parmcount=count($parms);   // = 4
$inclause=implode(',',array_fill(0,$parmcount,'?')); // = ?,?,?,?
$sql="SELECT age, name FROM people WHERE id IN (%s)";
$preparesql=sprintf($sql,$inclause);  // = example statement used in the question
$st=$dbh->prepare($preparesql);
$st->execute($parms);

I suspect, but have no proof, that the first solution might be better for larger lists, and the later would work for smaller lists.

To make @orrd happy here is a terse version.

$dbh=new PDO($dbConnect, $dbUser, $dbPass);
$parms=array(12, 45, 65, 33);
$st=$dbh->prepare(sprintf('SELECT age, name FROM people WHERE id IN (%s)',
                          implode(',',array_fill(0,count($parms),'?'))));
$st->execute($parms);

More Related Contents:

  1. Order results from two queries based on time in second query [closed]
  2. Query database then email posts from where user is subbed to
  3. Save the data into a database and return another value from database using PHP
  4. How can I prevent SQL injection in PHP?
  5. How can I do ‘insert if not exists’ in MySQL?
  6. Can I parameterize the table name in a prepared statement?
  7. Getting raw SQL query string from PDO prepared statements
  8. MySQLi prepared statements error reporting [duplicate]
  9. Correct way to use LIKE ‘%{$var}%’ with prepared statements?
  10. Achieve hierarchy, Parent/Child Relationship in an effective and easy way
  11. PDO with INSERT INTO through prepared statements [closed]
  12. How to search for slash (\) in MySQL? and why escaping (\) not required for where (=) but for Like is required?
  13. pdo prepared statements with wildcards
  14. I cannot get my login form to connect interact properly with mySQL database [closed]
  15. SQLSTATE[HY093]: Invalid parameter number: number of bound variables does not match number of tokens on line 102 [closed]
  16. Comma separated values in MySQL “IN” clause
  17. mysql_num_rows(): supplied argument is not a valid MySQL result resource [duplicate]
  18. Increment value in MySQL update query
  19. Doing a while / loop to get 10 random results
  20. MYSQL Select from tables based on multiple rows
  21. How to execute raw queries with Laravel 5.1?
  22. How do you do multiple SQL statements in one mysql_query?
  23. MySQL/SQL retrieve first 40 characters of a text field?
  24. Fatal error: Call to a member function query() on null
  25. Using Prepared Statement, how I return the id of the inserted row?
  26. how do I get month from date in mysql
  27. php/mysql zip code proximity search
  28. Updating column so that it contains the row position
  29. Aggregated query without GROUP BY
  30. Limit pagination page number

Leave a Comment