Posted By

Nettuts on 07/23/10

Tagged

Versions (?)

Last Edited at 07/23/10 05:19am

Statistics

Viewed 990 times

Favorited by 3 user(s)

Related snippets

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 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.

Highlighted TextExpand

class Database {

private $mysql;
	
	function __construct($host, $username, $password, $db) {
		$this->mysql = new mysqli($host, $username, $password, $db) or die('There was a problem connecting to the database');
	}
	
        /**
         * Simple query method. Pass in your SQL query as a parameter, and it'll do the rest
         * and return an array of the returned rows
         *
         * @param string $query Should be equal to a SQL query for querying the database.
         * @return array Returns an array of the returned rows from the db. 
         */
	public function query($query) {
            
                $parameters = array();
                $results = array();
	
		$stmt = $this->mysql->prepare($query);
		$stmt->execute();
                
                $meta = $stmt->result_metadata();
                
                while( $field = $meta->fetch_field() ) {
                    $parameters[] = &$row[$field->name];
                }
                
               call_user_func_array(array($stmt, 'bind_result'), $parameters);
                
		while($stmt->fetch()) {
                    $x = array();
                    foreach($row as $key => $val ) {
                        // This next line isn't necessary for your project. 
                        // It can be removed. I use it to ensure
                        // that the "excerpt" of the post doesn't end in the middle
                        // of a word. 
                        if ( $key === 'excerpt') $val = $this->cleanExcerpt($row[$key]);
                        $x[$key] = $val;
                    }
                 
                    $results[] = $x;
		}
                
		return $results; 
	}
	
}

$db = new Database('host', 'username' 'password', 'databaseName');
$items = $db->query("Your SQL query here");

print_r($items); // $items contains the returned rows

Report this snippet Tweet