Diese Seite bei php.net
The PHP manual text and comments are covered by the Creative Commons Attribution 3.0 License © the PHP Documentation Group - Impressum - mail("TO:Reinhard Neidl",...)
(PHP 5)
mysqli_stmt::bind_param -- mysqli_stmt_bind_param — Binds variables to a prepared statement as parameters
Objektorientierter Stil
Prozeduraler Stil
Bind variables for the parameter markers in the SQL statement that was passed to mysqli_prepare().
Hinweis:
If data size of a variable exceeds max. allowed packet size (max_allowed_packet), you have to specify b in types and use mysqli_stmt_send_long_data() to send the data in packets.
Hinweis:
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.
Nur bei prozeduralem Aufruf: ein von mysqli_stmt_init() zurückgegebenes Statementobjekt.
A string that contains one or more characters which specify the types for the corresponding bind variables:
| 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 |
The number of variables and length of string types must match the parameters in the statement.
Gibt bei Erfolg TRUE zurück. Im Fehlerfall wird FALSE zurückgegeben.
Beispiel #1 Objektorientierter Stil
<?php
$mysqli = new mysqli('localhost', 'my_user', 'my_password', 'world');
/* check connection */
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;
/* execute prepared statement */
$stmt->execute();
printf("%d Row inserted.\n", $stmt->affected_rows);
/* close statement and connection */
$stmt->close();
/* Clean up table CountryLanguage */
$mysqli->query("DELETE FROM CountryLanguage WHERE Language='Bavarian'");
printf("%d Row deleted.\n", $mysqli->affected_rows);
/* close connection */
$mysqli->close();
?>
Beispiel #2 Prozeduraler Stil
<?php
$link = mysqli_connect('localhost', 'my_user', 'my_password', 'world');
/* check connection */
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;
/* execute prepared statement */
mysqli_stmt_execute($stmt);
printf("%d Row inserted.\n", mysqli_stmt_affected_rows($stmt));
/* close statement and connection */
mysqli_stmt_close($stmt);
/* Clean up table CountryLanguage */
mysqli_query($link, "DELETE FROM CountryLanguage WHERE Language='Bavarian'");
printf("%d Row deleted.\n", mysqli_affected_rows($link));
/* close connection */
mysqli_close($link);
?>
The above examples will output:
1 Row inserted. 1 Row deleted.
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
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;
}
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)
{
/*** assign the last insert id ***/
$last_id = $mysqli->insert_id;
echo "OK$last_id";
}
else {
echo $mysqli->error;
}
?>
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;
}
?>
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 ) ;
?>
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.
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.
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');
/* execute prepared statement */
$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';
}
?>
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.
<?php
/* just explaining how to call mysqli_stmt_bind_param with a parameter array */
$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);
?>
///////////////////////////////
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);
/*
Array
(
[0] => Array
(
[id] => 1
[username] => mahees
[password] => mahees
)
)
*/
$userPassArr = DataAccess::fetch('SELECT * FROM users');
print_r($userPassArr);
/*
Array
(
[0] => Array
(
[id] => 1
[username] => mahees
[password] => mahees
)
[1] => Array
(
[id] => 4
[username] => foo
[password] => bar
)
[2] => Array
(
[id] => 5
[username] => bar
[password] => baz
)
)
*/
//********* function in DataAccess class *********
//im sure this can be written better with more checks...but principle stands
static function fetch() {
$args = func_get_args();
$sql = array_shift($args);
$link = self::establish_db_conn();
if (!$stmt = mysqli_prepare($link, $sql)) {
self::close_db_conn();
die('Please check your sql statement : unable to prepare');
}
$types = str_repeat('s', count($args));
array_unshift($args, $types);
array_unshift($args, $stmt);
call_user_func_array('mysqli_stmt_bind_param', $args);
mysqli_stmt_execute($stmt);
$result = mysqli_stmt_result_metadata($stmt);
$fields = array();
while ($field = mysqli_fetch_field($result)) {
$name = $field->name;
$fields[$name] = &$$name;
}
array_unshift($fields, $stmt);
call_user_func_array('mysqli_stmt_bind_result', $fields);
array_shift($fields);
$results = array();
while (mysqli_stmt_fetch($stmt)) {
$temp = array();
foreach($fields as $key => $val) { $temp[$key] = $val; }
array_push($results, $temp);
}
mysqli_free_result($result);
mysqli_stmt_close($stmt);
self::close_db_conn();
return $results;
}
?>
I wanted to pass the parameters for several queries to a single function to fill them (insert / update having the same fields for example), while at the same time making the types array a bit easier to maintain when you've got a lot of parameters. Here's a simple solution I came up with:
<?php
function bindParameters(&$statement, &$params)
{
$args = array();
$args[] = implode('', array_values($params));
foreach ($params as $paramName => $paramType)
{
$args[] = &$params[$paramName];
$params[$paramName] = null;
}
call_user_func_array(array(&$statement, 'bind_param'), $args);
}
// Usage:
$statement = $database->prepare('INSERT INTO test (value1, value2) VALUES (?, ?)');
$params = array('param1' => 's',
'param2' => 'i');
bindParameters($statement, $params);
$params['param1'] = 'parameter test';
$params['param2'] = 42;
$statement->execute();
?>
Note that the types will be overwritten after a call to bindParameters to provide a sensible default (otherwise it will be used as the parameter value when you execute the statement), so you need to reinitialize the types if you want to bind it to another statement.
I already have a database class that makes everything nice and easy. But when it came to preparing, binding and executing, I found it was a real challenge to boil things down.
But luckily I stumbled over a bug-report with a workaround, that pointed me in the right direction.: http://bugs.php.net/bug.php?id=43568
I now execute stored procedures (aka routines) like this:
<?php
$db = new myDb();
$db->execProcedure('call someProc(?,?)','ss',array('param1','param2'));
?>
And this is the code to make it happen:
(I extracted this example from a bigger context, but you probably get the idea)
<?php
class myDb extends mysqli {
public function __construct() {
//Connection established here
}
public function execProcedure($call,$types,$params) {
$stmt = $this->prepare($call);
$bind_names[] = $types;
for ($i=0; $i<count($params);$i++) {
$bind_name = 'bind' . $i;
$$bind_name = $params[$i];
$bind_names[] = &$$bind_name;
}
$return = call_user_func_array(array($stmt,'bind_param'),$bind_names);
$stmt->execute();
$stmt->close();
}
}
Small correction. This version removes the NULL element from the array, so it doesn't fall on to the next ? when passed to mysql_stmt_bind_param(). Note that $saParams is still passed by reference, but now it is being modified.
<?php
function preparse_prepared($sQuery, &$saParams)
{
$nPos = 0;
$sRetval = $sQuery;
foreach ($saParams as $x_Key => $Param)
{
//if we find no more ?'s we're done then
if (($nPos = strpos($sQuery, '?', $nPos + 1)) === false)
{
break;
}
//this test must be done second, because we need to increment offsets of $nPos for each ?.
//we have no need to parse anything that isn't NULL.
if (!is_null($Param))
{
continue;
}
//null value, replace this ? with NULL.
$sRetval = substr_replace($sRetval, 'NULL', $nPos, 1);
//unset this element now
unset($saParams[$x_Key]);
}
return $sRetval;
}
?>
I've found that you can't pass NULL values in using mysql_stmt_bind_param. Recently I ran into this problem because I wrote some MySQL routines that would update existing data, but only when the value wasn't NULL.
My solution to work around this is simple:
<?php
function preparse_prepared($sQuery, &$saParams)
{
$nPos = 0;
$sRetval = $sQuery;
foreach ($saParams as $Param)
{
//if we find no more ?'s we're done then
if (($nPos = strpos($sQuery, '?', $nPos + 1)) === false)
{
break;
}
//this test must be done second, because we need to increment offsets of $nPos for each ?.
//we have no need to parse anything that isn't NULL.
if (!is_null($Param))
{
continue;
}
//null value, replace this ? with NULL.
$sRetval = substr_replace($sRetval, 'NULL', $nPos, 1);
}
return $sRetval;
}
?>
This will iterate the given list of parameters and replace any null values in the query with an actual null value. You'll want to use the resulting $sQuery to pass to mysqli_prepare(). For that, I use another routine that generates a list of the values (s, i, etc).
For example:
<?php
array_unshift($saParams, $this->getPreparedTypeString($saParams));
array_unshift($saParams, $stmt);
call_user_func_array('mysqli_stmt_bind_param', $saParams);
?>
Where getPreparedTypeString is defined as:
<?php
public static function getPreparedTypeString(&$saParams)
{
$sRetval = '';
//if not an array, or empty.. return empty string
if (!is_array($saParams) || !count($saParams))
{
return $sRetval;
}
//iterate the elements and figure out what they are, and append to result
foreach ($saParams as $Param)
{
if (is_int($Param))
{
$sRetval .= 'i';
}
else if (is_double($Param))
{
$sRetval .= 'd';
}
else if (is_string($Param))
{
$sRetval .= 's';
}
}
return $sRetval;
}
?>
To clarify why I pass array values by reference: They aren't being modified, so I don't want copies of them begin made in memory as they may be large. In other languages, this is much more efficient. Not sure if PHP handles passing values on a "copy on edit" basis.. but I'm guessing not.
It's worth noting that you have to bind all parameters in one fell swoop - you can't go through and call bind_param once for each.
Some examples in the documentation suggest that you can call $stmt->bind_param() once, then call $stmt->execute() several times while altering the bound variables each time, so as to e.g. insert several records into a data base. This is not true.
You need to call $stmt->bind_param() once each time AFTER you altered the set of variables, and BEFORE you call $stmt->execute()
This may be a bug. If it is not, the documentation is flawed, and there is no gain to the programmer using the new mysqli interface at this point.
To continue on previous post
Bigints and the 'd' type:
If the digit you insert is longer then 16 digits the last digits will alter. I was noticing this in my inserts.
1111111111111111111 changes to 1111111111111111168
I had to switch to using 's' as type
Columns with type bigint need to be specified as type 'd' NOT 'i'.
Using 'i' results in large numbers (eg 3000169151) being truncated.
--
flame
Diese Seite bei php.net