mysqli_stmt::bind_param

mysqli_stmt_bind_param

(PHP 5)

mysqli_stmt::bind_param -- mysqli_stmt_bind_param Привязка переменных к параметрам подготавливаемого запроса

Описание

Объектно-ориентированный стиль

bool mysqli_stmt::bind_param ( string $types , mixed &$var1 [, mixed &$... ] )

Процедурный стиль

bool mysqli_stmt_bind_param ( mysqli_stmt $stmt , string $types , mixed &$var1 [, mixed &$... ] )

Привязывает переменные к меткам параметров в SQL выражении, которое было подготовлено фукнцией mysqli_prepare().

Замечание:

Если размер данных переменной превышает максимально допустимый размер пакета (max_allowed_packet), необходимо задать значение b параметру types и использовать функцию mysqli_stmt_send_long_data(), которая будет передавать данные пакетами.

Замечание:

При использовании mysqli_stmt_bind_param() совместно с call_user_func_array() необходимо соблюдать особую осторожность. Нужно принимать во внимание, что mysqli_stmt_bind_param() принимает в качестве параметров только ссылки на значения, в то время как call_user_func_array() принимает список параметров, которые могут передаваться как по ссылке, так и по значению.

Список параметров

stmt

Только для процедурного стиля: Идентификатор выражения, полученный с помощью mysqli_stmt_init().

types

Строка содержащая один или более символов, каждый из которых задает тип значения привязываемой переменной:

Символы задающие тип
Символ Описание
i соответствующая переменная имеет тип integer
d соответствующая переменная имеет тип double
s соответствующая переменная имеет тип string
b соответствующая переменная является большим двоичным объектом (blob) и будет пересылаться пакетами

var1

Количество переменных и длина строки types должны в точности соответствовать количеству параметров в запросе.

Возвращаемые значения

Возвращает TRUE в случае успешного завершения или FALSE в случае возникновения ошибки.

Примеры

Пример #1 Объектно-ориентированный стиль

<?php
$mysqli 
= new mysqli('localhost''my_user''my_password''world');

/* проверка подключения */
if (mysqli_connect_errno()) {
    
printf("Не удалось подключиться: %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 строк вставлено.\n"$stmt->affected_rows);

/* закрываем запрос */
$stmt->close();

/* очищаем таблицу CountryLanguage */
$mysqli->query("DELETE FROM CountryLanguage WHERE Language='Bavarian'");
printf("%d строк удалено.\n"$mysqli->affected_rows);

/* закрываем подключение */
$mysqli->close();
?>

Пример #2 Процедурный стиль

<?php
$link 
mysqli_connect('localhost''my_user''my_password''world');

/* проверка подключения */
if (!$link) {
    
printf("Не удалось подключиться: %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 строк вставлено.\n"mysqli_stmt_affected_rows($stmt));

/* закрываем запрос */
mysqli_stmt_close($stmt);

/* очищаем таблицу CountryLanguage */
mysqli_query($link"DELETE FROM CountryLanguage WHERE Language='Bavarian'");
printf("%d строк удалено.\n"mysqli_affected_rows($link));

/* закрываем подключение */
mysqli_close($link);
?>

Результат выполнения данных примеров:

1 строк вставлено.
1 Row deleted.

Смотрите также

  • mysqli_stmt_bind_result() - Привязка переменных к подготавленному запросу для размещения результата
  • mysqli_stmt_execute() - Выполняет подготовленный запрос
  • mysqli_stmt_fetch() - Связывает результаты подготовленного выражения с переменными
  • mysqli_prepare() - Подготавливает SQL выражение к выполнению
  • mysqli_stmt_send_long_data() - Отправка данных блоками
  • mysqli_stmt_errno() - Возвращает код ошибки выполнения последнего запроса
  • mysqli_stmt_error() - Возвращает строку с пояснением последней ошибки при выполнении запроса

Коментарии

Автор:
Columns with type bigint need to be specified as type 'd' NOT 'i'.

Using 'i' results in large numbers (eg 3000169151) being truncated.

--
flame
2007-02-18 22:44:29
http://php5.kiev.ua/manual/ru/mysqli-stmt.bind-param.html
Автор:
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.
2007-12-15 12:24:50
http://php5.kiev.ua/manual/ru/mysqli-stmt.bind-param.html
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();
  }
}
2008-07-17 04:46:37
http://php5.kiev.ua/manual/ru/mysqli-stmt.bind-param.html
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.
2008-09-07 17:26:09
http://php5.kiev.ua/manual/ru/mysqli-stmt.bind-param.html
Автор:
///////////////////////////////

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;
    }
?>
2009-02-21 12:34:08
http://php5.kiev.ua/manual/ru/mysqli-stmt.bind-param.html
Автор:
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.
2009-03-12 17:26:58
http://php5.kiev.ua/manual/ru/mysqli-stmt.bind-param.html
Автор:
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';
}
?>
2009-07-17 11:14:52
http://php5.kiev.ua/manual/ru/mysqli-stmt.bind-param.html
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.
2010-02-10 22:24:36
http://php5.kiev.ua/manual/ru/mysqli-stmt.bind-param.html
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.
2010-03-15 11:14:42
http://php5.kiev.ua/manual/ru/mysqli-stmt.bind-param.html
Автор:
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 ) ;
?>
2010-03-18 17:42:16
http://php5.kiev.ua/manual/ru/mysqli-stmt.bind-param.html
Автор:
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_list0strlen($column_list) -1);
$value_list substr($value_list0strlen($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;               
}

?>
2010-09-08 06:30:16
http://php5.kiev.ua/manual/ru/mysqli-stmt.bind-param.html
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; 
}
2010-09-22 00:05:10
http://php5.kiev.ua/manual/ru/mysqli-stmt.bind-param.html
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
2010-11-12 06:43:02
http://php5.kiev.ua/manual/ru/mysqli-stmt.bind-param.html
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);
?>
2011-01-25 02:44:30
http://php5.kiev.ua/manual/ru/mysqli-stmt.bind-param.html
Автор:
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'

?>
2011-03-14 16:28:02
http://php5.kiev.ua/manual/ru/mysqli-stmt.bind-param.html
Автор:
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);
2011-03-28 16:49:51
http://php5.kiev.ua/manual/ru/mysqli-stmt.bind-param.html
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 `idAsLettersFROM `tblWHERE `idIN(?));
$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 `idAsLettersFROM `tblWHERE `idIN(?, ?, ?, ?));
$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.
2011-04-22 21:05:35
http://php5.kiev.ua/manual/ru/mysqli-stmt.bind-param.html
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...

/* 
    Example 1 (wont work as expected, creates two empty entries)
    Re-assigning the array in the while()-head generates a new array, whereas references from bind_param stick to the old array
*/
foreach( getData() as $arr ) {
   
$res->execute();
}

/*
    Example 2 (will work as expected)
    Re-assigning every single value explicitly keeps the references alive
*/
foreach( getData() as $tempArr ) {
    foreach(
$tempArr as $k=>$v) {
       
$arr[$k] = $v;
    }
   
$res->execute();
}
?>
2011-05-20 17:39:25
http://php5.kiev.ua/manual/ru/mysqli-stmt.bind-param.html
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
2011-07-16 21:50:37
http://php5.kiev.ua/manual/ru/mysqli-stmt.bind-param.html
Автор:
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_HOSTDB_USERNAMEDB_PASSWORDDB_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();
2011-11-06 13:50:23
http://php5.kiev.ua/manual/ru/mysqli-stmt.bind-param.html
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.
2011-12-30 17:50:56
http://php5.kiev.ua/manual/ru/mysqli-stmt.bind-param.html
Автор:
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()
  Executes prepared querys given query syntax, and bind parameters
  Returns data in array format

Arguments:
  mysqli_link
  mysqli_prepare query
  mysqli_stmt_bind_param argmuent list in the form array($typeDefinitinonString, $var1 [, mixed $... ])

Return values:
  When given SELECT, SHOW, DESCRIBE or EXPLAIN statements: returns table data in the form resultArray[row number][associated field name]
  Returns number of rows affacted when given other queries
  Returns FALSE on error                       
*/
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.
2012-01-11 06:00:21
http://php5.kiev.ua/manual/ru/mysqli-stmt.bind-param.html
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_HOSTDB_USERDB_PASSWORDDB_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>';

?>
2012-05-25 01:22:38
http://php5.kiev.ua/manual/ru/mysqli-stmt.bind-param.html
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.]
2012-06-30 13:12:03
http://php5.kiev.ua/manual/ru/mysqli-stmt.bind-param.html
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.
2012-09-30 09:52:17
http://php5.kiev.ua/manual/ru/mysqli-stmt.bind-param.html
Автор:
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();
?>
2012-10-15 22:43:54
http://php5.kiev.ua/manual/ru/mysqli-stmt.bind-param.html
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

/* Code that loops through the rows and columns in the 
 * flat file and appends the MySQLi 'type' letter to the
 * $typeString variable and appends the actual value
 * to the $data array.  I left the code out because it's 
 * (probably) not relevant and would bloat the post.
 */
$stmtInsert $db->prepare('INSERT.....');
$typeString 'ississis';
$data = array(1'two''three'4'five''six'7'eight');

/* Here's where the actual strangeness starts happening
 */

// 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.
2013-10-09 00:08:56
http://php5.kiev.ua/manual/ru/mysqli-stmt.bind-param.html
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.
2014-03-27 17:46:16
http://php5.kiev.ua/manual/ru/mysqli-stmt.bind-param.html
Автор:
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
2015-08-04 15:14:39
http://php5.kiev.ua/manual/ru/mysqli-stmt.bind-param.html
Parameter type matters more than you might think!

A cautionary tail to any who would seek to find less than thorough solutions for automating prepared statements. Take the following mysqli_stmt extension method for example. 

<?php

public function param_type($param)
{
    if (
ctype_digit((string) $param)
        return 
$param <= PHP_INT_MAX 'i' 's';

    if (
is_numeric($param))
        return 
'd';

    return 
's';
}

?>

At face value this seems like a perfectly straightforward and innocuous function. Something like this served as a small piece in a larger automation extension which dutifully served its purpose efficiently handling hundreds of thousands of queries a day.

Now I know what you're thinking: it doesn't handle blob types. Well we didn't work with blob types (and still don't) so that's never been an issue. This problem was far more insidious and ultimately pernicious than that.

So what went wrong? The problem began to surface when we started automating SELECT queries on a newly created index for a column designed to store telephone numbers. The column was of type VARCHAR but the data stored was always formatted as an integer. Not a problem when performing a write operation but as soon as we went to read from the table on this index everything went to hell.

We're not entirely sure, but as near as we can tell the act of binding a parameter to a VARCHAR index as 'i' instead of 's' on a read query is detrimental in the following way: MySQL will ignore the b-tree on the index and perform a full table scan. With smaller tables this may never present itself as a significant performance issue. When your tables get into the tens of millions of rows, however...
2016-03-06 07:59:52
http://php5.kiev.ua/manual/ru/mysqli-stmt.bind-param.html
Автор:
Hey Folks,

just wanted to mention that parameters can only be used for input data, NOT for Table, Columns or Database names.
That gave me a headache yesterday!
So this code will not work:

$searchtype = "Title";
$searchterm = "The Fellowship of the Ring: The Lord of the Rings";

$query = 
"SELECT ISBN, Author, Title, Price 
FROM books 
WHERE ? = ?";

$mySql_stmt = $db->prepare($query);
$mySql_stmt->bind_param("ss" , $searchtype, $searchterm);
$mySql_stmt->execute(); 

In contrast, you will have to include the searchtype in ther query directly like this:

$searchtype = "Title";
$searchterm = "The Fellowship of the Ring: The Lord of the Rings";
$query = 
"SELECT ISBN, Author, Title, Price 
FROM books 
WHERE $searchtype = ?";

$mySql_stmt = $db->prepare($query);
$mySql_stmt->bind_param("s", $searchterm);
$mySql_stmt->execute(); 

Hope that helps someone to have a peaceful nights sleep :)
2016-11-10 09:57:36
http://php5.kiev.ua/manual/ru/mysqli-stmt.bind-param.html
Автор:
if bind_param() fails due to Number of elements in type definition string doesn't match number of bind variables. it triggers an E_WARNING error. and you will not find that error in $stmt->error property
2017-01-16 12:09:18
http://php5.kiev.ua/manual/ru/mysqli-stmt.bind-param.html
Instead of reflection or complicated ref assigning do this:

<?
mysqli_stmt_bind_param
($stmt, ...$params);

//(Only works in php 5.6+)

//Example:

//$mysql is from mysqli_connect()
//$sql is the query
//$types is the type string eg 'ii'
//$params is an array of params
function execute($mysqli$sql$types$params) {
   
$stmt mysqli_prepare($mysqli$sql);
   
mysqli_stmt_bind_param($stmt$types, ...$params);
   
mysqli_stmt_execute($stmt);
    return 
mysqli_stmt_get_result($stmt);
}
?>

You can have the types as the first element of the array as in the first example, or as a separate variable as in the second example.
2017-03-22 06:09:35
http://php5.kiev.ua/manual/ru/mysqli-stmt.bind-param.html
Автор:
A small remark from what I tried:
- if you use prepared statements with bind_param and your query looks like
"SELECT user_id FROM users WHERE ... = ?" and then you bind an integer param to this, the user_ids you get will be casted to int. On the other hand, if you don't use prepared statements, but sth like "SELECT user_id FROM users WHERE ... = $var", where $var is an int, and just make the query, the fetched results will be strings. (e.g., at var_dump,  ["user_id"]=> string(1) "6" for some row)
This is just from what I observed in my project, hope it's correct.
2017-07-10 18:23:20
http://php5.kiev.ua/manual/ru/mysqli-stmt.bind-param.html
There are two solutions in this page for calling bind_param() via  call_user_func_array() that involve using a user-created function called refValues(), so that you can pass the parameters to bind_param() as references. 

This works perfectly in PHP v5.3 (and I assume before), but since upgrading to PHP v7.1.7, the refValues() functions here no longer correctly convert the arrays to arrays of references. Instead you will get a warning: 

"PHP Warning:  Parameter 3 to mysqli_stmt_bind_param() expected to be a reference, value given"

I believe this is because of changes to arrays and reference handling as noted in the "Migrating from PHP 5.6.x to PHP 7.0.x" guide in Backwards Incompatibilities (change: "foreach by-value operates on a copy of the array"). 

So in PHP v7.1.7 at least, the user-created function refValues() no longer returns an array of references but instead a normal array of values. 

Changing the function definition of refValues() to accept the array as a reference seems to fix this - as intended it returns an array of references and thus bind_param() works as expected (although I haven't tested this super thoroughly to make sure there are no other ill effects, especially in older versions of PHP). 

New refValues() definition is simply: 

<?php
function refValues(&$arr// Changed $arr to reference for PHP v7.1.7 
{
    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;
}
?>
2017-07-16 11:20:54
http://php5.kiev.ua/manual/ru/mysqli-stmt.bind-param.html
I sometimes forget that you can't put functions inside. For example:

If I wanted to use md5() on a value like so:
<?php
$stmt
->bind_param("s",md5($val));
?>
If would not work. Because it uses the variables by binding them, you need to change them beforehand like this:
<?php
$val 
md5($val);
$stmt->bind_param("s",$val);
?>
2019-01-29 09:29:29
http://php5.kiev.ua/manual/ru/mysqli-stmt.bind-param.html
Guido's class above is great ... but to avoid a warning for an unknown array key 0, you should replace:

$this->mbind_params = array(); 

with

$this->mbind_params = array("");

on two of the lines.
2021-04-09 17:01:46
http://php5.kiev.ua/manual/ru/mysqli-stmt.bind-param.html
just wanted to share here, how we can use prepare statement with the combination of ... operator effectively .
<?php
class Database{
 private function 
getTypeofValues($string$value){
        if(
is_float($value)){
           
$string .= "d";
        }elseif(
is_integer($value)){
           
$string .= "i";
        }elseif(
is_string($value)){
           
$string .= "s";
        }else{
           
$string .= "b";
        }
        return 
$string;
    }
 public function 
makeQuery($query, array $values){
       
$stmt $this->connection->prepare($query);
       
$type array_reduce($values, array($this"getTypeOfValues"));
       
$stmt->bind_param($type, ...$values);
       
$stmt->execute();
       
$result $stmt->get_result();
        return 
$result;
    }
}
?>
2021-05-12 06:44:35
http://php5.kiev.ua/manual/ru/mysqli-stmt.bind-param.html
Автор:
It is very important to understand that you can not supply bind_param values

this will not work:

    $stmt -> bind_param("s", "value");
 

you have to do it like this :

    $var =  "value";
    $stmt -> bind_param("s", $var);
2021-08-02 00:26:11
http://php5.kiev.ua/manual/ru/mysqli-stmt.bind-param.html
I wasn't able to find anything like this, I thought I would post it. If its garbage let me know and I will remove it!

//Usage:
//$query = prepare($db, "SELECT * FROM TABLE WHERE ID = %d AND NAME = %s", 1, 'foo');
//while ($row = $query->fetch_assoc())

// "Splat" ... operator requires PHP 5.6
function prepare($db, $sql, ...$params) {
    if (preg_match_all('/%(\w)/sim', $sql, $types)) {
        $sql = preg_replace('/%(\w)/sim', '?', $sql);
        if ($stmt = $db->prepare($sql)) {
            $stmt->bind_param(implode('', $types[1]), ...$params);
            $stmt->execute();
            $query = $stmt->get_result();
            $stmt->close();
            return $query;
        } else {
            echo 'Error: #' . $db->errno . ' ' . $db->error;
        }
    } else {
        return $db->query($sql);
    }
}
2022-01-03 05:29:42
http://php5.kiev.ua/manual/ru/mysqli-stmt.bind-param.html

    Поддержать сайт на родительском проекте КГБ