code_junkie

MySQL Prepared statements with a variable size variable list

Nov 12th, 2011
71
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
text 1.12 KB | None | 0 0
  1. $dbh=new PDO($dbConnect, $dbUser, $dbPass);
  2. $parms=array(12, 45, 65, 33);
  3. $parmcount=count($parms); // = 4
  4. $inclause=implode(',',array_fill(0,$parmcount,'?')); // = ?,?,?,?
  5. $sql='SELECT age, name FROM people WHERE id IN (%s)';
  6. $preparesql=sprintf($sql,$inclause); // = example statement used in the question
  7. $st=$dbh->prepare($preparesql);
  8. $st->execute($parms);
  9.  
  10. $sql = "... WHERE id IN (?)";
  11. $values = array(1, 2, 3, 4);
  12. $result = $dbw -> prepare ($sql, $values) -> execute ();
  13.  
  14. function convertToInt(&$value, $key)
  15. {
  16. $value = intval($value);
  17. }
  18.  
  19. $ids = array('12', '45', '65', '33');
  20. array_walk($ids, 'convertToInt');
  21. $sql = 'SELECT age, name FROM people WHERE id IN (' . implode(', ', $ids) . ')';
  22. // $sql will contain SELECT age, name FROM people WHERE id IN (12, 45, 65, 33)
  23.  
  24. $query = "SELECT id,studentid,book_title,date FROM contracts WHERE studentid IN ($parameters)";
  25. $statement = $db->prepare($query);
  26. //build the bind_param function
  27. call_user_func_array (array($statement, "bind_param"), $idAr);
  28. //here is what we used to do before making it dynamic
  29. //statement->bind_param($paramtype,$v1,$v2);
  30. $statement->execute();
Add Comment
Please, Sign In to add comment