PHP Prepared Statements - with An Undefined Number of Variables Passed to bind_result()


/ Published in: PHP
Save to your folder(s)

When retrieving table rows with PHP and prepared statements, you must use a method called bind_result() and pass in a variable name for each column that is being returned from the database. For example, if you are selecting three columns (say, id, title, body), bind_result must be passed three variables names: bind_result($id, $title, $body). This becomes a pain in the butt when, for example, if it is within a class file, where you will not always know how many columns are being selected from the table. This code gets around that.


Copy this code and paste it in your HTML
  1. class Database {
  2.  
  3. private $mysql;
  4.  
  5. function __construct($host, $username, $password, $db) {
  6. $this->mysql = new mysqli($host, $username, $password, $db) or die('There was a problem connecting to the database');
  7. }
  8.  
  9. /**
  10.   * Simple query method. Pass in your SQL query as a parameter, and it'll do the rest
  11.   * and return an array of the returned rows
  12.   *
  13.   * @param string $query Should be equal to a SQL query for querying the database.
  14.   * @return array Returns an array of the returned rows from the db.
  15.   */
  16. public function query($query) {
  17.  
  18. $parameters = array();
  19. $results = array();
  20.  
  21. $stmt = $this->mysql->prepare($query);
  22. $stmt->execute();
  23.  
  24. $meta = $stmt->result_metadata();
  25.  
  26. while( $field = $meta->fetch_field() ) {
  27. $parameters[] = &$row[$field->name];
  28. }
  29.  
  30. call_user_func_array(array($stmt, 'bind_result'), $parameters);
  31.  
  32. while($stmt->fetch()) {
  33. $x = array();
  34. foreach($row as $key => $val ) {
  35. // This next line isn't necessary for your project.
  36. // It can be removed. I use it to ensure
  37. // that the "excerpt" of the post doesn't end in the middle
  38. // of a word.
  39. if ( $key === 'excerpt') $val = $this->cleanExcerpt($row[$key]);
  40. $x[$key] = $val;
  41. }
  42.  
  43. $results[] = $x;
  44. }
  45.  
  46. return $results;
  47. }
  48.  
  49. }
  50.  
  51. $db = new Database('host', 'username' 'password', 'databaseName');
  52. $items = $db->query("Your SQL query here");
  53.  
  54. print_r($items); // $items contains the returned rows

Report this snippet


Comments

RSS Icon Subscribe to comments

You need to login to post a comment.