Posted By

Nettuts on 07/23/10


Tagged

php statements prepared


Versions (?)

Who likes this?

13 people have marked this snippet as a favorite

screamwork
jfherring
LockeCole117
anilkiral
Bananenspin
mogwi
seanpowell
barbietunnie
esritter
angel
wsamoht
fomigo
qubestream


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


 / Published in: PHP
 

When retrieving table rows with PHP and prepared statements, you must use a method called bindresult() 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), bindresult 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.

  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
Posted By: LockeCole117 on July 23, 2010

Oh wow, nevermind. Mine is much more convoluted. Essentially, while($fetch); you concatenate each field into a large String Value, explode that into an array, and combine each array into a larger array based on record. This snippet is now a favorite of mine, and I'm going to replace my code before the next release.

Posted By: LockeCole117 on July 23, 2010

Oh wow, nevermind. Mine is much more convoluted. Essentially, while($fetch); you concatenate each field into a large String Value, explode that into an array, and combine each array into a larger array based on record. This snippet is now a favorite of mine, and I'm going to replace my code before the next release.

Posted By: LockeCole117 on July 23, 2010

Sorry About the Double Post >.< (Now 3 Posts for the Price of 1!)

You need to login to post a comment.