©
This document uses PHP Chinese website manual Release
mysqli_stmt::bind_param
mysqli_stmt_bind_param
(PHP 5)
mysqli_stmt::bind_param -- mysqli_stmt_bind_param — Binds variables to a prepared statement as parameters
说明
面向对象风格
$types
, mixed &$var1
[, mixed &$...
] )过程化风格
$stmt
, string $types
, mixed &$var1
[, mixed &$...
] )Bind variables for the parameter markers in the SQL statement that was passed to mysqli_prepare() .
Note:
If data size of a variable exceeds max. allowed packet size (max_allowed_packet), you have to specify b in
typesand use mysqli_stmt_send_long_data() to send the data in packets.
Note:
Care must be taken when using mysqli_stmt_bind_param() in conjunction with call_user_func_array() . Note that mysqli_stmt_bind_param() requires parameters to be passed by reference, whereas call_user_func_array() can accept as a parameter a list of variables that can represent references or values.
参数
-
stmt -
仅以过程化样式：由 mysqli_stmt_init() 返回的 statement 标识。
-
types -
A string that contains one or more characters which specify the types for the corresponding bind variables:
Type specification chars Character Description i corresponding variable has type integer d corresponding variable has type double s corresponding variable has type string b corresponding variable is a blob and will be sent in packets -
var1 -
The number of variables and length of string
typesmust match the parameters in the statement.
返回值
成功时返回 TRUE ， 或者在失败时返回 FALSE 。
范例
Example #1 面向对象风格
<?php
$mysqli = new mysqli ( 'localhost' , 'my_user' , 'my_password' , 'world' );
if ( mysqli_connect_errno ()) {
printf ( "Connect failed: %s\n" , mysqli_connect_error ());
exit();
}
$stmt = $mysqli -> prepare ( "INSERT INTO CountryLanguage VALUES (?, ?, ?, ?)" );
$stmt -> bind_param ( 'sssd' , $code , $language , $official , $percent );
$code = 'DEU' ;
$language = 'Bavarian' ;
$official = "F" ;
$percent = 11.2 ;
$stmt -> execute ();
printf ( "%d Row inserted.\n" , $stmt -> affected_rows );
$stmt -> close ();
$mysqli -> query ( "DELETE FROM CountryLanguage WHERE Language='Bavarian'" );
printf ( "%d Row deleted.\n" , $mysqli -> affected_rows );
$mysqli -> close ();
?> Example #2 过程化风格
<?php
$link = mysqli_connect ( 'localhost' , 'my_user' , 'my_password' , 'world' );
if (! $link ) {
printf ( "Connect failed: %s\n" , mysqli_connect_error ());
exit();
}
$stmt = mysqli_prepare ( $link , "INSERT INTO CountryLanguage VALUES (?, ?, ?, ?)" );
mysqli_stmt_bind_param ( $stmt , 'sssd' , $code , $language , $official , $percent );
$code = 'DEU' ;
$language = 'Bavarian' ;
$official = "F" ;
$percent = 11.2 ;
mysqli_stmt_execute ( $stmt );
printf ( "%d Row inserted.\n" , mysqli_stmt_affected_rows ( $stmt ));
mysqli_stmt_close ( $stmt );
mysqli_query ( $link , "DELETE FROM CountryLanguage WHERE Language='Bavarian'" );
printf ( "%d Row deleted.\n" , mysqli_affected_rows ( $link ));
mysqli_close ( $link );
?> 以上例程会输出：
1 Row inserted. 1 Row deleted.
参见
- mysqli_stmt_bind_result() - Binds variables to a prepared statement for result storage
- mysqli_stmt_execute() - Executes a prepared Query
- mysqli_stmt_fetch() - Fetch results from a prepared statement into the bound variables
- mysqli_prepare() - Prepare an SQL statement for execution
- mysqli_stmt_send_long_data() - Send data in blocks
- mysqli_stmt_errno() - Returns the error code for the most recent statement call
- mysqli_stmt_error() - Returns a string description for last statement error
用户评论:
[#1] bboymarco [2015-08-04 12:14:39]
I did for myself a really useful function (cause I didn't want to use eval() or reflection) that create for you a prepared statement with an indefinite number of parameters and return an indefinite number of result columns. Last two arguments are optional so when you don't have parameters to send to MySQL you can avoid them. I decided to share it here cause it may be helpful and save some time and brainstorming:
function prepared_stmt($con, $query, $type = "", $param = array()){
if($stmt = mysqli_prepare($con, $query)){
$refarg = array($stmt, $type);//First two parameter of mysqli_stmt_bind_param
foreach ($param as $key => $value)//create array of parameters' references
$refarg[] =& $param[$key];
if($type != "")//Jump instruction if argument $type is missing
call_user_func_array("mysqli_stmt_bind_param", $refarg);//bind parameters with dinamic length
if(mysqli_stmt_execute($stmt)){//check if execution go fine
$cols = mysqli_field_count($con);//retrive the number of columns of the resultset
$result = array_fill(0, $cols, NULL);//create an empty array with the same length of the columns
$ref = array($stmt);//first argument of mysqli_stmt_bind_result
foreach ($result as $key => $value)//create array of empty cells' references
$ref[] =& $result[$key];
call_user_func_array("mysqli_stmt_bind_result", $ref);//bind results with dinamic length
return $ref;//return statement and columns references
}//if
else
return false;
}//if
else
return false;
}//prepared_stmt
Example of call:
$hello = prepared_stmt($con, "SELECT Example FROM table WHERE Col=?", "s", array("LOL");
//count($hello) is equal to 2
//$hello[0] Statement reference
//$hello[1] Example column field where to find values after fetching
while(mysqli_stmt_fetch($hello[0]))
echo $hello[1] . "<br>";//This will print all value of the Example column
[#2] code at roberthairgrove dot com [2015-07-11 21:14:54]
When I tried to use nickv9's class BindParam, I had to make a small change to get it to work:
<?php
// snip...
public function add( $type, &$value ){
$this->values[] = &$value;
$this->types .= $type;
}
// snip...
?>
Note the additional reference notation used within the function to add the parameter to the class variable (&$value instead of $value), not just in the parameter list.
Otherwise, I got an error from call_user_func_array():
"Parameter 2 to mysqli_stmt::bind_param() expected to be a reference, value given..."
[#3] jmdoren at ok dot cl [2014-05-14 18:36:59]
I just wrote a function to bind a variable number of parameters
function bind_param_array( $stmt, $types, $vars ){
$php_command = '$stmt->bind_param( $types';
for( $i=0;$i<count($vars);$i++)
{
$php_command .= ',$vars['.$i.']';
}
$php_command .= ');';
return eval( $php_command );
}
yo can use it like this
$params = array( "one", "two", "two again" );
$types = str_repeat( 's', count( $params );
$stmt = $MysqliObject->prepare( $query );
bind_param_array( $stmt, $types,$params);
enjoy it!
[#4] tomasz at marcinkowski dot pl [2014-03-27 15:46:16]
When trying to bind a string param you get a "Number of variables doesn't match number of parameters in prepared statement" error, make sure you're not wrapping the question mark with quotes.
By mistake I had a query like:
SELECT something FROM table WHERE param_name = "?"
Binding it with
<?php $stmt->bind('s', $param_value); ?>
had been failing on me. All I had to do was to remove quotes around "?".
Hope this saves someone's time.
[#5] markac [2014-03-05 15:09:44]
Sprintf parameter binding style:
<?php
class MySqliDecorator {
private $db;
private $map = array(
'%d' => 'i', //integer
'%f' => 'd', //float
'%s' => 's', //string
);
public function __construct(mysqli $db) {
$this->db = $db;
}
public function prepare($query, &$params = array()) {
$expr = '/(' . implode('|', array_keys($this->map)) . ')/';
if (preg_match_all($expr, $query, $matches)) {
$types = implode('', $matches[0]);
$types = strtr($types, $this->map);
$query = preg_replace($expr, '?', $query);
if ($stmt = $this->db->prepare($query)) {
array_unshift($params, $types);
if (call_user_func_array(array($stmt, 'bind_param'), &$params)) {
return $stmt;
} else {
return false;
}
} else {
return false;
}
} else {
return $this->db->prepare($query);
}
}
}
?>
Using:
<?php
$db = new mysqli(DB_HOST, DB_USER, DB_PASS, DB_NAME);
$stmt = new MySqliDecorator($db);
$params = array(1, 'johny');
$stmt = $stmt->prepare('SELECT * FROM users WHERE Id = %d OR Username = %s', $params);
$stmt->execute();
?>
Be careful when using % with LIKE and so on.
[#6] Boyet [2013-10-23 12:56:48]
I think this is a good way to automatically get the types for the binding in as few lines of code as possible. If you bind the array values in the same order as when you used it for this you should not worry about aligning them.
foreach($array as $var){
$chrType = substr((string)gettype($var),0,1);
$strType .= (!in_array($chrType,array("i","d","s"))) ? "b" : $chrType;
}
[#7] laurence dot mackenzie at stream dot com [2013-10-08 21:08:56]
I just came across a very strange behaviour when using bind_param() with a reflection class. I figured I ought to post it here to save anyone else who comes across it from banging their head against their desk for an hour (as I just did).
First, some background: I have a set of classes, one per file format (i.e. CSV, HTML table, etc), which import data from flat files to a temporary table in my database. The class then transforms the data to 3NF.
I'm using a reflection class to pass an array to mysqli->bind_param() because the column counts and types are variable. The code (simplified) I am having issues with is:
<?php
$stmtInsert = $db->prepare('INSERT.....');
$typeString = 'ississis';
$data = array(1, 'two', 'three', 4, 'five', 'six', 7, 'eight');
// Merge the parameter types with the parameter values
$data = array_merge((array) $typeString, $data);
// Create the reflection class
$ref = new \ReflectionClass('mysqli_stmt');
// Get the bind_param method
$method = $ref->getMethod('bind_param');
// Invoke it with $data
$method->invokeArgs($stmtInsert, $data);
// Execute the statement
$stmtInsert->execute();
}
?>
Oddly, in one (and only one) case it started throwing "Warning: Parameter 41 to mysqli_stmt::bind_param() expected to be a reference, value given". The reflection class throws an exception. Other import sets using this code work just fine. Parameter 41 is the last parameter. Changing the affected code as follows resolves the issue:
<?php
$ref = new \ReflectionClass("mysqli_stmt");
$method = $ref->getMethod("bind_param");
$data[count($data)-1] = (string) $data[count($data)-1];
$method->invokeArgs($stmtInsert, $data);
$stmtInsert->execute();
?>
Not sure what's going on here, but like I said, hopefully this will keep the next person from thinking they're totally insane.
[#8] mike chip [2013-03-13 02:45:38]
I had to do a more advanced prepare function (A multi-multi values prepared statement)to make things easier for a few situations, so I wrote the following function.
NOTE: This function assumes you have put it into a class that have:
$mysqli = A mysqli object
NOTE2: This function currently only supports ints for the values, but you can modify it to support additional types...
To use it:
<?php
$sql = "SELECT id, code FROM country";
$result = $database->fetch_all($sql); // This is just a custom fetch_all function...
if(!$result) {
return null; // no results...
}
// Re-organize the order of the elements from the resultset, code first then id, just like the sql statement...
$pData = array();
foreach($result as $row) {
$pData[] = array('code'=>$row['code'] + 1,'id'=>$row['id']); //Assign valid php variable names, because this'll be used later on!!!
}
$database->mysqli_prepare_exec("UPDATE country SET code=? WHERE id=?", 'ii', $pData);
?>
Here's the function:
<?php
public function mysqli_prepare_exec($sql, $types, $data)
{
$stm = $this->mysqli->prepare($sql);
if(!$stm) {
return null; // Statement couldn't be prepared, check for errors...
}
foreach($data[0] as $k => $v) {
eval('$' . $k . '=' . $v . ';');
eval('$vars[\'' . $k . '\'] = &$' . $k . ';'); // Referencing to our last newly created variable
}
unset($k,$v);
call_user_func_array(array($stm, 'bind_param'), array_merge(array($types), $vars));
foreach($data as $row) {
foreach($row as $k => $v) {
eval('$' . $k . '=' . $v . ';');
}
$stm->execute();
}
}
?>
[#9] Ray.Paseur uses Gmail [2013-03-03 17:24:05]
It may seem obvious, but it's worth noting that variables must exist before you can bind them. This example worked well for me (line 12).
<?php
$test_names_arrays = array
( array( "fname" => "Walter" , "lname" => "Williams" )
, array( "fname" => "Ray" , "lname" => "Paseur" )
, array( "fname" => "Bill" , "lname" => "O'Reilly" )
)
;
$sql = "INSERT INTO my_table ( fname, lname ) VALUES ( ?,? )";
$ins = $mysqli->prepare($sql);
// NB: THESE VARIABLES MUST EXIST BEFORE THEY CAN BE BOUND!
$person = current($test_names_arrays);
$ins->bind_param('ss', $person['fname'], $person['lname']);
foreach ($test_names_arrays as $person)
{
$ins->execute();
}
?>
[#10] Guido [2012-10-15 19:43:54]
Dear all,
I was searching for a class which supports multiple calls to bind_param, because I have scenarios where I build huge SQL statements over different functions with variable numbers of parameters. But I didn't found one. So I have just written up this little piece of code I would like to share with you. There is enough room to optimize these classes, but it shows the general idea. And for me it works. In mbind_param_do() it seems to depend from the PHP version if makeValuesReferenced() must be used or if $params can be used directly. In my case I have to use it.
The cool thing about this solution: You don't have to care about a lot if you are using my mbind_ functions or not. You may also use default bind_param and the execute will still work.
<?php
class db extends mysqli {
public function prepare($query) {
return new stmt($this,$query);
}
}
class stmt extends mysqli_stmt {
public function __construct($link, $query) {
$this->mbind_reset();
parent::__construct($link, $query);
}
public function mbind_reset() {
unset($this->mbind_params);
unset($this->mbind_types);
$this->mbind_params = array();
$this->mbind_types = array();
}
//use this one to bind params by reference
public function mbind_param($type, &$param) {
$this->mbind_types[0].= $type;
$this->mbind_params[] = &$param;
}
//use this one to bin value directly, can be mixed with mbind_param()
public function mbind_value($type, $param) {
$this->mbind_types[0].= $type;
$this->mbind_params[] = $param;
}
public function mbind_param_do() {
$params = array_merge($this->mbind_types, $this->mbind_params);
return call_user_func_array(array($this, 'bind_param'), $this->makeValuesReferenced($params));
}
private function makeValuesReferenced($arr){
$refs = array();
foreach($arr as $key => $value)
$refs[$key] = &$arr[$key];
return $refs;
}
public function execute() {
if(count($this->mbind_params))
$this->mbind_param_do();
return parent::execute();
}
private $mbind_types = array();
private $mbind_params = array();
}
$search1 = "test1";
$search2 = "test2";
$_db = new db("host","user","pass","database");
$query = "SELECT name FROM table WHERE col1=? AND col2=?";
$stmt = $_db->prepare($query);
$stmt->mbind_param('s',$search1);
//this second call is the cool thing!!!
$stmt->mbind_param('s',$search2);
$stmt->execute();
//this would still work!
//$search1 = "test1changed";
//$search2 = "test2changed";
//$stmt->execute();
...
$stmt->store_result();
$stmt->bind_result(...);
$stmt->fetch();
?>
[#11] xianrenb at gmail dot com [2012-09-30 06:52:17]
It is believed that if one has specified 'b' in $types, the corresponding variable should be set to null, and one has to use mysqli_stmt::send_long_data() or mysqli_stmt_send_long_data() to send the blob, otherwise the blob value would be treated as empty.
[#12] ellert at _spam_ vankoperen _spam_ dot nl [2012-08-29 09:43:46]
Dont forget that in the query that you are preparing you do NOT need quotes around string values.
If you put them there anyway, you will get:
mysqli_stmt_bind_param(): Number of variables doesn't match number of parameters in prepared statement
[#13] nick9v at hotmail dot com [2012-06-30 10:12:03]
When dealing with a dynamic number of field values while preparing a statement I find this class useful.
<?php
class BindParam{
private $values = array(), $types = '';
public function add( $type, &$value ){
$this->values[] = $value;
$this->types .= $type;
}
public function get(){
return array_merge(array($this->types), $this->values);
}
}
?>
Usage is pretty simple. Create an instance and use the add method to populate. When you're ready to execute simply use the get method.
<?php
$bindParam = new BindParam();
$qArray = array();
$use_part_1 = 1;
$use_part_2 = 1;
$use_part_3 = 1;
$query = 'SELECT * FROM users WHERE ';
if($use_part_1){
$qArray[] = 'hair_color = ?';
$bindParam->add('s', 'red');
}
if($use_part_2){
$qArray[] = 'age = ?';
$bindParam->add('i', 25);
}
if($use_part_3){
$qArray[] = 'balance = ?';
$bindParam->add('d', 50.00);
}
$query .= implode(' OR ', $qArray);
//call_user_func_array( array($stm, 'bind_param'), $bindParam->get());
echo $query . '<br/>';
var_dump($bindParam->get());
?>
This gets you the result that looks something like this:
SELECT * FROM users WHERE hair_color = ? OR age = ? OR balance = ?
array(4) { [0]=> string(3) "sid" [1]=> string(3) "red" [2]=> int(25) [3]=> float(50) }
[Editor's note: changed BindParam::add() to accept $value by reference and thereby prevent a warning in newer versions of PHP.]
[#14] robstocki at battlesecure dot com [2012-05-24 22:22:38]
Here is the procedural version of a select statement when wanting to use %LIKE% in the query and not an '=':
<?php
function db_connect(){
// set $db as global for access outside function
global $db;
# Use procedural methods for database connection and manipulation
// Connect to Database
@$db = mysqli_connect(DB_HOST, DB_USER, DB_PASSWORD, DB_NAME);
// Check connection
if(mysqli_connect_errno()) {
echo '<br />Error: Could not connect to database. Please try again later.<br />';
exit;
}
}
// Connect to Database
db_connect();
$theRecordInTheTableIs = "%".$_POST['theRecordInTheTableIs']."%"; // concat $_POST variable with % on each side for use in prepared statement
$theRelatedRecordLooks = "%".$_POST['theRelatedRecordLooks']."%"; // concat $_POST variable with % on each side for use in prepared statement
// Create Query
$q = ''; // create variable for input
$q .= " SELECT ";
$q .= " * ";
$q .= " FROM ";
$q .= " myFavTable ";
$q .= " WHERE ";
$q .= " theRecordInTheTableIs LIKE ? ";
$q .= " AND ";
$q .= " theRelatedRecordLooks LIKE ? ";
// Prepare the statement
$stmt = mysqli_prepare($db, $q);
// bind the paramaters
mysqli_stmt_bind_param($stmt, 'ss', $theRecordInTheTableIs,$theRelatedRecordLooks);
// Execute the query
mysqli_stmt_execute($stmt);
// bind result variables
mysqli_stmt_bind_result($stmt, $col0, $col1, $col2, $col3, $col4, $col5, $col6, $col7, $col8, $col9, $col10);
// Display the query
// start the table
echo '<table>
<tr>
<td>Col0 Title</td>
<td>Col1 Title</td>
<td>Col2 Title</td>
<td>Col3 Title</td>
<td>Col4 Title</td>
<td>Col5 Title</td>
<td>Col6 Title</td>
<td>Col7 Title</td>
<td>Col8 Title</td>
<td>Col9 Title</td>
<td>Col10 Title</td>
</tr>';
// fetch values
while (mysqli_stmt_fetch($stmt)) {
echo " <tr>
<td>{$col0}</td>
<td>{$col1}</td>
<td>{$col2}</td>
<td>{$col3}</td>
<td>{$col4}</td>
<td>{$col5}</td>
<td>{$col6}</td>
<td>{$col7}</td>
<td>{$col8}</td>
<td>{$col9}</td>
<td>{$col10}</td>
</tr> ";
}
// close the table
echo '</table>';
?>
[#15] Darren [2012-01-11 04:00:21]
For those learning mysqli::prepare and mysqli_stmt::bind_params for the first time, here is a commented block of code which executes prepared queries and returns data in a similar format to the return values of mysqli_query. I tried to minimize unnecessary classes, objects, or overhead for two reasons:
1) facilitate learning
2) allow relativity interchangeable use with mysqli_query
My goal is to lower the learning curve for whoever is starting out with these family of functions. I am by no means an expert coder/scripter, so I am sure there are improvements and perhaps a few bugs, but I hope not =)
<?php
function mysqli_prepared_query($link,$sql,$bindParams = FALSE){
if($stmt = mysqli_prepare($link,$sql)){
if ($bindParams){
$bindParamsMethod = new ReflectionMethod('mysqli_stmt', 'bind_param'); //allows for call to mysqli_stmt->bind_param using variable argument list
$bindParamsReferences = array(); //will act as arguments list for mysqli_stmt->bind_param
$typeDefinitionString = array_shift($bindParams);
foreach($bindParams as $key => $value){
$bindParamsReferences[$key] = &$bindParams[$key];
}
array_unshift($bindParamsReferences,$typeDefinitionString); //returns typeDefinition as the first element of the string
$bindParamsMethod->invokeArgs($stmt,$bindParamsReferences); //calls mysqli_stmt->bind_param suing $bindParamsRereferences as the argument list
}
if(mysqli_stmt_execute($stmt)){
$resultMetaData = mysqli_stmt_result_metadata($stmt);
if($resultMetaData){
$stmtRow = array(); //this will be a result row returned from mysqli_stmt_fetch($stmt)
$rowReferences = array(); //this will reference $stmtRow and be passed to mysqli_bind_results
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); //calls mysqli_stmt_bind_result($stmt,[$rowReferences]) using object-oriented style
$result = array();
while(mysqli_stmt_fetch($stmt)){
foreach($stmtRow as $key => $value){ //variables must be assigned by value, so $result[] = $stmtRow does not work (not really sure why, something with referencing in $stmtRow)
$row[$key] = $value;
}
$result[] = $row;
}
mysqli_stmt_free_result($stmt);
} else {
$result = mysqli_stmt_affected_rows($stmt);
}
mysqli_stmt_close($stmt);
} else {
$result = FALSE;
}
} else {
$result = FALSE;
}
return $result;
}
?>
Here's hoping the PHP gods don't smite me.
[#16] samishiikihaku23 at gmail dot com [2011-12-30 15:50:56]
NOTES to new users! Or programmers like myself who learn the hard way!!!
Pay attention to the variables that are given in the function up above. ( string $types , mixed &$var1 [, mixed &$... ] ) . The example shows this too, but I personally didn't get it 'til trying to debug my code.
ITS REQUIRED TO PASS VARIABLES HERE. You can not pass straight data through here.
$stmt->bind('s','Something here'); Will error!!!
Just a clarification as to avoid another night like mine last night and this morning.
[#17] Ole Clausen [2011-11-06 11:50:23]
A lot of newcommers to mysqli find it hard to get started. I have written this wrapper with object based response, that handles most of my queries. I hope it'll be usefull for others as well:
<?php
define('DB_HOST', 'localhost');
define('DB_USERNAME', '');
define('DB_PASSWORD', '');
define('DB_DEFAULT_DB', 'test');
function iQuery($sql, $arrParams, $arrBindNames=false) {
$result = new stdClass();
$mysqli = @new mysqli(DB_HOST, DB_USERNAME, DB_PASSWORD, DB_DEFAULT_DB);
if (mysqli_connect_errno()) {
$result->error = 'Connection failed: '.mysqli_connect_error();
return $result;
}
if ($stmt = $mysqli->prepare($sql)) {
$method = new ReflectionMethod('mysqli_stmt', 'bind_param');
$method->invokeArgs($stmt, $arrParams);
$stmt->execute();
$meta = $stmt->result_metadata();
if (!$meta) {
$result->affected_rows = $stmt->affected_rows;
$result->insert_id = $stmt->insert_id;
} else {
$stmt->store_result();
$params = array();
$row = array();
if ($arrBindNames) {
for ($i=0,$j=count($arrBindNames); $i<$j; $i++) {
$params[$i] = &$row[$arrBindNames[$i]];
}
} else {
while ($field = $meta->fetch_field()) {
$params[] = &$row[$field->name];
}
}
$meta->close();
$method = new ReflectionMethod('mysqli_stmt', 'bind_result');
$method->invokeArgs($stmt, $params);
$result->rows = array();
while ($stmt->fetch()) {
$obj = new stdClass();
foreach($row as $key => $val) {
$obj->{$key} = $val;
}
$result->rows[] = $obj;
}
$stmt->free_result();
}
$stmt->close();
}
$mysqli->close();
return $result;
}
$arrParams = array('ss', $_POST['sex'], $_POST['active']);
$result = iQuery( 'SELECT * FROM `test_table` WHERE `sex`=? AND `active`=?', $arrParams);
print_r($result);
print $result->rows[1]->first_name . " " . $result->rows[1]->last_name;
?>
If $_POST['sex'] contains 'male' and $_POST['active'] contains 'yes' - and the field names are 'id', 'first_name', 'last_name', 'sex' and 'active', the printet result may look like this:
----------------------------------------------
stdClass Object
(
[rows] => Array
(
[0] => stdClass Object
(
[id] => 2
[first_name] => Peter
[last_name] => Johnson
[sex] => male
[active] => yes
)
[1] => stdClass Object
(
[id] => 5
[first_name] => Ole
[last_name] => Clausen
[sex] => male
[active] => yes
)
)
)
Ole Clausen
----------------------------------------------
You can also apply special field names to the response, if you use the parameter $arrBindNames:
$arrParams = array('ss', $_POST['sex'], $_POST['active']);
$arrNames = array('foo_id', 'bar_first', 'baz_last', 'foo_sex', 'bar_act');
$result = iQuery( 'SELECT * FROM `test_table` WHERE `sex`=? AND `active`=?', $arrParams, $arrNames);
- a row would then look like this:
[0] => stdClass Object
(
[foo_id] => 2
[bar_first] => Peter
[baz_last] => Johnson
[foo_sex] => male
[bar_act] => yes
)
The first argument 'ss' in $arrParams states, that the two following arguments are of type String. The options are 's' for String, 'i' for Integer, 'd' for Double and 'b' for Blob (sent in packages).
In queries, that do not return a result INSERT, UPDATE, etc. $result->affected_rows and $result->insert_id are available. Connection errors are available in $result->error. Additional error handling would be nice, but is not implemented for now. Play with the wrapper and use print_r on the result ... enjoy!
The name 'iQuery'? Well, it handles mysql*i* - and then I guess it's kind of a tribute to Mr. Jobs ... may he 'rest' in energetic, hungry foolishness =)
==
UPDATE: 08-NOV-2011 07:19
Due to changes in PHP 5.3 I encountered a problem with 'bind_param' in my iQuery function below. The values in the passed array *must* be references. The soloution is this function:
<?php
function getRefArray($a) {
if (strnatcmp(phpversion(),'5.3')>=0) {
$ret = array();
foreach($a as $key => $val) {
$ret[$key] = &$a[$key];
}
return $ret;
}
return $a;
}
?>
- and this change in iQuery:
if ($stmt = $mysqli->prepare($sql)) {
$arrParams = getRefArray($arrParams); // <-- Added due to changes since PHP 5.3
$method = new ReflectionMethod('mysqli_stmt', 'bind_param');
$method->invokeArgs($stmt, $arrParams);
$stmt->execute();
$meta = $stmt->result_metadata();
[#18] ghostarthour at exahost dot eu [2011-08-27 14:00:44]
Simple workaround for casting:
<?php
function mysqli_cast_workaround() {
$numargs = func_num_args();
$types = '';
for ($i = 0; $i < $numargs; $i++) {
$arg = func_get_arg($i);
if (is_numeric($arg)) {
$types.="i";
}else{
$types.="s";
}
$args[($i+1)] = $arg;
unset($arg);
}
$args[0] = $types;
return call_user_func_array('mysqli_stmt_bind_param',$args);
}
?>
[#19] alex dot deleyn at gmail dot com [2011-07-16 18:50:37]
MySQL has a "NULL-safe equal" operator (I'm guessing since 5.0)
http://dev.mysql.com/doc/refman/5.0/en/comparison-operators.html#operator_equal-to
If you use this operator instead of the usual = you can interchange values and null in where clauses.
There is however a known bug when using this operator with datetime or timestamp fields: http://bugs.mysql.com/bug.php?id=36100
[#20] jk at jankriedner dot de [2011-05-20 14:39:25]
There are some things to note when working with mysqli::bind_param() and array-elements.
Re-assigning an array will break any references, no matter if the keys are identical.
You have to explicitly reassign every single value in an array, for the references to be kept.
Best shown in an example:
<?php
function getData() {
return array(
0=>array(
"name"=>"test_0",
"email"=>"test_0@example.com"
),
1=>array(
"name"=>"test_1",
"email"=>"test_1@example.com"
)
);
}
$db = new mysqli("localhost","root","","tests");
$sql = "INSERT INTO `user` SET `name`=?,`email`=?";
$res = $db->prepare($sql);
// If you bind array-elements to a prepared statement, the array has to be declared first with the used keys:
$arr = array("name"=>"","email"=>"");
$res->bind_param("ss",$arr['name'],$arr['email']);
//So far the introduction...
foreach( getData() as $arr ) {
$res->execute();
}
foreach( getData() as $tempArr ) {
foreach($tempArr as $k=>$v) {
$arr[$k] = $v;
}
$res->execute();
}
?>
Coming to the problem calling mysqli::bind_param() with a dynamic number of arguments via call_user_func_array() with PHP Version 5.3+, there's another workaround besides using an extra function to build the references for the array-elements.
You can use Reflection to call mysqli::bind_param(). When using PHP 5.3+ this saves you about 20-40% Speed compared to passing the array to your own reference-builder-function.
Example:
<?php
$db = new mysqli("localhost","root","","tests");
$res = $db->prepare("INSERT INTO test SET foo=?,bar=?");
$refArr = array("si","hello",42);
$ref = new ReflectionClass('mysqli_stmt');
$method = $ref->getMethod("bind_param");
$method->invokeArgs($res,$refArr);
$res->execute();
?>
[#21] asb(.d o,t )han(a t)n i h e i(d.o_t)dk [2011-04-22 18:05:35]
It should be noted that MySQL has some issues regarding the use of the IN clause in prepared statements.
I.e. the code:
<?php
$idArr = "1, 2, 3, 4";
$int_one = 1;
$int_two = 2;
$int_three = 3;
$int_four = 4;
$db = new MySQLi();
$bad_stmt = $db->prepare(SELECT `idAsLetters` FROM `tbl` WHERE `id` IN(?));
$bad_stmt->bind_param("s", $idArr);
$bad_stmt->bind_result($ias);
$bad_stmt->execute();
echo "Bad results:" . PHP_EOL;
while($stmt->fetch()){
echo $ias . PHP_EOL;
}
$good_stmt->close();
$good_stmt = $db->prepare(SELECT `idAsLetters` FROM `tbl` WHERE `id` IN(?, ?, ?, ?));
$good_stmt->bind_param("iiii", $int_one, $int_two, $int_three, $int_four);
$good_stmt->bind_result($ias);
$good_stmt->execute();
echo "God results:" . PHP_EOL;
while($stmt->fetch()){
echo $ias . PHP_EOL;
}
$bad_stmt->close();
$db->close();
?>
will print this result:
Bad results:
one
Good results:
one
two
three
four
Using "IN(?)" in a prepared statement will return just one (the first) row from a table/view. This is not an error in PHP, but merely how MySQL handles prepared statements.
[#22] Anonymous [2011-03-28 13:49:51]
You can bind to variables with NULL values, and on update and insert queries, the corresponding field will be updated to NULL no matter what bind string type you associated it with. But, for parameters meant for the WHERE clause (ie where field = ?), the query will have no effect and produce no results.
When comparing a value against NULL, the MYSQL syntax is either "value IS NULL" or "value IS NOT NULL". So, you can't pass in something like "WHERE (value = ?)" and expect this to work using a null value parameter.
Instead, you can do something like this in your WHERE clause:
"WHERE (IF(ISNULL(?), field1 is null, field1 = ?))"
Then, pass in the value you want to test twice:
bind_param('ss', $value1, $value1);
[#23] Anonymous [2011-03-14 14:28:02]
Blob and null handling aside, a couple of notes on how param values are automatically converted and forwarded on to the Mysql engine based on your type string argument:
1) PHP will automatically convert the value behind the scenes to the underlying type corresponding to your binding type string. i.e.:
<?php
$var = true;
bind_param('i', $var); // forwarded to Mysql as 1
?>
2) Though PHP numbers cannot be reliably cast to (int) if larger than PHP_INT_MAX, behind the scenes, the value will be converted anyway to at most long long depending on the size. This means that keeping in mind precision limits and avoiding manually casting the variable to (int) first, you can still use the 'i' binding type for larger numbers. i.e.:
<?php
$var = '429496729479896';
bind_param('i', $var); // forwarded to Mysql as 429496729479900
?>
3) You can default to 's' for most parameter arguments in most cases. The value will then be automatically cast to string on the back-end before being passed to the Mysql engine. Mysql will then perform its own conversions with values it receives from PHP on execute. This allows you to bind not only to larger numbers without concern for precision, but also to objects as long as that object has a '__toString' method.
This auto-string casting behavior greatly improves things like datetime handling. For example: if you extended DateTime class to add a __toString method which outputs the datetime format expected by Mysql, you can just bind to that DateTime_Extended object using type 's'. i.e.:
<?php
// DateTime_Extended has __toString defined to return the Mysql formatted datetime
$var = new DateTime_Extended;
bind_param('s', $var); // forwarded to Mysql as '2011-03-14 17:00:01'
?>
[#24] user at mail dot com [2011-01-25 00:44:30]
I had a problem with the LIKE operator
This code did not work:
<?php
$test = $sql->prepare("SELECT name FROM names WHERE name LIKE %?%");
$test->bind_param("s", $myname);
?>
The solution is:
<?php
$test = $sql->prepare("SELECT name FROM names WHERE name LIKE ?");
$param = "%" . $myname . "%";
$test->bind_param("s", $param);
?>
[#25] canche_x at yahoo dot com [2010-11-12 04:43:02]
Hi, I just write a function to do all my sql statements based on all the others comments in this page, maybe it can be useful for someone else :)
Usage:
execSQL($sql, $parameters, $close);
$sql = Statement to execute;
$parameters = array of type and values of the parameters (if any)
$close = true to close $stmt (in inserts) false to return an array with the values;
Examples:
execSQL("SELECT * FROM table WHERE id = ?", array('i', $id), false);
execSQL("SELECT * FROM table", array(), false);
execSQL("INSERT INTO table(id, name) VALUES (?,?)", array('ss', $id, $name), true);
<?php
function execSQL($sql, $params, $close){
$mysqli = new mysqli("localhost", "user", "pass", "db");
$stmt = $mysqli->prepare($sql) or die ("Failed to prepared the statement!");
call_user_func_array(array($stmt, 'bind_param'), refValues($params));
$stmt->execute();
if($close){
$result = $mysqli->affected_rows;
} else {
$meta = $stmt->result_metadata();
while ( $field = $meta->fetch_field() ) {
$parameters[] = &$row[$field->name];
}
call_user_func_array(array($stmt, 'bind_result'), refValues($parameters));
while ( $stmt->fetch() ) {
$x = array();
foreach( $row as $key => $val ) {
$x[$key] = $val;
}
$results[] = $x;
}
$result = $results;
}
$stmt->close();
$mysqli->close();
return $result;
}
function refValues($arr){
if (strnatcmp(phpversion(),'5.3') >= 0) //Reference is required for PHP 5.3+
{
$refs = array();
foreach($arr as $key => $value)
$refs[$key] = &$arr[$key];
return $refs;
}
return $arr;
}
?>
Regards
[#26] erik at gravyllc dot com [2010-09-21 21:05:10]
WOW! Thanks for the code that fixed the issue with mysqli_stmt_bind_param and PHP 5.3+. Worth sharing again for people getting the error message that a reference was expected and a value was provided. Here's a snippet and the whole function that fixed it!
//Use it like this
call_user_func_array('mysqli_stmt_bind_param', array_merge (array($sql_stmt, $type), $this->refValues($param)));
function refValues($arr)
{
if (strnatcmp(phpversion(),'5.3') >= 0) //Reference is required for PHP 5.3+
{
$refs = array();
foreach($arr as $key => $value)
$refs[$key] = &$arr[$key];
return $refs;
}
return $arr;
}
[#27] Mark Reddin [2010-09-08 03:30:16]
Miguel Hatrick's Statement_Parameter class, as posted in these notes, allows for a relatively painless way of writing secure dynamic SQL. It is secure against SQL injection because we still use bind parameters for any content coming from the user.
For example, the following code constructs an insert statement, but looks at which query string (GET) parameters are present in order to figure out which columns should be included. The ParameterManager.php file is simply Miguel's classes as posted in this discussion.
<?php
require_once("dbConnectionParams.php");
require_once("ParameterManager.php");
$sp = new Statement_Parameter();
$column_list = "";
$value_list = "";
if (isset ($_GET['name']) ) {
$column_list = $column_list . "name,";
$value_list = $value_list . "?,";
$sp->Add_Parameter('name', Statement_Parameter_Type::$STATEMENT_TYPE_STRING);
$sp->Set_Parameter('name',$_GET['name']);
}
if (isset($_GET['address']) ) {
$column_list = $column_list . "address,";
$value_list = $value_list . "?,";
$sp->Add_Parameter('address', Statement_Parameter_Type::$STATEMENT_TYPE_STRING);
$sp->Set_Parameter('address',$_GET['address']);
}
//tidy up column list and value list - the code above will always leave them ending in a comma, which we remove now
$column_list = substr($column_list, 0, strlen($column_list) -1);
$value_list = substr($value_list, 0, strlen($value_list) -1);
$sql = "insert into test_table (" . $column_list . ") values (" . $value_list . ");";
echo $sql;
$mysqli = @new mysqli($host,$user,$password,$database);
$stmt = $mysqli->prepare($sql);
$sp->Bind_Params($stmt);
if($stmt->execute() === TRUE)
{
$last_id = $mysqli->insert_id;
echo "OK$last_id";
}
else {
echo $mysqli->error;
}
?>
[#28] gregg at mochabomb dot com [2010-04-27 22:29:53]
Used the hints above - esp the call_user_func_array - what works simply is passing by reference...
<?php
class MySQL {
// so vars that are global to the class
var $connection;
var $dbc;
function __construct () {
$this->connection = new mysqli(DB_HOST, DB_USER, DB_PASSWORD, DB_NAME);
if (mysqli_connect_errno()) {
//printf("Could not connect to the DB: %s\n", mysqli_connect_error()); // TMI
printf("Could not connect to the DB");
exit();
}
}
function DBi($info) {
// a call has this array structure
// $this->info['params'] = array('is', &$user, &$name);
// $this->info['query'] = "select id, username, role_id from users where id = ? and name = ?";
// $this->info['close'] = "false"; // return $stmt for further work
//
$mysqli = $this->connection;
$params = $info['params'];
// print_r($info); // debug
if ($stmt = $mysqli->prepare($info['query'])) {
$ret = call_user_func_array (array($stmt,'bind_param'),$info['params']);
// $ret not used yet...
$stmt->execute();
if ($info['close'] == "true") {
$result = $mysqli->affected_rows;
$stmt->close();
return $result;
} else {
return $stmt;
}
} else { printf("Prepared Statement Error: $server_id \n"); }
}
}
?>
The close is generally for inserts, else the $stmt is returned for further processing...
<?php
// call the method to run the prepared query, then return statement handle. If just wanted an insert, use close = true
if ( is_int($user) ) {
$this->info['params'] = array('is', &$user, &$name);
$this->info['query'] = "select id, username, role_id from users where id = ? and name = ?";
$this->info['close'] = "false"; // return $stmt for further work
$stmt = parent::DBi($this->info);
$stmt->bind_result($col1, $col2, $col3);
while( $stmt->fetch() ) {
$res['id'] = $col1;
$res['username'] = $col2;
$res['role_id'] = $col3;
$res['error'] = 0;
}
$stmt->close;
return $res;
}
?>
[#29] eisoft [2010-03-18 15:42:16]
I did a prepared statement for inserting in a simple table - images ( blob ) and their unique identifiers ( string ). All my blobs have smaller sizes than the MAX-ALLOWED-PACKET value.
I've found that when binding my BLOB parameter, I need to pass it as a STRING, otherwise it's truncated to zero length in my table. So I have to do this:
<?php
$ok = $stmt->bind_param( 'ss', $id, $im ) ;
?>
[#30] fabio at kidopi dot com dot br [2010-03-15 09:14:42]
I used to have problems with call_user_func_array and bind_param after migrating to php 5.3.
The problem is that 5.3 requires array values as reference while 5.2 works with real values.
so i created a secondary function to help me with this...
<?php
function refValues($arr){
if (strnatcmp(phpversion(),'5.3') >= 0) //Reference is required for PHP 5.3+
{
$refs = array();
foreach($arr as $key => $value)
$refs[$key] = &$arr[$key];
return $refs;
}
return $arr;
}
?>
and changed my previous function from:
<?php
call_user_func_array(array($this->stmt, "bind_param"),$this->valores);
?>
to:
<?php
call_user_func_array(array($this->stmt, "bind_param"),refValues($this->valores));
?>
in this way my db functions keep working in php 5.2/5.3 servers.
I hope this help someone.
[#31] rejohns at nOsPaMpost dot harvard dot edu [2010-02-10 20:24:36]
You can, in fact, use mysqli_bind_parameter to pass a NULL value to the database. simply create a variable and store the NULL value (see the manpage for it) to the variable and bind that. Works great for me anyway.
[#32] Miguel Hatrick [2009-07-17 08:14:52]
This might be helpful for someone. I made a class to manage the parameters
Its used like this:
<?php
$stmt = $mysqli->prepare("CALL item_add(?, ?, ?, ?)");
$sp = new Statement_Parameter();
$sp->Add_Parameter('mydescription', Statement_Parameter_Type::$STATEMENT_TYPE_STRING);
$sp->Add_Parameter('myean', Statement_Parameter_Type::$STATEMENT_TYPE_STRING);
$sp->Add_Parameter('myprice', Statement_Parameter_Type::$STATEMENT_TYPE_DOUBLE);
$sp->Add_Parameter('myactive', Statement_Parameter_Type::$STATEMENT_TYPE_INTEGER);
// call this to bind the parameters
$sp->Bind_Params($stmt);
//you can then modify the values as you wish
$sp->Set_Parameter('myactive',0);
$sp->Set_Parameter('mydescription','whatever');
$stmt->execute();
class Statement_Parameter
{
private $_array = array();
public function __constructor()
{
}
public function Add_Parameter($name, $type, $value = NULL)
{
$this->_array[$name] = array("type" => $type, "value" => $value);
}
public function Get_Type_String()
{
$types = "";
foreach($this->_array as $name => $la)
$types .= $la['type'];
return $types;
}
public function Set_Parameter($name, $value)
{
if (isset($this->_array[$name]))
{
$this->_array[$name]["value"] = $value;
return true;
}
return false;
}
public function Bind_Params(&$stmt)
{
$ar = Array();
$ar[] = $this->Get_Type_String();
foreach($this->_array as $name => $la)
$ar[] = &$this->_array[$name]['value'];
return call_user_func_array(array($stmt, 'bind_param'),$ar);
}
}
class Statement_Parameter_Type
{
public static $STATEMENT_TYPE_INTEGER = 'i';
public static $STATEMENT_TYPE_DOUBLE = 'd';
public static $STATEMENT_TYPE_STRING = 's';
public static $STATEMENT_TYPE_BLOB = 'b';
}
?>
[#33] Kai Sellgren [2009-03-12 15:26:58]
A few notes on this function.
If you specify type "i" (integer), the maximum value it allows you to have is 2^32-1 or 2147483647. So, if you are using UNSIGNED INTEGER or BIGINT in your database, then you are better off using "s" (string) for this.
Here's a quick summary:
(UN)SIGNED TINYINT: I
(UN)SIGNED SMALLINT: I
(UN)SIGNED MEDIUMINT: I
SIGNED INT: I
UNSIGNED INT: S
(UN)SIGNED BIGINT: S
(VAR)CHAR, (TINY/SMALL/MEDIUM/BIG)TEXT/BLOB should all have S.
FLOAT/REAL/DOUBLE (PRECISION) should all be D.
That advice was for MySQL. I have not looked into other database software.
[#34] tasdildiren at gmail dot com [2009-02-25 02:29:21]
<?php
$sql_link = mysqli_connect('localhost', 'my_user', 'my_password', 'world');
$type = "isssi";
$param = array("5", "File Description", "File Title", "Original Name", time());
$sql = "INSERT INTO file_detail (file_id, file_description, file_title, file_original_name, file_upload_date) VALUES (?, ?, ?, ?, ?)";
$sql_stmt = mysqli_prepare ($sql_link, $sql);
call_user_func_array('mysqli_stmt_bind_param', array_merge (array($sql_stmt, $type), $param);
mysqli_stmt_execute($sql_stmt);
?>
[#35] Mahees [2009-02-21 10:34:08]
///////////////////////////////
Im sure many of you may want to use this functionality.
spent about 3hours writing this, so maybe i can save somone else some time, you can break it up into smaller functions for reuse as you wish.
the mysqli stmt bind param (mysqli_stmt_bind_param) function only takes one variable at a time, so its difficult to pass in a few variables to fill in the placeholder space.
this allows mysqli prepared statements with variable arguments, one sql template with multiple placeholders to be prepared and excuted.
hope this helps somone,
Mahees.
///////////////////////////////
<?php
$uname = 'mahees';
$pass = 'mahees';
$userPassArr = DataAccess::fetch('SELECT * FROM users WHERE username = ? AND password = ?', $uname, $pass);
print_r($userPassArr);
$userPassArr = DataAccess::fetch('SELECT * FROM users');
print_r($userPassArr);
/
if ( mysqli_connect_errno ()) {
printf ( "Connect failed: %s\n" , mysqli_connect_error ());
exit();
}
$stmt = $mysqli -> prepare ( "INSERT INTO CountryLanguage VALUES (?, ?, ?, ?)" );
$stmt -> bind_param ( 'sssd' , $code , $language , $official , $percent );
$code = 'DEU' ;
$language = 'Bavarian' ;
$official = "F" ;
$percent = 11.2 ;
$stmt -> execute ();
printf ( "%d Row inserted.\n" , $stmt -> affected_rows );
$stmt -> close ();
$mysqli -> query ( "DELETE FROM CountryLanguage WHERE Language='Bavarian'" );
printf ( "%d Row deleted.\n" , $mysqli -> affected_rows );
$mysqli -> close ();
?>
Example #2 过程化风格
<?php
$link = mysqli_connect ( 'localhost' , 'my_user' , 'my_password' , 'world' );
if (! $link ) {
printf ( "Connect failed: %s\n" , mysqli_connect_error ());
exit();
}
$stmt = mysqli_prepare ( $link , "INSERT INTO CountryLanguage VALUES (?, ?, ?, ?)" );
mysqli_stmt_bind_param ( $stmt , 'sssd' , $code , $language , $official , $percent );
$code = 'DEU' ;
$language = 'Bavarian' ;
$official = "F" ;
$percent = 11.2 ;
mysqli_stmt_execute ( $stmt );
printf ( "%d Row inserted.\n" , mysqli_stmt_affected_rows ( $stmt ));
mysqli_stmt_close ( $stmt );
mysqli_query ( $link , "DELETE FROM CountryLanguage WHERE Language='Bavarian'" );
printf ( "%d Row deleted.\n" , mysqli_affected_rows ( $link ));
mysqli_close ( $link );
?> 以上例程会输出：
1 Row inserted. 1 Row deleted.
参见
- mysqli_stmt_bind_result() - Binds variables to a prepared statement for result storage
- mysqli_stmt_execute() - Executes a prepared Query
- mysqli_stmt_fetch() - Fetch results from a prepared statement into the bound variables
- mysqli_prepare() - Prepare an SQL statement for execution
- mysqli_stmt_send_long_data() - Send data in blocks
- mysqli_stmt_errno() - Returns the error code for the most recent statement call
- mysqli_stmt_error() - Returns a string description for last statement error
用户评论:
[#1] bboymarco [2015-08-04 12:14:39]
I did for myself a really useful function (cause I didn't want to use eval() or reflection) that create for you a prepared statement with an indefinite number of parameters and return an indefinite number of result columns. Last two arguments are optional so when you don't have parameters to send to MySQL you can avoid them. I decided to share it here cause it may be helpful and save some time and brainstorming:
function prepared_stmt($con, $query, $type = "", $param = array()){
if($stmt = mysqli_prepare($con, $query)){
$refarg = array($stmt, $type);//First two parameter of mysqli_stmt_bind_param
foreach ($param as $key => $value)//create array of parameters' references
$refarg[] =& $param[$key];
if($type != "")//Jump instruction if argument $type is missing
call_user_func_array("mysqli_stmt_bind_param", $refarg);//bind parameters with dinamic length
if(mysqli_stmt_execute($stmt)){//check if execution go fine
$cols = mysqli_field_count($con);//retrive the number of columns of the resultset
$result = array_fill(0, $cols, NULL);//create an empty array with the same length of the columns
$ref = array($stmt);//first argument of mysqli_stmt_bind_result
foreach ($result as $key => $value)//create array of empty cells' references
$ref[] =& $result[$key];
call_user_func_array("mysqli_stmt_bind_result", $ref);//bind results with dinamic length
return $ref;//return statement and columns references
}//if
else
return false;
}//if
else
return false;
}//prepared_stmt
Example of call:
$hello = prepared_stmt($con, "SELECT Example FROM table WHERE Col=?", "s", array("LOL");
//count($hello) is equal to 2
//$hello[0] Statement reference
//$hello[1] Example column field where to find values after fetching
while(mysqli_stmt_fetch($hello[0]))
echo $hello[1] . "<br>";//This will print all value of the Example column
[#2] code at roberthairgrove dot com [2015-07-11 21:14:54]
When I tried to use nickv9's class BindParam, I had to make a small change to get it to work:
<?php
// snip...
public function add( $type, &$value ){
$this->values[] = &$value;
$this->types .= $type;
}
// snip...
?>
Note the additional reference notation used within the function to add the parameter to the class variable (&$value instead of $value), not just in the parameter list.
Otherwise, I got an error from call_user_func_array():
"Parameter 2 to mysqli_stmt::bind_param() expected to be a reference, value given..."
[#3] jmdoren at ok dot cl [2014-05-14 18:36:59]
I just wrote a function to bind a variable number of parameters
function bind_param_array( $stmt, $types, $vars ){
$php_command = '$stmt->bind_param( $types';
for( $i=0;$i<count($vars);$i++)
{
$php_command .= ',$vars['.$i.']';
}
$php_command .= ');';
return eval( $php_command );
}
yo can use it like this
$params = array( "one", "two", "two again" );
$types = str_repeat( 's', count( $params );
$stmt = $MysqliObject->prepare( $query );
bind_param_array( $stmt, $types,$params);
enjoy it!
[#4] tomasz at marcinkowski dot pl [2014-03-27 15:46:16]
When trying to bind a string param you get a "Number of variables doesn't match number of parameters in prepared statement" error, make sure you're not wrapping the question mark with quotes.
By mistake I had a query like:
SELECT something FROM table WHERE param_name = "?"
Binding it with
<?php $stmt->bind('s', $param_value); ?>
had been failing on me. All I had to do was to remove quotes around "?".
Hope this saves someone's time.
[#5] markac [2014-03-05 15:09:44]
Sprintf parameter binding style:
<?php
class MySqliDecorator {
private $db;
private $map = array(
'%d' => 'i', //integer
'%f' => 'd', //float
'%s' => 's', //string
);
public function __construct(mysqli $db) {
$this->db = $db;
}
public function prepare($query, &$params = array()) {
$expr = '/(' . implode('|', array_keys($this->map)) . ')/';
if (preg_match_all($expr, $query, $matches)) {
$types = implode('', $matches[0]);
$types = strtr($types, $this->map);
$query = preg_replace($expr, '?', $query);
if ($stmt = $this->db->prepare($query)) {
array_unshift($params, $types);
if (call_user_func_array(array($stmt, 'bind_param'), &$params)) {
return $stmt;
} else {
return false;
}
} else {
return false;
}
} else {
return $this->db->prepare($query);
}
}
}
?>
Using:
<?php
$db = new mysqli(DB_HOST, DB_USER, DB_PASS, DB_NAME);
$stmt = new MySqliDecorator($db);
$params = array(1, 'johny');
$stmt = $stmt->prepare('SELECT * FROM users WHERE Id = %d OR Username = %s', $params);
$stmt->execute();
?>
Be careful when using % with LIKE and so on.
[#6] Boyet [2013-10-23 12:56:48]
I think this is a good way to automatically get the types for the binding in as few lines of code as possible. If you bind the array values in the same order as when you used it for this you should not worry about aligning them.
foreach($array as $var){
$chrType = substr((string)gettype($var),0,1);
$strType .= (!in_array($chrType,array("i","d","s"))) ? "b" : $chrType;
}
[#7] laurence dot mackenzie at stream dot com [2013-10-08 21:08:56]
I just came across a very strange behaviour when using bind_param() with a reflection class. I figured I ought to post it here to save anyone else who comes across it from banging their head against their desk for an hour (as I just did).
First, some background: I have a set of classes, one per file format (i.e. CSV, HTML table, etc), which import data from flat files to a temporary table in my database. The class then transforms the data to 3NF.
I'm using a reflection class to pass an array to mysqli->bind_param() because the column counts and types are variable. The code (simplified) I am having issues with is:
<?php
$stmtInsert = $db->prepare('INSERT.....');
$typeString = 'ississis';
$data = array(1, 'two', 'three', 4, 'five', 'six', 7, 'eight');
// Merge the parameter types with the parameter values
$data = array_merge((array) $typeString, $data);
// Create the reflection class
$ref = new \ReflectionClass('mysqli_stmt');
// Get the bind_param method
$method = $ref->getMethod('bind_param');
// Invoke it with $data
$method->invokeArgs($stmtInsert, $data);
// Execute the statement
$stmtInsert->execute();
}
?>
Oddly, in one (and only one) case it started throwing "Warning: Parameter 41 to mysqli_stmt::bind_param() expected to be a reference, value given". The reflection class throws an exception. Other import sets using this code work just fine. Parameter 41 is the last parameter. Changing the affected code as follows resolves the issue:
<?php
$ref = new \ReflectionClass("mysqli_stmt");
$method = $ref->getMethod("bind_param");
$data[count($data)-1] = (string) $data[count($data)-1];
$method->invokeArgs($stmtInsert, $data);
$stmtInsert->execute();
?>
Not sure what's going on here, but like I said, hopefully this will keep the next person from thinking they're totally insane.
[#8] mike chip [2013-03-13 02:45:38]
I had to do a more advanced prepare function (A multi-multi values prepared statement)to make things easier for a few situations, so I wrote the following function.
NOTE: This function assumes you have put it into a class that have:
$mysqli = A mysqli object
NOTE2: This function currently only supports ints for the values, but you can modify it to support additional types...
To use it:
<?php
$sql = "SELECT id, code FROM country";
$result = $database->fetch_all($sql); // This is just a custom fetch_all function...
if(!$result) {
return null; // no results...
}
// Re-organize the order of the elements from the resultset, code first then id, just like the sql statement...
$pData = array();
foreach($result as $row) {
$pData[] = array('code'=>$row['code'] + 1,'id'=>$row['id']); //Assign valid php variable names, because this'll be used later on!!!
}
$database->mysqli_prepare_exec("UPDATE country SET code=? WHERE id=?", 'ii', $pData);
?>
Here's the function:
<?php
public function mysqli_prepare_exec($sql, $types, $data)
{
$stm = $this->mysqli->prepare($sql);
if(!$stm) {
return null; // Statement couldn't be prepared, check for errors...
}
foreach($data[0] as $k => $v) {
eval('$' . $k . '=' . $v . ';');
eval('$vars[\'' . $k . '\'] = &$' . $k . ';'); // Referencing to our last newly created variable
}
unset($k,$v);
call_user_func_array(array($stm, 'bind_param'), array_merge(array($types), $vars));
foreach($data as $row) {
foreach($row as $k => $v) {
eval('$' . $k . '=' . $v . ';');
}
$stm->execute();
}
}
?>
[#9] Ray.Paseur uses Gmail [2013-03-03 17:24:05]
It may seem obvious, but it's worth noting that variables must exist before you can bind them. This example worked well for me (line 12).
<?php
$test_names_arrays = array
( array( "fname" => "Walter" , "lname" => "Williams" )
, array( "fname" => "Ray" , "lname" => "Paseur" )
, array( "fname" => "Bill" , "lname" => "O'Reilly" )
)
;
$sql = "INSERT INTO my_table ( fname, lname ) VALUES ( ?,? )";
$ins = $mysqli->prepare($sql);
// NB: THESE VARIABLES MUST EXIST BEFORE THEY CAN BE BOUND!
$person = current($test_names_arrays);
$ins->bind_param('ss', $person['fname'], $person['lname']);
foreach ($test_names_arrays as $person)
{
$ins->execute();
}
?>
[#10] Guido [2012-10-15 19:43:54]
Dear all,
I was searching for a class which supports multiple calls to bind_param, because I have scenarios where I build huge SQL statements over different functions with variable numbers of parameters. But I didn't found one. So I have just written up this little piece of code I would like to share with you. There is enough room to optimize these classes, but it shows the general idea. And for me it works. In mbind_param_do() it seems to depend from the PHP version if makeValuesReferenced() must be used or if $params can be used directly. In my case I have to use it.
The cool thing about this solution: You don't have to care about a lot if you are using my mbind_ functions or not. You may also use default bind_param and the execute will still work.
<?php
class db extends mysqli {
public function prepare($query) {
return new stmt($this,$query);
}
}
class stmt extends mysqli_stmt {
public function __construct($link, $query) {
$this->mbind_reset();
parent::__construct($link, $query);
}
public function mbind_reset() {
unset($this->mbind_params);
unset($this->mbind_types);
$this->mbind_params = array();
$this->mbind_types = array();
}
//use this one to bind params by reference
public function mbind_param($type, &$param) {
$this->mbind_types[0].= $type;
$this->mbind_params[] = &$param;
}
//use this one to bin value directly, can be mixed with mbind_param()
public function mbind_value($type, $param) {
$this->mbind_types[0].= $type;
$this->mbind_params[] = $param;
}
public function mbind_param_do() {
$params = array_merge($this->mbind_types, $this->mbind_params);
return call_user_func_array(array($this, 'bind_param'), $this->makeValuesReferenced($params));
}
private function makeValuesReferenced($arr){
$refs = array();
foreach($arr as $key => $value)
$refs[$key] = &$arr[$key];
return $refs;
}
public function execute() {
if(count($this->mbind_params))
$this->mbind_param_do();
return parent::execute();
}
private $mbind_types = array();
private $mbind_params = array();
}
$search1 = "test1";
$search2 = "test2";
$_db = new db("host","user","pass","database");
$query = "SELECT name FROM table WHERE col1=? AND col2=?";
$stmt = $_db->prepare($query);
$stmt->mbind_param('s',$search1);
//this second call is the cool thing!!!
$stmt->mbind_param('s',$search2);
$stmt->execute();
//this would still work!
//$search1 = "test1changed";
//$search2 = "test2changed";
//$stmt->execute();
...
$stmt->store_result();
$stmt->bind_result(...);
$stmt->fetch();
?>
[#11] xianrenb at gmail dot com [2012-09-30 06:52:17]
It is believed that if one has specified 'b' in $types, the corresponding variable should be set to null, and one has to use mysqli_stmt::send_long_data() or mysqli_stmt_send_long_data() to send the blob, otherwise the blob value would be treated as empty.
[#12] ellert at _spam_ vankoperen _spam_ dot nl [2012-08-29 09:43:46]
Dont forget that in the query that you are preparing you do NOT need quotes around string values.
If you put them there anyway, you will get:
mysqli_stmt_bind_param(): Number of variables doesn't match number of parameters in prepared statement
[#13] nick9v at hotmail dot com [2012-06-30 10:12:03]
When dealing with a dynamic number of field values while preparing a statement I find this class useful.
<?php
class BindParam{
private $values = array(), $types = '';
public function add( $type, &$value ){
$this->values[] = $value;
$this->types .= $type;
}
public function get(){
return array_merge(array($this->types), $this->values);
}
}
?>
Usage is pretty simple. Create an instance and use the add method to populate. When you're ready to execute simply use the get method.
<?php
$bindParam = new BindParam();
$qArray = array();
$use_part_1 = 1;
$use_part_2 = 1;
$use_part_3 = 1;
$query = 'SELECT * FROM users WHERE ';
if($use_part_1){
$qArray[] = 'hair_color = ?';
$bindParam->add('s', 'red');
}
if($use_part_2){
$qArray[] = 'age = ?';
$bindParam->add('i', 25);
}
if($use_part_3){
$qArray[] = 'balance = ?';
$bindParam->add('d', 50.00);
}
$query .= implode(' OR ', $qArray);
//call_user_func_array( array($stm, 'bind_param'), $bindParam->get());
echo $query . '<br/>';
var_dump($bindParam->get());
?>
This gets you the result that looks something like this:
SELECT * FROM users WHERE hair_color = ? OR age = ? OR balance = ?
array(4) { [0]=> string(3) "sid" [1]=> string(3) "red" [2]=> int(25) [3]=> float(50) }
[Editor's note: changed BindParam::add() to accept $value by reference and thereby prevent a warning in newer versions of PHP.]
[#14] robstocki at battlesecure dot com [2012-05-24 22:22:38]
Here is the procedural version of a select statement when wanting to use %LIKE% in the query and not an '=':
<?php
function db_connect(){
// set $db as global for access outside function
global $db;
# Use procedural methods for database connection and manipulation
// Connect to Database
@$db = mysqli_connect(DB_HOST, DB_USER, DB_PASSWORD, DB_NAME);
// Check connection
if(mysqli_connect_errno()) {
echo '<br />Error: Could not connect to database. Please try again later.<br />';
exit;
}
}
// Connect to Database
db_connect();
$theRecordInTheTableIs = "%".$_POST['theRecordInTheTableIs']."%"; // concat $_POST variable with % on each side for use in prepared statement
$theRelatedRecordLooks = "%".$_POST['theRelatedRecordLooks']."%"; // concat $_POST variable with % on each side for use in prepared statement
// Create Query
$q = ''; // create variable for input
$q .= " SELECT ";
$q .= " * ";
$q .= " FROM ";
$q .= " myFavTable ";
$q .= " WHERE ";
$q .= " theRecordInTheTableIs LIKE ? ";
$q .= " AND ";
$q .= " theRelatedRecordLooks LIKE ? ";
// Prepare the statement
$stmt = mysqli_prepare($db, $q);
// bind the paramaters
mysqli_stmt_bind_param($stmt, 'ss', $theRecordInTheTableIs,$theRelatedRecordLooks);
// Execute the query
mysqli_stmt_execute($stmt);
// bind result variables
mysqli_stmt_bind_result($stmt, $col0, $col1, $col2, $col3, $col4, $col5, $col6, $col7, $col8, $col9, $col10);
// Display the query
// start the table
echo '<table>
<tr>
<td>Col0 Title</td>
<td>Col1 Title</td>
<td>Col2 Title</td>
<td>Col3 Title</td>
<td>Col4 Title</td>
<td>Col5 Title</td>
<td>Col6 Title</td>
<td>Col7 Title</td>
<td>Col8 Title</td>
<td>Col9 Title</td>
<td>Col10 Title</td>
</tr>';
// fetch values
while (mysqli_stmt_fetch($stmt)) {
echo " <tr>
<td>{$col0}</td>
<td>{$col1}</td>
<td>{$col2}</td>
<td>{$col3}</td>
<td>{$col4}</td>
<td>{$col5}</td>
<td>{$col6}</td>
<td>{$col7}</td>
<td>{$col8}</td>
<td>{$col9}</td>
<td>{$col10}</td>
</tr> ";
}
// close the table
echo '</table>';
?>
[#15] Darren [2012-01-11 04:00:21]
For those learning mysqli::prepare and mysqli_stmt::bind_params for the first time, here is a commented block of code which executes prepared queries and returns data in a similar format to the return values of mysqli_query. I tried to minimize unnecessary classes, objects, or overhead for two reasons:
1) facilitate learning
2) allow relativity interchangeable use with mysqli_query
My goal is to lower the learning curve for whoever is starting out with these family of functions. I am by no means an expert coder/scripter, so I am sure there are improvements and perhaps a few bugs, but I hope not =)
<?php
function mysqli_prepared_query($link,$sql,$bindParams = FALSE){
if($stmt = mysqli_prepare($link,$sql)){
if ($bindParams){
$bindParamsMethod = new ReflectionMethod('mysqli_stmt', 'bind_param'); //allows for call to mysqli_stmt->bind_param using variable argument list
$bindParamsReferences = array(); //will act as arguments list for mysqli_stmt->bind_param
$typeDefinitionString = array_shift($bindParams);
foreach($bindParams as $key => $value){
$bindParamsReferences[$key] = &$bindParams[$key];
}
array_unshift($bindParamsReferences,$typeDefinitionString); //returns typeDefinition as the first element of the string
$bindParamsMethod->invokeArgs($stmt,$bindParamsReferences); //calls mysqli_stmt->bind_param suing $bindParamsRereferences as the argument list
}
if(mysqli_stmt_execute($stmt)){
$resultMetaData = mysqli_stmt_result_metadata($stmt);
if($resultMetaData){
$stmtRow = array(); //this will be a result row returned from mysqli_stmt_fetch($stmt)
$rowReferences = array(); //this will reference $stmtRow and be passed to mysqli_bind_results
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); //calls mysqli_stmt_bind_result($stmt,[$rowReferences]) using object-oriented style
$result = array();
while(mysqli_stmt_fetch($stmt)){
foreach($stmtRow as $key => $value){ //variables must be assigned by value, so $result[] = $stmtRow does not work (not really sure why, something with referencing in $stmtRow)
$row[$key] = $value;
}
$result[] = $row;
}
mysqli_stmt_free_result($stmt);
} else {
$result = mysqli_stmt_affected_rows($stmt);
}
mysqli_stmt_close($stmt);
} else {
$result = FALSE;
}
} else {
$result = FALSE;
}
return $result;
}
?>
Here's hoping the PHP gods don't smite me.
[#16] samishiikihaku23 at gmail dot com [2011-12-30 15:50:56]
NOTES to new users! Or programmers like myself who learn the hard way!!!
Pay attention to the variables that are given in the function up above. ( string $types , mixed &$var1 [, mixed &$... ] ) . The example shows this too, but I personally didn't get it 'til trying to debug my code.
ITS REQUIRED TO PASS VARIABLES HERE. You can not pass straight data through here.
$stmt->bind('s','Something here'); Will error!!!
Just a clarification as to avoid another night like mine last night and this morning.
[#17] Ole Clausen [2011-11-06 11:50:23]
A lot of newcommers to mysqli find it hard to get started. I have written this wrapper with object based response, that handles most of my queries. I hope it'll be usefull for others as well:
<?php
define('DB_HOST', 'localhost');
define('DB_USERNAME', '');
define('DB_PASSWORD', '');
define('DB_DEFAULT_DB', 'test');
function iQuery($sql, $arrParams, $arrBindNames=false) {
$result = new stdClass();
$mysqli = @new mysqli(DB_HOST, DB_USERNAME, DB_PASSWORD, DB_DEFAULT_DB);
if (mysqli_connect_errno()) {
$result->error = 'Connection failed: '.mysqli_connect_error();
return $result;
}
if ($stmt = $mysqli->prepare($sql)) {
$method = new ReflectionMethod('mysqli_stmt', 'bind_param');
$method->invokeArgs($stmt, $arrParams);
$stmt->execute();
$meta = $stmt->result_metadata();
if (!$meta) {
$result->affected_rows = $stmt->affected_rows;
$result->insert_id = $stmt->insert_id;
} else {
$stmt->store_result();
$params = array();
$row = array();
if ($arrBindNames) {
for ($i=0,$j=count($arrBindNames); $i<$j; $i++) {
$params[$i] = &$row[$arrBindNames[$i]];
}
} else {
while ($field = $meta->fetch_field()) {
$params[] = &$row[$field->name];
}
}
$meta->close();
$method = new ReflectionMethod('mysqli_stmt', 'bind_result');
$method->invokeArgs($stmt, $params);
$result->rows = array();
while ($stmt->fetch()) {
$obj = new stdClass();
foreach($row as $key => $val) {
$obj->{$key} = $val;
}
$result->rows[] = $obj;
}
$stmt->free_result();
}
$stmt->close();
}
$mysqli->close();
return $result;
}
$arrParams = array('ss', $_POST['sex'], $_POST['active']);
$result = iQuery( 'SELECT * FROM `test_table` WHERE `sex`=? AND `active`=?', $arrParams);
print_r($result);
print $result->rows[1]->first_name . " " . $result->rows[1]->last_name;
?>
If $_POST['sex'] contains 'male' and $_POST['active'] contains 'yes' - and the field names are 'id', 'first_name', 'last_name', 'sex' and 'active', the printet result may look like this:
----------------------------------------------
stdClass Object
(
[rows] => Array
(
[0] => stdClass Object
(
[id] => 2
[first_name] => Peter
[last_name] => Johnson
[sex] => male
[active] => yes
)
[1] => stdClass Object
(
[id] => 5
[first_name] => Ole
[last_name] => Clausen
[sex] => male
[active] => yes
)
)
)
Ole Clausen
----------------------------------------------
You can also apply special field names to the response, if you use the parameter $arrBindNames:
$arrParams = array('ss', $_POST['sex'], $_POST['active']);
$arrNames = array('foo_id', 'bar_first', 'baz_last', 'foo_sex', 'bar_act');
$result = iQuery( 'SELECT * FROM `test_table` WHERE `sex`=? AND `active`=?', $arrParams, $arrNames);
- a row would then look like this:
[0] => stdClass Object
(
[foo_id] => 2
[bar_first] => Peter
[baz_last] => Johnson
[foo_sex] => male
[bar_act] => yes
)
The first argument 'ss' in $arrParams states, that the two following arguments are of type String. The options are 's' for String, 'i' for Integer, 'd' for Double and 'b' for Blob (sent in packages).
In queries, that do not return a result INSERT, UPDATE, etc. $result->affected_rows and $result->insert_id are available. Connection errors are available in $result->error. Additional error handling would be nice, but is not implemented for now. Play with the wrapper and use print_r on the result ... enjoy!
The name 'iQuery'? Well, it handles mysql*i* - and then I guess it's kind of a tribute to Mr. Jobs ... may he 'rest' in energetic, hungry foolishness =)
==
UPDATE: 08-NOV-2011 07:19
Due to changes in PHP 5.3 I encountered a problem with 'bind_param' in my iQuery function below. The values in the passed array *must* be references. The soloution is this function:
<?php
function getRefArray($a) {
if (strnatcmp(phpversion(),'5.3')>=0) {
$ret = array();
foreach($a as $key => $val) {
$ret[$key] = &$a[$key];
}
return $ret;
}
return $a;
}
?>
- and this change in iQuery:
if ($stmt = $mysqli->prepare($sql)) {
$arrParams = getRefArray($arrParams); // <-- Added due to changes since PHP 5.3
$method = new ReflectionMethod('mysqli_stmt', 'bind_param');
$method->invokeArgs($stmt, $arrParams);
$stmt->execute();
$meta = $stmt->result_metadata();
[#18] ghostarthour at exahost dot eu [2011-08-27 14:00:44]
Simple workaround for casting:
<?php
function mysqli_cast_workaround() {
$numargs = func_num_args();
$types = '';
for ($i = 0; $i < $numargs; $i++) {
$arg = func_get_arg($i);
if (is_numeric($arg)) {
$types.="i";
}else{
$types.="s";
}
$args[($i+1)] = $arg;
unset($arg);
}
$args[0] = $types;
return call_user_func_array('mysqli_stmt_bind_param',$args);
}
?>
[#19] alex dot deleyn at gmail dot com [2011-07-16 18:50:37]
MySQL has a "NULL-safe equal" operator (I'm guessing since 5.0)
http://dev.mysql.com/doc/refman/5.0/en/comparison-operators.html#operator_equal-to
If you use this operator instead of the usual = you can interchange values and null in where clauses.
There is however a known bug when using this operator with datetime or timestamp fields: http://bugs.mysql.com/bug.php?id=36100
[#20] jk at jankriedner dot de [2011-05-20 14:39:25]
There are some things to note when working with mysqli::bind_param() and array-elements.
Re-assigning an array will break any references, no matter if the keys are identical.
You have to explicitly reassign every single value in an array, for the references to be kept.
Best shown in an example:
<?php
function getData() {
return array(
0=>array(
"name"=>"test_0",
"email"=>"test_0@example.com"
),
1=>array(
"name"=>"test_1",
"email"=>"test_1@example.com"
)
);
}
$db = new mysqli("localhost","root","","tests");
$sql = "INSERT INTO `user` SET `name`=?,`email`=?";
$res = $db->prepare($sql);
// If you bind array-elements to a prepared statement, the array has to be declared first with the used keys:
$arr = array("name"=>"","email"=>"");
$res->bind_param("ss",$arr['name'],$arr['email']);
//So far the introduction...
foreach( getData() as $arr ) {
$res->execute();
}
foreach( getData() as $tempArr ) {
foreach($tempArr as $k=>$v) {
$arr[$k] = $v;
}
$res->execute();
}
?>
Coming to the problem calling mysqli::bind_param() with a dynamic number of arguments via call_user_func_array() with PHP Version 5.3+, there's another workaround besides using an extra function to build the references for the array-elements.
You can use Reflection to call mysqli::bind_param(). When using PHP 5.3+ this saves you about 20-40% Speed compared to passing the array to your own reference-builder-function.
Example:
<?php
$db = new mysqli("localhost","root","","tests");
$res = $db->prepare("INSERT INTO test SET foo=?,bar=?");
$refArr = array("si","hello",42);
$ref = new ReflectionClass('mysqli_stmt');
$method = $ref->getMethod("bind_param");
$method->invokeArgs($res,$refArr);
$res->execute();
?>
[#21] asb(.d o,t )han(a t)n i h e i(d.o_t)dk [2011-04-22 18:05:35]
It should be noted that MySQL has some issues regarding the use of the IN clause in prepared statements.
I.e. the code:
<?php
$idArr = "1, 2, 3, 4";
$int_one = 1;
$int_two = 2;
$int_three = 3;
$int_four = 4;
$db = new MySQLi();
$bad_stmt = $db->prepare(SELECT `idAsLetters` FROM `tbl` WHERE `id` IN(?));
$bad_stmt->bind_param("s", $idArr);
$bad_stmt->bind_result($ias);
$bad_stmt->execute();
echo "Bad results:" . PHP_EOL;
while($stmt->fetch()){
echo $ias . PHP_EOL;
}
$good_stmt->close();
$good_stmt = $db->prepare(SELECT `idAsLetters` FROM `tbl` WHERE `id` IN(?, ?, ?, ?));
$good_stmt->bind_param("iiii", $int_one, $int_two, $int_three, $int_four);
$good_stmt->bind_result($ias);
$good_stmt->execute();
echo "God results:" . PHP_EOL;
while($stmt->fetch()){
echo $ias . PHP_EOL;
}
$bad_stmt->close();
$db->close();
?>
will print this result:
Bad results:
one
Good results:
one
two
three
four
Using "IN(?)" in a prepared statement will return just one (the first) row from a table/view. This is not an error in PHP, but merely how MySQL handles prepared statements.
[#22] Anonymous [2011-03-28 13:49:51]
You can bind to variables with NULL values, and on update and insert queries, the corresponding field will be updated to NULL no matter what bind string type you associated it with. But, for parameters meant for the WHERE clause (ie where field = ?), the query will have no effect and produce no results.
When comparing a value against NULL, the MYSQL syntax is either "value IS NULL" or "value IS NOT NULL". So, you can't pass in something like "WHERE (value = ?)" and expect this to work using a null value parameter.
Instead, you can do something like this in your WHERE clause:
"WHERE (IF(ISNULL(?), field1 is null, field1 = ?))"
Then, pass in the value you want to test twice:
bind_param('ss', $value1, $value1);
[#23] Anonymous [2011-03-14 14:28:02]
Blob and null handling aside, a couple of notes on how param values are automatically converted and forwarded on to the Mysql engine based on your type string argument:
1) PHP will automatically convert the value behind the scenes to the underlying type corresponding to your binding type string. i.e.:
<?php
$var = true;
bind_param('i', $var); // forwarded to Mysql as 1
?>
2) Though PHP numbers cannot be reliably cast to (int) if larger than PHP_INT_MAX, behind the scenes, the value will be converted anyway to at most long long depending on the size. This means that keeping in mind precision limits and avoiding manually casting the variable to (int) first, you can still use the 'i' binding type for larger numbers. i.e.:
<?php
$var = '429496729479896';
bind_param('i', $var); // forwarded to Mysql as 429496729479900
?>
3) You can default to 's' for most parameter arguments in most cases. The value will then be automatically cast to string on the back-end before being passed to the Mysql engine. Mysql will then perform its own conversions with values it receives from PHP on execute. This allows you to bind not only to larger numbers without concern for precision, but also to objects as long as that object has a '__toString' method.
This auto-string casting behavior greatly improves things like datetime handling. For example: if you extended DateTime class to add a __toString method which outputs the datetime format expected by Mysql, you can just bind to that DateTime_Extended object using type 's'. i.e.:
<?php
// DateTime_Extended has __toString defined to return the Mysql formatted datetime
$var = new DateTime_Extended;
bind_param('s', $var); // forwarded to Mysql as '2011-03-14 17:00:01'
?>
[#24] user at mail dot com [2011-01-25 00:44:30]
I had a problem with the LIKE operator
This code did not work:
<?php
$test = $sql->prepare("SELECT name FROM names WHERE name LIKE %?%");
$test->bind_param("s", $myname);
?>
The solution is:
<?php
$test = $sql->prepare("SELECT name FROM names WHERE name LIKE ?");
$param = "%" . $myname . "%";
$test->bind_param("s", $param);
?>
[#25] canche_x at yahoo dot com [2010-11-12 04:43:02]
Hi, I just write a function to do all my sql statements based on all the others comments in this page, maybe it can be useful for someone else :)
Usage:
execSQL($sql, $parameters, $close);
$sql = Statement to execute;
$parameters = array of type and values of the parameters (if any)
$close = true to close $stmt (in inserts) false to return an array with the values;
Examples:
execSQL("SELECT * FROM table WHERE id = ?", array('i', $id), false);
execSQL("SELECT * FROM table", array(), false);
execSQL("INSERT INTO table(id, name) VALUES (?,?)", array('ss', $id, $name), true);
<?php
function execSQL($sql, $params, $close){
$mysqli = new mysqli("localhost", "user", "pass", "db");
$stmt = $mysqli->prepare($sql) or die ("Failed to prepared the statement!");
call_user_func_array(array($stmt, 'bind_param'), refValues($params));
$stmt->execute();
if($close){
$result = $mysqli->affected_rows;
} else {
$meta = $stmt->result_metadata();
while ( $field = $meta->fetch_field() ) {
$parameters[] = &$row[$field->name];
}
call_user_func_array(array($stmt, 'bind_result'), refValues($parameters));
while ( $stmt->fetch() ) {
$x = array();
foreach( $row as $key => $val ) {
$x[$key] = $val;
}
$results[] = $x;
}
$result = $results;
}
$stmt->close();
$mysqli->close();
return $result;
}
function refValues($arr){
if (strnatcmp(phpversion(),'5.3') >= 0) //Reference is required for PHP 5.3+
{
$refs = array();
foreach($arr as $key => $value)
$refs[$key] = &$arr[$key];
return $refs;
}
return $arr;
}
?>
Regards
[#26] erik at gravyllc dot com [2010-09-21 21:05:10]
WOW! Thanks for the code that fixed the issue with mysqli_stmt_bind_param and PHP 5.3+. Worth sharing again for people getting the error message that a reference was expected and a value was provided. Here's a snippet and the whole function that fixed it!
//Use it like this
call_user_func_array('mysqli_stmt_bind_param', array_merge (array($sql_stmt, $type), $this->refValues($param)));
function refValues($arr)
{
if (strnatcmp(phpversion(),'5.3') >= 0) //Reference is required for PHP 5.3+
{
$refs = array();
foreach($arr as $key => $value)
$refs[$key] = &$arr[$key];
return $refs;
}
return $arr;
}
[#27] Mark Reddin [2010-09-08 03:30:16]
Miguel Hatrick's Statement_Parameter class, as posted in these notes, allows for a relatively painless way of writing secure dynamic SQL. It is secure against SQL injection because we still use bind parameters for any content coming from the user.
For example, the following code constructs an insert statement, but looks at which query string (GET) parameters are present in order to figure out which columns should be included. The ParameterManager.php file is simply Miguel's classes as posted in this discussion.
<?php
require_once("dbConnectionParams.php");
require_once("ParameterManager.php");
$sp = new Statement_Parameter();
$column_list = "";
$value_list = "";
if (isset ($_GET['name']) ) {
$column_list = $column_list . "name,";
$value_list = $value_list . "?,";
$sp->Add_Parameter('name', Statement_Parameter_Type::$STATEMENT_TYPE_STRING);
$sp->Set_Parameter('name',$_GET['name']);
}
if (isset($_GET['address']) ) {
$column_list = $column_list . "address,";
$value_list = $value_list . "?,";
$sp->Add_Parameter('address', Statement_Parameter_Type::$STATEMENT_TYPE_STRING);
$sp->Set_Parameter('address',$_GET['address']);
}
//tidy up column list and value list - the code above will always leave them ending in a comma, which we remove now
$column_list = substr($column_list, 0, strlen($column_list) -1);
$value_list = substr($value_list, 0, strlen($value_list) -1);
$sql = "insert into test_table (" . $column_list . ") values (" . $value_list . ");";
echo $sql;
$mysqli = @new mysqli($host,$user,$password,$database);
$stmt = $mysqli->prepare($sql);
$sp->Bind_Params($stmt);
if($stmt->execute() === TRUE)
{
$last_id = $mysqli->insert_id;
echo "OK$last_id";
}
else {
echo $mysqli->error;
}
?>
[#28] gregg at mochabomb dot com [2010-04-27 22:29:53]
Used the hints above - esp the call_user_func_array - what works simply is passing by reference...
<?php
class MySQL {
// so vars that are global to the class
var $connection;
var $dbc;
function __construct () {
$this->connection = new mysqli(DB_HOST, DB_USER, DB_PASSWORD, DB_NAME);
if (mysqli_connect_errno()) {
//printf("Could not connect to the DB: %s\n", mysqli_connect_error()); // TMI
printf("Could not connect to the DB");
exit();
}
}
function DBi($info) {
// a call has this array structure
// $this->info['params'] = array('is', &$user, &$name);
// $this->info['query'] = "select id, username, role_id from users where id = ? and name = ?";
// $this->info['close'] = "false"; // return $stmt for further work
//
$mysqli = $this->connection;
$params = $info['params'];
// print_r($info); // debug
if ($stmt = $mysqli->prepare($info['query'])) {
$ret = call_user_func_array (array($stmt,'bind_param'),$info['params']);
// $ret not used yet...
$stmt->execute();
if ($info['close'] == "true") {
$result = $mysqli->affected_rows;
$stmt->close();
return $result;
} else {
return $stmt;
}
} else { printf("Prepared Statement Error: $server_id \n"); }
}
}
?>
The close is generally for inserts, else the $stmt is returned for further processing...
<?php
// call the method to run the prepared query, then return statement handle. If just wanted an insert, use close = true
if ( is_int($user) ) {
$this->info['params'] = array('is', &$user, &$name);
$this->info['query'] = "select id, username, role_id from users where id = ? and name = ?";
$this->info['close'] = "false"; // return $stmt for further work
$stmt = parent::DBi($this->info);
$stmt->bind_result($col1, $col2, $col3);
while( $stmt->fetch() ) {
$res['id'] = $col1;
$res['username'] = $col2;
$res['role_id'] = $col3;
$res['error'] = 0;
}
$stmt->close;
return $res;
}
?>
[#29] eisoft [2010-03-18 15:42:16]
I did a prepared statement for inserting in a simple table - images ( blob ) and their unique identifiers ( string ). All my blobs have smaller sizes than the MAX-ALLOWED-PACKET value.
I've found that when binding my BLOB parameter, I need to pass it as a STRING, otherwise it's truncated to zero length in my table. So I have to do this:
<?php
$ok = $stmt->bind_param( 'ss', $id, $im ) ;
?>
[#30] fabio at kidopi dot com dot br [2010-03-15 09:14:42]
I used to have problems with call_user_func_array and bind_param after migrating to php 5.3.
The problem is that 5.3 requires array values as reference while 5.2 works with real values.
so i created a secondary function to help me with this...
<?php
function refValues($arr){
if (strnatcmp(phpversion(),'5.3') >= 0) //Reference is required for PHP 5.3+
{
$refs = array();
foreach($arr as $key => $value)
$refs[$key] = &$arr[$key];
return $refs;
}
return $arr;
}
?>
and changed my previous function from:
<?php
call_user_func_array(array($this->stmt, "bind_param"),$this->valores);
?>
to:
<?php
call_user_func_array(array($this->stmt, "bind_param"),refValues($this->valores));
?>
in this way my db functions keep working in php 5.2/5.3 servers.
I hope this help someone.
[#31] rejohns at nOsPaMpost dot harvard dot edu [2010-02-10 20:24:36]
You can, in fact, use mysqli_bind_parameter to pass a NULL value to the database. simply create a variable and store the NULL value (see the manpage for it) to the variable and bind that. Works great for me anyway.
[#32] Miguel Hatrick [2009-07-17 08:14:52]
This might be helpful for someone. I made a class to manage the parameters
Its used like this:
<?php
$stmt = $mysqli->prepare("CALL item_add(?, ?, ?, ?)");
$sp = new Statement_Parameter();
$sp->Add_Parameter('mydescription', Statement_Parameter_Type::$STATEMENT_TYPE_STRING);
$sp->Add_Parameter('myean', Statement_Parameter_Type::$STATEMENT_TYPE_STRING);
$sp->Add_Parameter('myprice', Statement_Parameter_Type::$STATEMENT_TYPE_DOUBLE);
$sp->Add_Parameter('myactive', Statement_Parameter_Type::$STATEMENT_TYPE_INTEGER);
// call this to bind the parameters
$sp->Bind_Params($stmt);
//you can then modify the values as you wish
$sp->Set_Parameter('myactive',0);
$sp->Set_Parameter('mydescription','whatever');
$stmt->execute();
class Statement_Parameter
{
private $_array = array();
public function __constructor()
{
}
public function Add_Parameter($name, $type, $value = NULL)
{
$this->_array[$name] = array("type" => $type, "value" => $value);
}
public function Get_Type_String()
{
$types = "";
foreach($this->_array as $name => $la)
$types .= $la['type'];
return $types;
}
public function Set_Parameter($name, $value)
{
if (isset($this->_array[$name]))
{
$this->_array[$name]["value"] = $value;
return true;
}
return false;
}
public function Bind_Params(&$stmt)
{
$ar = Array();
$ar[] = $this->Get_Type_String();
foreach($this->_array as $name => $la)
$ar[] = &$this->_array[$name]['value'];
return call_user_func_array(array($stmt, 'bind_param'),$ar);
}
}
class Statement_Parameter_Type
{
public static $STATEMENT_TYPE_INTEGER = 'i';
public static $STATEMENT_TYPE_DOUBLE = 'd';
public static $STATEMENT_TYPE_STRING = 's';
public static $STATEMENT_TYPE_BLOB = 'b';
}
?>
[#33] Kai Sellgren [2009-03-12 15:26:58]
A few notes on this function.
If you specify type "i" (integer), the maximum value it allows you to have is 2^32-1 or 2147483647. So, if you are using UNSIGNED INTEGER or BIGINT in your database, then you are better off using "s" (string) for this.
Here's a quick summary:
(UN)SIGNED TINYINT: I
(UN)SIGNED SMALLINT: I
(UN)SIGNED MEDIUMINT: I
SIGNED INT: I
UNSIGNED INT: S
(UN)SIGNED BIGINT: S
(VAR)CHAR, (TINY/SMALL/MEDIUM/BIG)TEXT/BLOB should all have S.
FLOAT/REAL/DOUBLE (PRECISION) should all be D.
That advice was for MySQL. I have not looked into other database software.
[#34] tasdildiren at gmail dot com [2009-02-25 02:29:21]
<?php
$sql_link = mysqli_connect('localhost', 'my_user', 'my_password', 'world');
$type = "isssi";
$param = array("5", "File Description", "File Title", "Original Name", time());
$sql = "INSERT INTO file_detail (file_id, file_description, file_title, file_original_name, file_upload_date) VALUES (?, ?, ?, ?, ?)";
$sql_stmt = mysqli_prepare ($sql_link, $sql);
call_user_func_array('mysqli_stmt_bind_param', array_merge (array($sql_stmt, $type), $param);
mysqli_stmt_execute($sql_stmt);
?>
[#35] Mahees [2009-02-21 10:34:08]
///////////////////////////////
Im sure many of you may want to use this functionality.
spent about 3hours writing this, so maybe i can save somone else some time, you can break it up into smaller functions for reuse as you wish.
the mysqli stmt bind param (mysqli_stmt_bind_param) function only takes one variable at a time, so its difficult to pass in a few variables to fill in the placeholder space.
this allows mysqli prepared statements with variable arguments, one sql template with multiple placeholders to be prepared and excuted.
hope this helps somone,
Mahees.
///////////////////////////////
<?php
$uname = 'mahees';
$pass = 'mahees';
$userPassArr = DataAccess::fetch('SELECT * FROM users WHERE username = ? AND password = ?', $uname, $pass);
print_r($userPassArr);
$userPassArr = DataAccess::fetch('SELECT * FROM users');
print_r($userPassArr);
/
if ( $stmt = $mysqli -> prepare ( "SELECT Code, Name FROM Country ORDER BY Name LIMIT 5" )) {
$stmt -> execute ();
$stmt -> bind_result ( $col1 , $col2 );
while ( $stmt -> fetch ()) {
printf ( "%s %s\n" , $col1 , $col2 );
}
$stmt -> close ();
}
$mysqli -> close ();
?>
Example #2 过程化风格
<?php
$link = mysqli_connect ( "localhost" , "my_user" , "my_password" , "world" );
if (! $link ) {
printf ( "Connect failed: %s\n" , mysqli_connect_error ());
exit();
}
if ( $stmt = mysqli_prepare ( $link , "SELECT Code, Name FROM Country ORDER BY Name LIMIT 5" )) {
mysqli_stmt_execute ( $stmt );
mysqli_stmt_bind_result ( $stmt , $col1 , $col2 );
while ( mysqli_stmt_fetch ( $stmt )) {
printf ( "%s %s\n" , $col1 , $col2 );
}
mysqli_stmt_close ( $stmt );
}
mysqli_close ( $link );
?> 以上例程会输出：
AFG Afghanistan ALB Albania DZA Algeria ASM American Samoa AND Andorra
参见
- mysqli_stmt_get_result() - Gets a result set from a prepared statement
- mysqli_stmt_bind_param() - Binds variables to a prepared statement as parameters
- mysqli_stmt_execute() - Executes a prepared Query
- mysqli_stmt_fetch() - Fetch results from a prepared statement into the bound variables
- mysqli_prepare() - Prepare an SQL statement for execution
- mysqli_stmt_prepare() - Prepare an SQL statement for execution
- mysqli_stmt_init() - 初始化一条语句并返回一个用于mysqli_stmt_prepare(调用)的对象
- mysqli_stmt_errno() - Returns the error code for the most recent statement call
- mysqli_stmt_error() - Returns a string description for last statement error
用户评论:
[#1] kaspy at example dot com [2015-03-05 18:31:32]
For those of you trying to bind rows into array,
<?php
$stmt = $db->prepare('SELECT id, name, mail, phone, FROM contacts');
$stmt->execute();
$stmt->store_result();
$stmt->bind_result($arr['id'], $arr['name'], $arr['mail'], $arr['phone']);
while ($stmt->fetch()) {
$outArr[] = $arr;
}
$stmt->close();
return $outArr;
?>
this will give you all the rows you asked for except that they would all be the same as the first one because of some gremlins in the background code (i've heard that PHP is trying to save memory here).
But this one works:
<?php
$stmt = $db->prepare('SELECT id, name, mail, phone, FROM contacts');
$stmt->execute();
$stmt->store_result();
$stmt->bind_result($a,$b,$c,$d);
while ($stmt->fetch()) {
$outArr[] = ['id' => $a, 'name' => $b, 'mail' => $c, 'phone' => $d];
}
$stmt->close();
return $outArr;
?>
Just don't use arrays to bind results :)
[#2] Masterkitano [2015-02-20 03:53:19]
for people who doesn't have the mysqlInd driver or for some reason just can't use the stmt->get_result, I've made this function which allows you to "mimic" the mysqli_result::fetch_assoc:
function fetchAssocStatement($stmt)
{
if($stmt->num_rows>0)
{
$result = array();
$md = $stmt->result_metadata();
$params = array();
while($field = $md->fetch_field()) {
$params[] = &$result[$field->name];
}
call_user_func_array(array($stmt, 'bind_result'), $params);
$stmt->fetch();
return $result;
}
return null;
}
you can use it in a while sentence to fetch and return an assoc array from the statement (as long as the statement is open):
usage:
$statement = $mysqli->prepare($query));
$statement.execute();
while($rowAssocArray = fetchAssocStatement($statement))
{
//do something
}
$statement.close();
hope this helps.
[#3] timchampion dot NOSPAM at gmail dot com [2012-02-20 19:29:23]
Just wanted to make sure that all were aware of get_result for those needing the result in array format.
In the code sample, after execute(), perform a get_result() like this:
<?php
// ... this document's example code:
$stmt->execute();
$result = $stmt->get_result();
while ($myrow = $result->fetch_assoc()) {
printf("%s %s\n", $myrow['Code'], $myrow['Name']);
}
?>
This is much nicer when you have a dozen or more fields coming back from your query. Hope this helps. Also, as noted in the comments for get_result, it requires mysqlnd.
[#4] dev+php at alepe dot com [2011-07-25 22:31:33]
According to the above documentation:
"Depending on column types bound variables can silently change to the corresponding PHP type. "
if you specify a field as int (tinyint, mediumint, etc.) with zerofill property, it will be converted (silently) to PHP integer (erasing the leading zeros). In order to keep those leading zeros, one solution is to specify the field as decimal.
Note that this only happens when using prepared statements and not when executing the query directly.
[#5] scragar at gmail dot com [2011-06-16 00:02:53]
To clarify for anyone having problems with arrays, PHP will automatically pass arrays as references, cloning the array if needed in the event of setting or unsetting a part of it, changing a referenced variable does not trigger cloning.
This is done for efficiency, to clone an array containing this information you may either use a foreach loop, or set/unset a key. Techniques like array_values will also work provided you don't mind losing your keys.
[#6] nieprzeklinaj at gmail dot com [2011-02-01 01:02:02]
I wrote a function that fetches all rows from a result set - either normal or prepared.
<?php
function fetch($result)
{
$array = array();
if($result instanceof mysqli_stmt)
{
$result->store_result();
$variables = array();
$data = array();
$meta = $result->result_metadata();
while($field = $meta->fetch_field())
$variables[] = &$data[$field->name]; // pass by reference
call_user_func_array(array($result, 'bind_result'), $variables);
$i=0;
while($result->fetch())
{
$array[$i] = array();
foreach($data as $k=>$v)
$array[$i][$k] = $v;
$i++;
// don't know why, but when I tried $array[] = $data, I got the same one result in all rows
}
}
elseif($result instanceof mysqli_result)
{
while($row = $result->fetch_assoc())
$array[] = $row;
}
return $array;
}
?>
Simply call it passing a result set or executed statement and you'll get all rows fetched.
[#7] quano [2010-12-23 04:19:26]
If I have a longtext field in the result, the whole page will go blank, without giving me any errors what so ever. This is because PHP _crashes_. I've spent an entire morning figuring this out.
Apparently, if you have longtext present, you HAVE to call store_result before using bind_result.
http://bugs.php.net/bug.php?id=47928
[#8] pcc at pccglobal dot com [2010-07-31 19:06:34]
If done properly, 'call_user_func_array()' works to bind variables to a result of multiple columns including BLOB columns.
EXAMPLE:
<?php
$data = array() ; // Array that accepts the data.
$params = array() ; // Parameter array passed to 'bind_result()'
$column = array("fidentity", "fvarchar", "fdate", "ftinyblob") ; // The column names.
foreach($column as $col_name)
{
// 'fetch()' will assign fetched value to the variable '$data[$col_name]'
$params[] =& $data[$col_name] ;
}
$res = call_user_func_array(array($stmt, "bind_result"), $params) ;
?>
Here's the complete example.
WARNING: When using 'prepare' to prepare a statement to retrieve LOBs the method order matters.
Also, method 'store_result()' must be called and be called in correct order.
Failure to observe this causes PHP/MySQLi to crash or return an erroneous value.
The proper procedure order is: prepare -> execute -> store_result -> bind -> fetch
<?php
$database = "test" ;
$table = "test" ;
$column = array("fidentity", "fvarchar", "fdate", "ftinyblob") ;
$select_set = "`fidentity`, `fvarchar`, `fdate`, `ftinyblob`" ;
$mysqli = new mysqli("localhost", "root", $password, $database);
// Proper procedure order: prepare -> execute -> store_result -> bind -> fetch
$stmt = $mysqli->prepare("SELECT $select_set FROM `$table`") ;
$stmt->execute();
$stmt->store_result();
$data = array() ; // Array that accepts the data.
$params = array() ; // Parameter array passed to 'bind_result()'
foreach($column as $col_name)
{
// Assign the fetched value to the variable '$data[$name]'
$params[] =& $data[$col_name] ;
}
$res = call_user_func_array(array($stmt, "bind_result"), $params) ;
if(! $res)
{
echo "bind_result() failed: " . $mysqli->error . "\n" ;
}
else
{
$res = $stmt->fetch() ;
if($res)
{
echo "<pre>" . htmlentities(print_r($data, true)) . "</pre>\n" ;
}
else
{
echo ((false !== $res) ? "End of data" : $stmt->error) . "\n" ;
}
}
$stmt->close() ;
$mysqli->close() ;
exit ;
?>
The above example should output:
Array (
[fidentity] => 24
[fvarchar] => the rain in spain
[fdate] => 2010-07-31
[ftinyblob] => GIF89a...(more BLOB data)
)
[#9] swissbob2 at yahoo dot co dot uk [2010-06-28 04:08:03]
I discovered this one by accident; mysqli_stmt_bind_result overwrites an *existing* session variable of the same name (but doesn't create them).
In other words,
mysqli_stmt_bind_result($stmt, $col1, $col2);
will overwrite session variables $_SESSION['col1'] and $_SESSION['col2'] if they exist, but will not create them if they don't.
[#10] uramihsayibok, gmail, com [2009-07-27 13:35:44]
A note to people to want to return an array of results - that is, an array of all the results from the query, not just one at a time.
<?php
// blah blah...
call_user_func_array(array($mysqli_stmt_object, "bind_result"), $byref_array_for_fields);
$results = array();
while ($mysqli_stmt_object->fetch()) {
$results[] = $byref_array_for_fields;
}
?>
This will NOT work. $results will have a bunch of arrays, but each one will have a reference to $byref.
PHP is optimizing performance here: you aren't so much copying the $byref array into $results as you are *adding* it. That means $results will have a bunch of $byrefs - the same array repeated multiple times. (So what you see is that $results is all duplicates of the last item from the query.)
hamidhossain (01-Sep-2008) shows how to get around that: inside the loop that fetches results you also have to loop through the list of fields, copying them as you go. In effect, copying everything individually.
Personally, I'd rather use some kind of function that effectively duplicates an array than write my own code. Many of the built-in array functions don't work, apparently using references rather than copies, but a combination of array_map and create_function does.
<?php
// blah blah...
call_user_func_array(array($mysqli_stmt_object, "bind_result"), $byref_array_for_fields);
// returns a copy of a value
$copy = create_function('$a', 'return $a;');
$results = array();
while ($mysqli_stmt_object->fetch()) {
// array_map will preserve keys when done here and this way
$results[] = array_map($copy, $byref_array_for_fields);
}
?>
All these problems would go away if they just implemented a fetch_assoc or even fetch_array for prepared statements...
[#11] Miguel Hatrick [2009-07-17 09:18:22]
Took some cool code from here and made a little class for those object oriented kind of guys
used like this:
<?php
// execute prepared statement
$stmt->execute();
$stmt->store_result();
//custom class :D bind to Statement Result mambo jambo!
$sr = new Statement_Result($stmt);
$stmt->fetch();
printf("ID: %d\n", $sr->Get('id') );
/////////////////////////////////
class Statement_Result
{
private $_bindVarsArray = array();
private $_results = array();
public function __construct(&$stmt)
{
$meta = $stmt->result_metadata();
while ($columnName = $meta->fetch_field())
$this->_bindVarsArray[] = &$this->_results[$columnName->name];
call_user_func_array(array($stmt, 'bind_result'), $this->_bindVarsArray);
$meta->close();
}
public function Get_Array()
{
return $this->_results;
}
public function Get($column_name)
{
return $this->_results[$column_name];
}
}
?>
[#12] atulkashyap1 at hotmail dot com [2009-04-25 03:31:24]
bind_ result can also be used to return an array of variables from a function,
This took me a long time to figure out, so I would like to share this.
<?php
function extracting(){
$query="SELECT topic, detail, date, tags
FROM updates
ORDER BY date DESC
LIMIT 5 ";
if($stmt = $this->conn->prepare($query)) {
$stmt->execute();
$stmt->bind_result($updates[0],$updates[1],$updates[2],$updates[3]);
$i=0;
while($stmt->fetch()){
$i++;
$name='t'.$i;
$$name = array($updates[0],$updates[1],$updates[2],$updates[3]);
}
return array($t1,$t2,$t3,$t4,$t5,);
$stmt->close();
}
}
?>
[#13] hamidhossain at gmail dot com [2008-09-01 07:07:39]
lot of people don't like how bind_result works with prepared statements! it requires you to pass long list of parameters which will be loaded with column value when the function being called.
To solve this, i used call_user_func_array function and result_metadata functions. which make easy and automatically returns an array of all columns results stored in an array with column names.
please don't forget to change setting variables with your own credentials:
<?php
$host = 'localhost';
$user = 'root';
$pass = '1234';
$data = 'test';
$mysqli = new mysqli($host, $user, $pass, $data);
if (mysqli_connect_errno()) {
printf("Connect failed: %s\n", mysqli_connect_error());
exit();
}
if ($stmt = $mysqli->prepare("SELECT * FROM sample WHERE t2 LIKE ?")) {
$tt2 = '%';
$stmt->bind_param("s", $tt2);
$stmt->execute();
$meta = $stmt->result_metadata();
while ($field = $meta->fetch_field())
{
$params[] = &$row[$field->name];
}
call_user_func_array(array($stmt, 'bind_result'), $params);
while ($stmt->fetch()) {
foreach($row as $key => $val)
{
$c[$key] = $val;
}
$result[] = $c;
}
$stmt->close();
}
$mysqli->close();
print_r($result);
?>
[#14] bb at servertje dot nl [2008-02-23 15:24:41]
Although inspired by an earlier post, this method could be added to any of your database objects. It's an object oriented implementation of an earlier post.
The method returns an array with objects representing a row. Each property represents a column and its value.
<?php
private function getresult($stmt)
{
$result = array();
$metadata = $stmt->result_metadata();
$fields = $metadata->fetch_fields();
for (;;)
{
$pointers = array();
$row = new stdClass();
$pointers[] = $stmt;
foreach ($fields as $field)
{
$fieldname = $field->name;
$pointers[] = &$row->$fieldname;
}
call_user_func_array(mysqli_stmt_bind_result, $pointers);
if (!$stmt->fetch())
break;
$result[] = $row;
}
$metadata->free();
return $result;
}
?>
[#15] thejkwhosaysni at gmail dot com [2005-10-19 13:12:06]
I've created these functions which will act like mysqli_fetch_array() and mysqli_fetch_object() but work with bound results.
<?php
function fetch_object() {
$data = mysqli_stmt_result_metadata($this->stmt);
$count = 1; //start the count from 1. First value has to be a reference to stmt.
$fieldnames[0] = &$this->stmt;
$obj = new stdClass;
while ($field = mysqli_fetch_field($data)) {
$fn = $field->name; //get all the feild names
$fieldnames[$count] = &$obj->$fn; //load the fieldnames into an object..
$count++;
}
call_user_func_array(mysqli_stmt_bind_result, $fieldnames);
mysqli_stmt_fetch($this->stmt);
return $obj;
}
function fetch_array() {
$data = mysqli_stmt_result_metadata($this->stmt);
$count = 1; //start the count from 1. First value has to be a reference to the stmt. because bind_param requires the link to $stmt as the first param.
$fieldnames[0] = &$this->stmt;
while ($field = mysqli_fetch_field($data)) {
$fieldnames[$count] = &$array[$field->name]; //load the fieldnames into an array.
$count++;
}
call_user_func_array(mysqli_stmt_bind_result, $fieldnames);
mysqli_stmt_fetch($this->stmt);
return $array;
}
?>
Hope this helps some people, I was puzzled by this for a while.
[#16] andrey at php dot net [2005-10-07 05:38:42]
If you select LOBs use the following order of execution or you risk mysqli allocating more memory that actually used
1)prepare()
2)execute()
3)store_result()
4)bind_result()
If you skip 3) or exchange 3) and 4) then mysqli will allocate memory for the maximal length of the column which is 255 for tinyblob, 64k for blob(still ok), 16MByte for MEDIUMBLOB - quite a lot and 4G for LONGBLOB (good if you have so much memory). Queries which use this order a bit slower when there is a LOB but this is the price of not having memory exhaustion in seconds.
[#17] brad dot jackson at resiideo dot com [2005-03-22 09:10:39]
A potential problem exists in binding result parameters from a prepared statement which reference large datatypes like mediumblobs. One of our database tables contains a table of binary image data. Our largest image in this table is around 50Kb, but even so the column is typed as a mediumblob to allow for files larger than 64Kb. I spent a frustrating hour trying to figure out why mysqli_stmt_bind_result choked while trying to allocate 16MB of memory for what should have been at most a 50Kb result, until I realized the function is checking the column type first to find out how big a result _might_ be retrieved, and attempting to allocate that much memory to contain it. My solution was to use a more basic mysqli_result() query. Another option might have been to retype the image data column as blob (64Kb limit).