note 107200 added to mysqli.prepare
| From: | Darren at osu1 dot php dot net | Date: | Sat, 14 Jan 2012 07:09:43 +0000 |
| Subject: | note 107200 added to mysqli.prepare | ||
| Groups: | php.notes | ||
| Request: | Send a blank email to php-notes+get-185644@lists.php.net to get a copy of this message | ||
I wrote this function for my personal use and figured I would share it. I am not sure if this is
the appropriate forum but I wish I had this when I stumbled on to mysqli::prepare. The function is
an update of the function I posted previously. The previous function could not handle multiple
queries.
For queries:
Results of single queries are given as arrays[row#][associated Data Array]
Results of multiple queries are given as arrays[query#][row#][associated Data Array]
For queries which return an affected row#, affected rows are returned instead of
(array[row#][associated Data Array])
Code and example are below:
<?php
function mysqli_prepared_query($link,$sql,$typeDef = FALSE,$params = FALSE){
if($stmt = mysqli_prepare($link,$sql)){
if(count($params) == count($params,1)){
$params = array($params);
$multiQuery = FALSE;
} else {
$multiQuery = TRUE;
}
if($typeDef){
$bindParams = array();
$bindParamsReferences = array();
$bindParams =
array_pad($bindParams,(count($params,1)-count($params))/count($params),"");
foreach($bindParams as $key => $value){
$bindParamsReferences[$key] = &$bindParams[$key];
}
array_unshift($bindParamsReferences,$typeDef);
$bindParamsMethod = new ReflectionMethod('mysqli_stmt', 'bind_param');
$bindParamsMethod->invokeArgs($stmt,$bindParamsReferences);
}
$result = array();
foreach($params as $queryKey => $query){
foreach($bindParams as $paramKey => $value){
$bindParams[$paramKey] = $query[$paramKey];
}
$queryResult = array();
if(mysqli_stmt_execute($stmt)){
$resultMetaData = mysqli_stmt_result_metadata($stmt);
if($resultMetaData){
$stmtRow = array();
$rowReferences = array();
while ($field = mysqli_fetch_field($resultMetaData)) {
$rowReferences[] = &$stmtRow[$field->name];
}
mysqli_free_result($resultMetaData);
$bindResultMethod = new ReflectionMethod('mysqli_stmt',
'bind_result');
$bindResultMethod->invokeArgs($stmt, $rowReferences);
while(mysqli_stmt_fetch($stmt)){
$row = array();
foreach($stmtRow as $key => $value){
$row[$key] = $value;
}
$queryResult[] = $row;
}
mysqli_stmt_free_result($stmt);
} else {
$queryResult[] = mysqli_stmt_affected_rows($stmt);
}
} else {
$queryResult[] = FALSE;
}
$result[$queryKey] = $queryResult;
}
mysqli_stmt_close($stmt);
} else {
$result = FALSE;
}
if($multiQuery){
return $result;
} else {
return $result[0];
}
}
?>
Example(s):
For a table of firstName and lastName:
John Smith
Mark Smith
Jack Johnson
Bob Johnson
<?php
//single query, single result
$query = "SELECT * FROM names WHERE firstName=?";
$params = array("Bob");
mysqli_prepared_query($link,$query,"s",$params)
/*
returns array(
0=> array('firstName' => 'Bob', 'lastName' =>
'Johnson')
)
*/
//single query, multiple results
$query = "SELECT * FROM names WHERE lastName=?";
$params = array("Smith");
mysqli_prepared_query($link,$query,"ss",$params)
/*
returns array(
0=> array('firstName' => 'John', 'lastName' =>
'Smith')
1=> array('firstName' => 'Mark', 'lastName' =>
'Smith')
)
*/
//multiple query, multiple results
$query = "SELECT * FROM names WHERE lastName=?";
$params = array("Smith","Johnson");
mysqli_prepared_query($link,$query,"ss",$params)
/*
returns array(
0=>
array(
0=> array('firstName' => 'John', 'lastName' =>
'Smith')
1=> array('firstName' => 'Mark', 'lastName' =>
'Smith')
)
1=>
array(
0=> array('firstName' => 'Jack', 'lastName' =>
'Johnson')
1=> array('firstName' => 'Bob', 'lastName' =>
'Johnson')
)
)
*/
Hope it helps =)
----
Server IP: 69.147.83.197
Probable Submitter: 24.5.125.223
----
Manual Page -- http://www.php.net/manual/en/mysqli.prepare.php
Edit -- https://master.php.net/note/edit/107200
Del: integrated -- https://master.php.net/note/delete/107200/integrated
Del: useless -- https://master.php.net/note/delete/107200/useless
Del: bad code -- https://master.php.net/note/delete/107200/bad+code
Del: spam -- https://master.php.net/note/delete/107200/spam
Del: non-english -- https://master.php.net/note/delete/107200/non-english
Del: in docs -- https://master.php.net/note/delete/107200/in+docs
Del: other reasons-- https://master.php.net/note/delete/107200
Reject -- https://master.php.net/note/reject/107200
Search -- https://master.php.net/manage/user-notes.php