mysqli_stmt::bind_param
mysqli_stmt_bind_param
(PHP 5, PHP 7, PHP 8)
mysqli_stmt::bind_param -- mysqli_stmt_bind_param — プリペアドステートメントのパラメータに変数をバインドする
説明
オブジェクト指向型
手続き型
mysqli_prepare() や mysqli_stmt_prepare() で準備された、 SQLステートメントのパラメータマーカに変数をバインドします。
注意:
データのサイズがパケットサイズの最大値（max_allowed_packet）を こえた場合、
typesにbを 指定して 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() が返す mysqli_stmt オブジェクト。
types-
ひとつあるいは複数の文字で、対応するバインド変数の型を表します。
型指定文字 文字 説明 i 対応する変数の型は int です。 d 対応する変数の型は float です。 s 対応する変数の型は string です。 b 対応する変数の型は blob で、複数のパケットに分割して送信されます。 varvars-
変数の数。文字列
typesの長さは、ステートメント中のパラメータの数と一致する必要があります。
エラー / 例外
mysqli のエラー報告 (MYSQLI_REPORT_ERROR) が有効になっており、かつ要求された操作が失敗した場合は、警告が発生します。さらに、エラー報告のモードが MYSQLI_REPORT_STRICT に設定されていた場合は、mysqli_sql_exception が代わりにスローされます。
例
例1 mysqli_stmt::bind_param() の例
オブジェクト指向型
<?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);
/* 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));
/* 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 引数を渡すために ... を使う
可変長の引数を渡すために、
... 演算子を使うことができます。
たとえば WHERE IN 条件で使えます。
<?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() - 結果を保存するため、プリペアドステートメントに変数をバインドする
- mysqli_stmt_execute() - プリペアドステートメントを実行する
- mysqli_stmt_fetch() - プリペアドステートメントから結果を取得し、バインド変数に格納する
- mysqli_prepare() - 実行するための SQL文 を準備する
- mysqli_stmt_send_long_data() - データをブロックで送信する
- mysqli_stmt_errno() - 直近のステートメントのコールに関するエラーコードを返す
- mysqli_stmt_error() - 直近のステートメントのエラー内容を文字列で返す
＋add a note
User Contributed Notes 19 notes
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();
}
?>
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'
?>
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;
}
?>
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...
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);
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.
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 ) ;
?>
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.
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.
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.
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
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
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);
?>
Matze ¶
7 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 :)
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);
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.
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.
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