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();
}
?>

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'?>

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. <?phppublic 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...

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: <?phpfunction 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;}?>

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 ) ;
?>

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);

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.

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:oneGood results:onetwothreefourUsing "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.

Columns with type bigint need to be specified as type 'd' NOT 'i'.

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

--
flame

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.

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.

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

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);?>

It is very important to understand that you can not supply bind_param valuesthis will not work:    $stmt -> bind_param("s", "value"); you have to do it like this :    $var =  "value";    $stmt -> bind_param("s", $var);

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 :)

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.

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.

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-toIf 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

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.