mysqli_stmt::attr_set
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';
}
?>
mysqli_stmt::bind_param
mysqli_stmt_bind_param
(PHP 5)
mysqli_stmt::bind_param -- mysqli_stmt_bind_param — プリペアドステートメントのパラメータに変数をバインドする
説明
オブジェクト指向型（メソッド）:
手続き型:
変数を、SQL ステートメントのパラメータマーカにバインドします。 この変数は、mysqli_prepare() に渡されたものです。
注意: データのサイズがパケットサイズの最大値（max_allowed_packet）を こえた場合、types に b を 指定して mysqli_stmt_send_long_data() を使用し、 データをパケットに分割して送信する必要があります。
パラメータ
- stmt
-
手続き型のみ: mysqli_stmt_init() が返すステートメント ID。
- 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("Connect failed: %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 Row inserted.\n", $stmt->affected_rows);
/* ステートメントと接続を閉じます */
$stmt->close();
/* CountryLanguage テーブルをクリアします */
$mysqli->query("DELETE FROM CountryLanguage WHERE Language='Bavarian'");
printf("%d Row deleted.\n", $mysqli->affected_rows);
/* 接続を閉じます */
$mysqli->close();
?>
例2 手続き型
<?php
$link = mysqli_connect('localhost', 'my_user', 'my_password', 'world');
/* 接続状況をチェックします */
if (!$link) {
printf("Connect failed: %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 Row inserted.\n", mysqli_stmt_affected_rows($stmt));
/* ステートメントと接続を閉じます */
mysqli_stmt_close($stmt);
/* CountryLanguage テーブルをクリアします */
mysqli_query($link, "DELETE FROM CountryLanguage WHERE Language='Bavarian'");
printf("%d Row deleted.\n", mysqli_affected_rows($link));
/* 接続を閉じます */
mysqli_close($link);
?>
上の例の出力は以下となります。
1 Row inserted. 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() - 直近のステートメントのエラー内容を文字列で返す
add a note
User Contributed Notesmysqli_stmt::bind_param
Miguel Hatrick
17-Jul-2009 03:14
17-Jul-2009 03:14
Kai Sellgren
12-Mar-2009 11:26
12-Mar-2009 11:26
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.
tasdildiren at gmail dot com
25-Feb-2009 10:29
25-Feb-2009 10:29
<?php
/* just explaining how to call mysqli_stmt_bind_param with a parameter array */
$sql_link = mysqli_connect('localhost', 'my_user', 'my_password', 'world');
$type = "isssi";
$param = array("5", "File Description", "File Title", "Original Name", time());
$sql = "INSERT INTO file_detail (file_id, file_description, file_title, file_original_name, file_upload_date) VALUES (?, ?, ?, ?, ?)";
$sql_stmt = mysqli_prepare ($sql_link, $sql);
call_user_func_array('mysqli_stmt_bind_param', array_merge (array($sql_stmt, $type), $param);
mysqli_stmt_execute($sql_stmt);
?>
Mahees
21-Feb-2009 06:34
21-Feb-2009 06:34
///////////////////////////////
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;
}
?>
mark at x2software dot net
07-Sep-2008 09:26
07-Sep-2008 09:26
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.
jette at nerdgirl dot dk
17-Jul-2008 08:46
17-Jul-2008 08:46
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();
}
}
michael dot martinek at gmail dot com
30-Dec-2007 01:00
30-Dec-2007 01:00
Small correction. This version removes the NULL element from the array, so it doesn't fall on to the next ? when passed to mysql_stmt_bind_param(). Note that $saParams is still passed by reference, but now it is being modified.
<?php
function preparse_prepared($sQuery, &$saParams)
{
$nPos = 0;
$sRetval = $sQuery;
foreach ($saParams as $x_Key => $Param)
{
//if we find no more ?'s we're done then
if (($nPos = strpos($sQuery, '?', $nPos + 1)) === false)
{
break;
}
//this test must be done second, because we need to increment offsets of $nPos for each ?.
//we have no need to parse anything that isn't NULL.
if (!is_null($Param))
{
continue;
}
//null value, replace this ? with NULL.
$sRetval = substr_replace($sRetval, 'NULL', $nPos, 1);
//unset this element now
unset($saParams[$x_Key]);
}
return $sRetval;
}
?>
michael dot martinek at gmail dot com
30-Dec-2007 12:52
30-Dec-2007 12:52
I've found that you can't pass NULL values in using mysql_stmt_bind_param. Recently I ran into this problem because I wrote some MySQL routines that would update existing data, but only when the value wasn't NULL.
My solution to work around this is simple:
<?php
function preparse_prepared($sQuery, &$saParams)
{
$nPos = 0;
$sRetval = $sQuery;
foreach ($saParams as $Param)
{
//if we find no more ?'s we're done then
if (($nPos = strpos($sQuery, '?', $nPos + 1)) === false)
{
break;
}
//this test must be done second, because we need to increment offsets of $nPos for each ?.
//we have no need to parse anything that isn't NULL.
if (!is_null($Param))
{
continue;
}
//null value, replace this ? with NULL.
$sRetval = substr_replace($sRetval, 'NULL', $nPos, 1);
}
return $sRetval;
}
?>
This will iterate the given list of parameters and replace any null values in the query with an actual null value. You'll want to use the resulting $sQuery to pass to mysqli_prepare(). For that, I use another routine that generates a list of the values (s, i, etc).
For example:
<?php
array_unshift($saParams, $this->getPreparedTypeString($saParams));
array_unshift($saParams, $stmt);
call_user_func_array('mysqli_stmt_bind_param', $saParams);
?>
Where getPreparedTypeString is defined as:
<?php
public static function getPreparedTypeString(&$saParams)
{
$sRetval = '';
//if not an array, or empty.. return empty string
if (!is_array($saParams) || !count($saParams))
{
return $sRetval;
}
//iterate the elements and figure out what they are, and append to result
foreach ($saParams as $Param)
{
if (is_int($Param))
{
$sRetval .= 'i';
}
else if (is_double($Param))
{
$sRetval .= 'd';
}
else if (is_string($Param))
{
$sRetval .= 's';
}
}
return $sRetval;
}
?>
To clarify why I pass array values by reference: They aren't being modified, so I don't want copies of them begin made in memory as they may be large. In other languages, this is much more efficient. Not sure if PHP handles passing values on a "copy on edit" basis.. but I'm guessing not.
Anonymous
15-Dec-2007 06:24
15-Dec-2007 06:24
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.
usera at example dot com
20-Jul-2007 03:50
20-Jul-2007 03:50
Some examples in the documentation suggest that you can call $stmt->bind_param() once, then call $stmt->execute() several times while altering the bound variables each time, so as to e.g. insert several records into a data base. This is not true.
You need to call $stmt->bind_param() once each time AFTER you altered the set of variables, and BEFORE you call $stmt->execute()
This may be a bug. If it is not, the documentation is flawed, and there is no gain to the programmer using the new mysqli interface at this point.
mixleplix1 at yahoo dot com
19-Jun-2007 08:12
19-Jun-2007 08:12
To continue on previous post
Bigints and the 'd' type:
If the digit you insert is longer then 16 digits the last digits will alter. I was noticing this in my inserts.
1111111111111111111 changes to 1111111111111111168
I had to switch to using 's' as type
flame
19-Feb-2007 04:44
19-Feb-2007 04:44
Columns with type bigint need to be specified as type 'd' NOT 'i'.
Using 'i' results in large numbers (eg 3000169151) being truncated.
--
flame