mysqli_stmt::bind_param

mysqli_stmt_bind_param

(PHP 5, PHP 7, PHP 8)

mysqli_stmt::bind_param -- mysqli_stmt_bind_param — Binds variables to a prepared statement as parameters

Опис

Об'єктно-орієнтований стиль

public mysqli_stmt::bind_param(string $types, mixed &$var, mixed &...$vars): bool

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

mysqli_stmt_bind_param(
    mysqli_stmt $statement,
    string $types,
    mixed &$var,
    mixed &...$vars
): bool

Bind variables for the parameter markers in the SQL statement prepared by mysqli_prepare() or mysqli_stmt_prepare().

Зауваження:
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.

Зауваження:
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.

Параметри

statement

Тільки процедурний стиль: об'єкт mysqli_stmt, якого повертає функція mysqli_stmt_init().

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 int
d	corresponding variable has type float
s	corresponding variable has type string
b	corresponding variable is a blob and will be sent in packets

var

vars

The number of variables and length of string types must match the parameters in the statement.

Значення, що повертаються

Повертає true у разі успіху або false в разі помилки.

Помилки/виключення

Якщо увімкнені звіти про помилки mysqli (MYSQLI_REPORT_ERROR) і запитана операція не виконалась, буде виведено попередження, але якщо встановлено режим MYSQLI_REPORT_STRICT, буде викинуто виключення mysqli_sql_exception.

Приклади

Приклад #1 mysqli_stmt::bind_param() example

Об'єктно-орієнтований стиль

<?php

mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);
$mysqli = new mysqli('localhost', 'my_user', 'my_password', 'world');

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

/* Clean up table CountryLanguage */
$mysqli->query("DELETE FROM CountryLanguage WHERE Language='Bavarian'");
printf("%d row deleted.\n", $mysqli->affected_rows);

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

<?php

mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);
$link = mysqli_connect('localhost', 'my_user', 'my_password', 'world');

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

/* Clean up table CountryLanguage */
mysqli_query($link, "DELETE FROM CountryLanguage WHERE Language='Bavarian'");
printf("%d row deleted.\n", mysqli_affected_rows($link));

Подані вище приклади виведуть:

1 row inserted.
1 row deleted.

Приклад #2 Using ... to provide arguments

The ... operator can be used to provide variable-length argument list, e.g. in a WHERE IN clause.

<?php

mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);
$mysqli = new mysqli('localhost', 'my_user', 'my_password', 'world');

$stmt = $mysqli->prepare("SELECT Language FROM CountryLanguage WHERE CountryCode IN (?, ?)");
/* Using ... to provide arguments */
$stmt->bind_param('ss', ...['DEU', 'POL']);
$stmt->execute();
$stmt->store_result();

printf("%d rows found.\n", $stmt->num_rows());

Подані вище приклади виведуть:

10 rows found.

Прогляньте також

mysqli_stmt_bind_result() - Binds variables to a prepared statement for result storage
mysqli_stmt_execute() - Executes a prepared statement
mysqli_stmt_fetch() - Fetch results from a prepared statement into the bound variables
mysqli_prepare() - Prepares 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

Improve This Page

Learn How To Improve This Page • Submit a Pull Request • Report a Bug

＋add a note

User Contributed Notes 19 notes

down

jk at jankriedner dot de ¶

13 years ago

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

}

?>

down

Anonymous ¶

13 years ago

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'

?>

down

davidharrison at gmail dot com ¶

7 years ago

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

down

travis at twooutrally dot com ¶

8 years ago

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

down

Anonymous ¶

13 years ago

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

down

tomasz at marcinkowski dot pl ¶

10 years ago

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.

down

eisoft ¶

14 years ago

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

?>

down

asb(.d o,t )han(a t)n i h e i(d.o_t)dk ¶

13 years ago

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.

down

rejohns at nOsPaMpost dot harvard dot edu ¶

14 years ago

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.

down

xianrenb at gmail dot com ¶

12 years ago

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.

down

flame ¶

17 years ago

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



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



--

flame

down

accountant ¶

7 years ago

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

down

andersmmg at gmail dot com ¶

5 years ago

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

down

Matze ¶

8 years ago

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

down

c at zp1 dot net ¶

3 years ago

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

down

Darky ¶

7 years ago

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.

down

laurence dot mackenzie at stream dot com ¶

11 years ago

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.

down

-2

alex dot deleyn at gmail dot com ¶

13 years ago

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

down

-2

Anonymous ¶

16 years ago

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.

＋add a note