note 90436 added to mysqli-stmt.prepare
| From: | ndungiatgmaildotcom at osu1 dot php dot net | Date: | Wed, 22 Apr 2009 09:25:41 +0000 |
| Subject: | note 90436 added to mysqli-stmt.prepare | ||
| Groups: | php.notes | ||
| Request: | Send a blank email to php-notes+get-153570@lists.php.net to get a copy of this message | ||
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 selected 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/>" ;
}
?>
----
Server IP: 69.147.83.197
Probable Submitter: 217.21.118.62
----
Manual Page -- http://www.php.net/manual/en/mysqli-stmt.prepare.php
Edit -- https://master.php.net/note/edit/90436
Del: integrated -- https://master.php.net/note/delete/90436/integrated
Del: useless -- https://master.php.net/note/delete/90436/useless
Del: bad code -- https://master.php.net/note/delete/90436/bad+code
Del: spam -- https://master.php.net/note/delete/90436/spam
Del: non-english -- https://master.php.net/note/delete/90436/non-english
Del: in docs -- https://master.php.net/note/delete/90436/in+docs
Del: other reasons-- https://master.php.net/note/delete/90436
Reject -- https://master.php.net/note/reject/90436
Search -- https://master.php.net/manage/user-notes.php