Advanced Search

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.

Expand | Embed | Plain Text

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.