Voting

The Note You're Voting On

16 years ago
The `prepare` , `bind_param`, `bind_result`, `fetch` result, `close` stmt cycle can be tedious at times. Here is an object that does all the mysqli mumbo jumbo for you when all you want is a select leaving you to the bare essential `preparedSelect` on a prepared stmt. The method returns the result set as a 2D associative array with the `select`ed columns as keys. I havent done sufficient error-checking and it also may have some bugs. Help debug and improve on it. 

I used the bible.sql db from http://www.biblesql.net/sites/biblesql.net/files/bible.mysql.gz. 

Baraka tele!

============================

<?php

class DB
{
 public $connection;
 
 #establish db connection
 public function __construct($host="localhost", $user="user", $pass="", $db="bible")
 {
 $this->connection = new mysqli($host, $user, $pass, $db);
 
 if(mysqli_connect_errno())
 {
 echo("Database connect Error : " 
 . mysqli_connect_error($mysqli));
 } 
 }
 
 #store mysqli object
 public function connect()
 {
 return $this->connection;
 }

 #run a prepared query
 public function runPreparedQuery($query, $params_r)
 {
 $stmt = $this->connection->prepare($query);
 $this->bindParameters($stmt, $params_r);

 if ($stmt->execute()) {
 return $stmt;
 } else {
 echo("Error in $statement: " 
 . mysqli_error($this->connection));
 return 0;
 }
 
 }

 # To run a select statement with bound parameters and bound results. 
 # Returns an associative array two dimensional array which u can easily 
 # manipulate with array functions.
 
 public function preparedSelect($query, $bind_params_r)
 {
 $select = $this->runPreparedQuery($query, $bind_params_r);
 $fields_r = $this->fetchFields($select);
 
 foreach ($fields_r as $field) {
 $bind_result_r[] = &${$field};
 } 
 
 $this->bindResult($select, $bind_result_r);
 
 $result_r = array();
 $i = 0;
 while ($select->fetch()) {
 foreach ($fields_r as $field) {
 $result_r[$i][$field] = $$field;
 }
 $i++;
 }
 $select->close();
 return $result_r; 
 }
 
 
 #takes in array of bind parameters and binds them to result of 
 #executed prepared stmt
 
 private function bindParameters(&$obj, &$bind_params_r)
 {
 call_user_func_array(array($obj, "bind_param"), $bind_params_r);
 }
 
 private function bindResult(&$obj, &$bind_result_r)
 {
 call_user_func_array(array($obj, "bind_result"), $bind_result_r);
 }
 
 #returns a list of the selected field names
 
 private function fetchFields($selectStmt)
 {
 $metadata = $selectStmt->result_metadata();
 $fields_r = array();
 while ($field = $metadata->fetch_field()) {
 $fields_r[] = $field->name;
 }

 return $fields_r;
 }
}
#end of class
 
#An example of the DB class in use

$DB = new DB("localhost", "root", "", "bible");
$var = 5; 
$query = "SELECT abbr, name from books where id > ?" ;
$bound_params_r = array("i", $var);

$result_r = $DB->preparedSelect($query, $bound_params_r);

#loop thru result array and display result

foreach ($result_r as $result) {
 echo $result['abbr'] . " : " . $result['name'] . "<br/>" ;
}

?>
<< Back to user notes page