mysqli_stmt::bind_param
mysqli_stmt_bind_param
(PHP 5)
mysqli_stmt::bind_param -- mysqli_stmt_bind_param — Привязка переменных к параметрам подготавливаемого запроса
Описание
Объектно-ориентированный стиль
Процедурный стиль
Привязывает переменные к меткам параметров в 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() - Возвращает строку с пояснением последней ошибки при выполнении запроса
- PHP Руководство
- Функции по категориям
- Индекс функций
- Справочник функций
- Расширения для работы с базами данных
- Расширения для работы с базами данных отдельных производителей
- MySQL Drivers and Plugins
- Улучшенный модуль MySQL
- mysqli_stmt::$affected_rows
- Функция mysqli_stmt::attr_get() - Получает текущее значение атрибута запроса
- Функция mysqli_stmt::attr_set() - Изменяет поведение подготовленного запроса
- Функция mysqli_stmt::bind_param() - Привязка переменных к параметрам подготавливаемого запроса
- Функция mysqli_stmt::bind_result() - Привязка переменных к подготавленному запросу для размещения результата
- Функция mysqli_stmt::close() - Закрывает подготовленный запрос
- mysqli_stmt::__construct
- Функция mysqli_stmt::data_seek() - Переход к заданной строке в результирующем наборе
- Функция mysqli_stmt::$errno() - Возвращает код ошибки выполнения последнего запроса
- Функция mysqli_stmt::$error_list() - Возвращает список ошибок выполнения последнего запроса
- Функция mysqli_stmt::$error() - Возвращает строку с пояснением последней ошибки при выполнении запроса
- Функция mysqli_stmt::execute() - Выполняет подготовленный запрос
- Функция mysqli_stmt::fetch() - Связывает результаты подготовленного выражения с переменными
- Функция mysqli_stmt::$field_count() - Возвращает число полей в заданном выражении
- Функция mysqli_stmt::free_result() - Освобождает память от результата запроса, указанного дескриптором
- Функция mysqli_stmt::get_result() - Получает результат из подготовленного запроса
- Функция mysqli_stmt::get_warnings() - Получает результат от SHOW WARNINGS
- Функция mysqli_stmt::$insert_id() - Получает ID сгенерированный предыдущей операцией INSERT
- Функция mysqli_stmt::more_results() - Проверяет, есть ли еще наборы строк в результате мультизапроса
- Функция mysqli_stmt::next_result() - Читает следующий набор строк из мультизапроса
- Функция mysqli_stmt::$num_rows() - Возвращает число строк в результате запроса
- Функция mysqli_stmt::$param_count() - Возвращает количество параметров в запросе
- Функция mysqli_stmt::prepare() - Подготовка SQL запроса к выполнению
- Функция mysqli_stmt::reset() - Сбрасывает результаты выполнения подготовленного запроса
- Функция mysqli_stmt::result_metadata() - Возвращает метаданные результирующей таблицы подготавливаемого запроса
- Функция mysqli_stmt::send_long_data() - Отправка данных блоками
- mysqli_stmt::$sqlstate
- Функция mysqli_stmt::store_result() - Передает результирующий набор запроса на клиента
Коментарии
Columns with type bigint need to be specified as type 'd' NOT 'i'.
Using 'i' results in large numbers (eg 3000169151) being truncated.
--
flame
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.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.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 ) ;
?>
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'
?>
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);
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.
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();
}
?>
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
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.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.
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.
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...
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 :)
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 propertyA 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.
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;
}
?>
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 values
this will not work:
$stmt -> bind_param("s", "value");
you have to do it like this :
$var = "value";
$stmt -> bind_param("s", $var);