mysqli_stmt::attr_set
For those learning mysqli::prepare and mysqli_stmt::bind_params for the first time, here is a commented block of code which executes prepared queries and returns data in a similar format to the return values of mysqli_query. I tried to minimize unnecessary classes, objects, or overhead for two reasons:
1) facilitate learning
2) allow relativity interchangeable use with mysqli_query
My goal is to lower the learning curve for whoever is starting out with these family of functions. I am by no means an expert coder/scripter, so I am sure there are improvements and perhaps a few bugs, but I hope not =)
<?php
/*
Function: mysqli_prepared_query()
Executes prepared querys given query syntax, and bind parameters
Returns data in array format
Arguments:
mysqli_link
mysqli_prepare query
mysqli_stmt_bind_param argmuent list in the form array($typeDefinitinonString, $var1 [, mixed $... ])
Return values:
When given SELECT, SHOW, DESCRIBE or EXPLAIN statements: returns table data in the form resultArray[row number][associated field name]
Returns number of rows affacted when given other queries
Returns FALSE on error
*/
function mysqli_prepared_query($link,$sql,$bindParams = FALSE){
if($stmt = mysqli_prepare($link,$sql)){
if ($bindParams){
$bindParamsMethod = new ReflectionMethod('mysqli_stmt', 'bind_param'); //allows for call to mysqli_stmt->bind_param using variable argument list
$bindParamsReferences = array(); //will act as arguments list for mysqli_stmt->bind_param
$typeDefinitionString = array_shift($bindParams);
foreach($bindParams as $key => $value){
$bindParamsReferences[$key] = &$bindParams[$key];
}
array_unshift($bindParamsReferences,$typeDefinitionString); //returns typeDefinition as the first element of the string
$bindParamsMethod->invokeArgs($stmt,$bindParamsReferences); //calls mysqli_stmt->bind_param suing $bindParamsRereferences as the argument list
}
if(mysqli_stmt_execute($stmt)){
$resultMetaData = mysqli_stmt_result_metadata($stmt);
if($resultMetaData){
$stmtRow = array(); //this will be a result row returned from mysqli_stmt_fetch($stmt)
$rowReferences = array(); //this will reference $stmtRow and be passed to mysqli_bind_results
while ($field = mysqli_fetch_field($resultMetaData)) {
$rowReferences[] = &$stmtRow[$field->name];
}
mysqli_free_result($resultMetaData);
$bindResultMethod = new ReflectionMethod('mysqli_stmt', 'bind_result');
$bindResultMethod->invokeArgs($stmt, $rowReferences); //calls mysqli_stmt_bind_result($stmt,[$rowReferences]) using object-oriented style
$result = array();
while(mysqli_stmt_fetch($stmt)){
foreach($stmtRow as $key => $value){ //variables must be assigned by value, so $result[] = $stmtRow does not work (not really sure why, something with referencing in $stmtRow)
$row[$key] = $value;
}
$result[] = $row;
}
mysqli_stmt_free_result($stmt);
} else {
$result = mysqli_stmt_affected_rows($stmt);
}
mysqli_stmt_close($stmt);
} else {
$result = FALSE;
}
} else {
$result = FALSE;
}
return $result;
}
?>
Here's hoping the PHP gods don't smite me.
mysqli_stmt::bind_param
mysqli_stmt_bind_param
(PHP 5)
mysqli_stmt::bind_param -- mysqli_stmt_bind_param — Lie des variables à une requête MySQL
Description
Style orienté objet
Style procédural
Sert à lier des variables à une requête MySQL préparée par mysqli_prepare().
Note:
Si la taille des données dépasse la taille maximal d'un paquet, (max_allowed_packet), vous devez spécifier le caractère b dans le paramètre types et utiliser la fonction mysqli_stmt_send_long_data() pour envoyer le message par paquets.
Note:
Vous devez être prudent lors de l'utilisation de mysqli_stmt_bind_param() avec la fonction call_user_func_array(). Notez que mysqli_stmt_bind_param() nécessite que ses paramètres soient passés par référence, alors que la fonction call_user_func_array() peut accepter comme paramètre une liste de variables qui peuvent représenter des références ou des valeurs.
Liste de paramètres
- stmt
-
Style procédural uniquement : Un identifiant de requête retourné par la fonction mysqli_stmt_init().
- types
-
Une chaîne de caractères qui contient un ou plusieurs caractères qui spécifient le type de la variable à lier :
Caractère de spécification des types Caractère Description i correspond à une variable de type entier d correspond à une variable de type nombre décimal s correspond à une variable de type chaîne de caractères b correspond à une variable de type BLOB, qui sera envoyé par paquets - var1
-
Le nombre de variables et la longueur de la chaîne de caractères types doivent correspondre aux paramètres de la requête.
Valeurs de retour
Cette fonction retourne TRUE en cas de succès ou FALSE si une erreur survient.
Exemples
Exemple #1 Style orienté objet
<?php
$mysqli = new mysqli('localhost', 'my_user', 'my_password', 'world');
/* Vérification de la connexion */
if (mysqli_connect_errno()) {
printf("Échec de la connexion : %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;
/* Exécution de la requête */
$stmt->execute();
printf("%d ligne insérée.\n", $stmt->affected_rows);
/* Fermeture du traitement */
$stmt->close();
/* Nettoyage de la table CountryLanguage */
$mysqli->query("DELETE FROM CountryLanguage WHERE Language='Bavarian'");
printf("%d ligne effacée.\n", $mysqli->affected_rows);
/* Fermeture de la connexion */
$mysqli->close();
?>
Exemple #2 Style procédural
<?php
$link = mysqli_connect('localhost', 'my_user', 'my_password', 'world');
/* Vérification de la connexion */
if (!$link) {
printf("Échec de la connexion : %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;
/* Exécution de la requête */
mysqli_stmt_execute($stmt);
printf("%d ligne insérée.\n", mysqli_stmt_affected_rows($stmt));
/* Fermeture du traitement */
mysqli_stmt_close($stmt);
/* Nettoyage de la table CountryLanguage */
mysqli_query($link, "DELETE FROM CountryLanguage WHERE Language='Bavarian'");
printf("%d ligne effacée.\n", mysqli_affected_rows($link));
/* Fermeture de la connexion */
mysqli_close($link);
?>
L'exemple ci-dessus va afficher :
1 ligne insérée. 1 ligne effacée.
Voir aussi
- mysqli_stmt_bind_result() - Lie des variables à un jeu de résultats
- mysqli_stmt_execute() - Exécute une requête préparée
- mysqli_stmt_fetch() - Lit des résultats depuis une requête MySQL préparée dans des variables liées
- mysqli_prepare() - Prépare une requête SQL pour l'exécution
- mysqli_stmt_send_long_data() - Envoie des données MySQL par paquets
- mysqli_stmt_errno() - Retourne un code erreur pour la dernière requête
- mysqli_stmt_error() - Retourne une description de la dernière erreur de traitement
add a note
User Contributed Notes
mysqli_stmt::bind_param
Darren
11-Jan-2012 05:00
samishiikihaku23 at gmail dot com
30-Dec-2011 04:50
NOTES to new users! Or programmers like myself who learn the hard way!!!
Pay attention to the variables that are given in the function up above. ( string $types , mixed &$var1 [, mixed &$... ] ) . The example shows this too, but I personally didn't get it 'til trying to debug my code.
ITS REQUIRED TO PASS VARIABLES HERE. You can not pass straight data through here.
$stmt->bind('s','Something here'); Will error!!!
Just a clarification as to avoid another night like mine last night and this morning.
Ole Clausen
06-Nov-2011 12:50
A lot of newcommers to mysqli find it hard to get started. I have written this wrapper with object based response, that handles most of my queries. I hope it'll be usefull for others as well:
<?php
define('DB_HOST', 'localhost');
define('DB_USERNAME', '');
define('DB_PASSWORD', '');
define('DB_DEFAULT_DB', 'test');
function iQuery($sql, $arrParams, $arrBindNames=false) {
$result = new stdClass();
$mysqli = @new mysqli(DB_HOST, DB_USERNAME, DB_PASSWORD, DB_DEFAULT_DB);
if (mysqli_connect_errno()) {
$result->error = 'Connection failed: '.mysqli_connect_error();
return $result;
}
if ($stmt = $mysqli->prepare($sql)) {
$method = new ReflectionMethod('mysqli_stmt', 'bind_param');
$method->invokeArgs($stmt, $arrParams);
$stmt->execute();
$meta = $stmt->result_metadata();
if (!$meta) {
$result->affected_rows = $stmt->affected_rows;
$result->insert_id = $stmt->insert_id;
} else {
$stmt->store_result();
$params = array();
$row = array();
if ($arrBindNames) {
for ($i=0,$j=count($arrBindNames); $i<$j; $i++) {
$params[$i] = &$row[$arrBindNames[$i]];
}
} else {
while ($field = $meta->fetch_field()) {
$params[] = &$row[$field->name];
}
}
$meta->close();
$method = new ReflectionMethod('mysqli_stmt', 'bind_result');
$method->invokeArgs($stmt, $params);
$result->rows = array();
while ($stmt->fetch()) {
$obj = new stdClass();
foreach($row as $key => $val) {
$obj->{$key} = $val;
}
$result->rows[] = $obj;
}
$stmt->free_result();
}
$stmt->close();
}
$mysqli->close();
return $result;
}
$arrParams = array('ss', $_POST['sex'], $_POST['active']);
$result = iQuery( 'SELECT * FROM `test_table` WHERE `sex`=? AND `active`=?', $arrParams);
print_r($result);
print $result->rows[1]->first_name . " " . $result->rows[1]->last_name;
?>
If $_POST['sex'] contains 'male' and $_POST['active'] contains 'yes' - and the field names are 'id', 'first_name', 'last_name', 'sex' and 'active', the printet result may look like this:
----------------------------------------------
stdClass Object
(
[rows] => Array
(
[0] => stdClass Object
(
[id] => 2
[first_name] => Peter
[last_name] => Johnson
[sex] => male
[active] => yes
)
[1] => stdClass Object
(
[id] => 5
[first_name] => Ole
[last_name] => Clausen
[sex] => male
[active] => yes
)
)
)
Ole Clausen
----------------------------------------------
You can also apply special field names to the response, if you use the parameter $arrBindNames:
$arrParams = array('ss', $_POST['sex'], $_POST['active']);
$arrNames = array('foo_id', 'bar_first', 'baz_last', 'foo_sex', 'bar_act');
$result = iQuery( 'SELECT * FROM `test_table` WHERE `sex`=? AND `active`=?', $arrParams, $arrNames);
- a row would then look like this:
[0] => stdClass Object
(
[foo_id] => 2
[bar_first] => Peter
[baz_last] => Johnson
[foo_sex] => male
[bar_act] => yes
)
The first argument 'ss' in $arrParams states, that the two following arguments are of type String. The options are 's' for String, 'i' for Integer, 'd' for Double and 'b' for Blob (sent in packages).
In queries, that do not return a result INSERT, UPDATE, etc. $result->affected_rows and $result->insert_id are available. Connection errors are available in $result->error. Additional error handling would be nice, but is not implemented for now. Play with the wrapper and use print_r on the result ... enjoy!
The name 'iQuery'? Well, it handles mysql*i* - and then I guess it's kind of a tribute to Mr. Jobs ... may he 'rest' in energetic, hungry foolishness =)
==
UPDATE: 08-NOV-2011 07:19
Due to changes in PHP 5.3 I encountered a problem with 'bind_param' in my iQuery function below. The values in the passed array *must* be references. The soloution is this function:
<?php
function getRefArray($a) {
if (strnatcmp(phpversion(),'5.3')>=0) {
$ret = array();
foreach($a as $key => $val) {
$ret[$key] = &$a[$key];
}
return $ret;
}
return $a;
}
?>
- and this change in iQuery:
if ($stmt = $mysqli->prepare($sql)) {
$arrParams = getRefArray($arrParams); // <-- Added due to changes since PHP 5.3
$method = new ReflectionMethod('mysqli_stmt', 'bind_param');
$method->invokeArgs($stmt, $arrParams);
$stmt->execute();
$meta = $stmt->result_metadata();
ghostarthour at exahost dot eu
27-Aug-2011 04:00
Simple workaround for casting:
<?php
function mysqli_cast_workaround() {
$numargs = func_num_args();
$types = '';
for ($i = 0; $i < $numargs; $i++) {
$arg = func_get_arg($i);
if (is_numeric($arg)) {
$types.="i";
}else{
$types.="s";
}
$args[($i+1)] = $arg;
unset($arg);
}
$args[0] = $types;
return call_user_func_array('mysqli_stmt_bind_param',$args);
}
?>
alex dot deleyn at gmail dot com
16-Jul-2011 08:50
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
jk at jankriedner dot de
20-May-2011 04:39
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();
}
?>
Coming to the problem calling mysqli::bind_param() with a dynamic number of arguments via call_user_func_array() with PHP Version 5.3+, there's another workaround besides using an extra function to build the references for the array-elements.
You can use Reflection to call mysqli::bind_param(). When using PHP 5.3+ this saves you about 20-40% Speed compared to passing the array to your own reference-builder-function.
Example:
<?php
$db = new mysqli("localhost","root","","tests");
$res = $db->prepare("INSERT INTO test SET foo=?,bar=?");
$refArr = array("si","hello",42);
$ref = new ReflectionClass('mysqli_stmt');
$method = $ref->getMethod("bind_param");
$method->invokeArgs($res,$refArr);
$res->execute();
?>
asb(.d o,t )han(a t)n i h e i(d.o_t)dk
22-Apr-2011 08:05
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.
Anonymous
28-Mar-2011 03:49
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);
Anonymous
14-Mar-2011 03:28
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'
?>
user at mail dot com
25-Jan-2011 01:44
I had a problem with the LIKE operator
This code did not work:
<?php
$test = $sql->prepare("SELECT name FROM names WHERE name LIKE %?%");
$test->bind_param("s", $myname);
?>
The solution is:
<?php
$test = $sql->prepare("SELECT name FROM names WHERE name LIKE ?");
$param = "%" . $myname . "%";
$test->bind_param("s", $param);
?>
canche_x at yahoo dot com
12-Nov-2010 05:43
Hi, I just write a function to do all my sql statements based on all the others comments in this page, maybe it can be useful for someone else :)
Usage:
execSQL($sql, $parameters, $close);
$sql = Statement to execute;
$parameters = array of type and values of the parameters (if any)
$close = true to close $stmt (in inserts) false to return an array with the values;
Examples:
execSQL("SELECT * FROM table WHERE id = ?", array('i', $id), false);
execSQL("SELECT * FROM table", array(), false);
execSQL("INSERT INTO table(id, name) VALUES (?,?)", array('ss', $id, $name), true);
<?php
function execSQL($sql, $params, $close){
$mysqli = new mysqli("localhost", "user", "pass", "db");
$stmt = $mysqli->prepare($sql) or die ("Failed to prepared the statement!");
call_user_func_array(array($stmt, 'bind_param'), refValues($params));
$stmt->execute();
if($close){
$result = $mysqli->affected_rows;
} else {
$meta = $stmt->result_metadata();
while ( $field = $meta->fetch_field() ) {
$parameters[] = &$row[$field->name];
}
call_user_func_array(array($stmt, 'bind_result'), refValues($parameters));
while ( $stmt->fetch() ) {
$x = array();
foreach( $row as $key => $val ) {
$x[$key] = $val;
}
$results[] = $x;
}
$result = $results;
}
$stmt->close();
$mysqli->close();
return $result;
}
function refValues($arr){
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;
}
?>
Regards
erik at gravyllc dot com
21-Sep-2010 11:05
WOW! Thanks for the code that fixed the issue with mysqli_stmt_bind_param and PHP 5.3+. Worth sharing again for people getting the error message that a reference was expected and a value was provided. Here's a snippet and the whole function that fixed it!
//Use it like this
call_user_func_array('mysqli_stmt_bind_param', array_merge (array($sql_stmt, $type), $this->refValues($param)));
function refValues($arr)
{
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;
}
Mark Reddin
08-Sep-2010 05:30
Miguel Hatrick's Statement_Parameter class, as posted in these notes, allows for a relatively painless way of writing secure dynamic SQL. It is secure against SQL injection because we still use bind parameters for any content coming from the user.
For example, the following code constructs an insert statement, but looks at which query string (GET) parameters are present in order to figure out which columns should be included. The ParameterManager.php file is simply Miguel's classes as posted in this discussion.
<?php
require_once("dbConnectionParams.php");
require_once("ParameterManager.php");
$sp = new Statement_Parameter();
$column_list = "";
$value_list = "";
if (isset ($_GET['name']) ) {
$column_list = $column_list . "name,";
$value_list = $value_list . "?,";
$sp->Add_Parameter('name', Statement_Parameter_Type::$STATEMENT_TYPE_STRING);
$sp->Set_Parameter('name',$_GET['name']);
}
if (isset($_GET['address']) ) {
$column_list = $column_list . "address,";
$value_list = $value_list . "?,";
$sp->Add_Parameter('address', Statement_Parameter_Type::$STATEMENT_TYPE_STRING);
$sp->Set_Parameter('address',$_GET['address']);
}
//tidy up column list and value list - the code above will always leave them ending in a comma, which we remove now
$column_list = substr($column_list, 0, strlen($column_list) -1);
$value_list = substr($value_list, 0, strlen($value_list) -1);
$sql = "insert into test_table (" . $column_list . ") values (" . $value_list . ");";
echo $sql;
$mysqli = @new mysqli($host,$user,$password,$database);
$stmt = $mysqli->prepare($sql);
$sp->Bind_Params($stmt);
if($stmt->execute() === TRUE)
{
/*** assign the last insert id ***/
$last_id = $mysqli->insert_id;
echo "OK$last_id";
}
else {
echo $mysqli->error;
}
?>
gregg at mochabomb dot com
28-Apr-2010 12:29
Used the hints above - esp the call_user_func_array - what works simply is passing by reference...
<?php
class MySQL {
// so vars that are global to the class
var $connection;
var $dbc;
function __construct () {
$this->connection = new mysqli(DB_HOST, DB_USER, DB_PASSWORD, DB_NAME);
if (mysqli_connect_errno()) {
//printf("Could not connect to the DB: %s\n", mysqli_connect_error()); // TMI
printf("Could not connect to the DB");
exit();
}
}
function DBi($info) {
// a call has this array structure
// $this->info['params'] = array('is', &$user, &$name);
// $this->info['query'] = "select id, username, role_id from users where id = ? and name = ?";
// $this->info['close'] = "false"; // return $stmt for further work
//
$mysqli = $this->connection;
$params = $info['params'];
// print_r($info); // debug
if ($stmt = $mysqli->prepare($info['query'])) {
$ret = call_user_func_array (array($stmt,'bind_param'),$info['params']);
// $ret not used yet...
$stmt->execute();
if ($info['close'] == "true") {
$result = $mysqli->affected_rows;
$stmt->close();
return $result;
} else {
return $stmt;
}
} else { printf("Prepared Statement Error: $server_id \n"); }
}
}
?>
The close is generally for inserts, else the $stmt is returned for further processing...
<?php
// call the method to run the prepared query, then return statement handle. If just wanted an insert, use close = true
if ( is_int($user) ) {
$this->info['params'] = array('is', &$user, &$name);
$this->info['query'] = "select id, username, role_id from users where id = ? and name = ?";
$this->info['close'] = "false"; // return $stmt for further work
$stmt = parent::DBi($this->info);
$stmt->bind_result($col1, $col2, $col3);
while( $stmt->fetch() ) {
$res['id'] = $col1;
$res['username'] = $col2;
$res['role_id'] = $col3;
$res['error'] = 0;
}
$stmt->close;
return $res;
}
?>
eisoft
18-Mar-2010 04:42
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 ) ;
?>
fabio at kidopi dot com dot br
15-Mar-2010 10:14
I used to have problems with call_user_func_array and bind_param after migrating to php 5.3.
The problem is that 5.3 requires array values as reference while 5.2 works with real values.
so i created a secondary function to help me with this...
<?php
function refValues($arr){
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;
}
?>
and changed my previous function from:
<?php
call_user_func_array(array($this->stmt, "bind_param"),$this->valores);
?>
to:
<?php
call_user_func_array(array($this->stmt, "bind_param"),refValues($this->valores));
?>
in this way my db functions keep working in php 5.2/5.3 servers.
I hope this help someone.
rejohns at nOsPaMpost dot harvard dot edu
10-Feb-2010 09:24
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.
Miguel Hatrick
17-Jul-2009 10:14
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';
}
?>
Kai Sellgren
12-Mar-2009 04: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 03: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 11: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 04: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 03: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 06: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 05: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 11: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 10: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 03: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
18-Feb-2007 09: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