mysqli_stmt::attr_set
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.
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 (méthode) :
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.
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 à virgule flottante 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, FALSE en cas d'échec.
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.
add a note
User Contributed Notesmysqli_stmt::bind_param
mark at x2software dot net
07-Sep-2008 02:26
07-Sep-2008 02:26
jette at nerdgirl dot dk
17-Jul-2008 01:46
17-Jul-2008 01: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();
}
}
Anonymous
02-Jun-2008 09:04
02-Jun-2008 09:04
dams at php dot net: You can't use this function for getting results, that is what bind_result is for.
I noticed that if you try to use a ? to match a column that doesn't exist, you get a warning that there are too many variables in the bind_param call, for instance:
$stmt->prepare ("INSERT INTO t (badname) VALUES (?)");
$stmt->bind_param("s", $val);
dams at php dot net
25-May-2008 11:34
25-May-2008 11:34
don't try to bin twice the same variable, as it won't work.
$query = "select * from table where col1 = ? or col2 = ?";
mysqli_bind_param($stmt, "ss", $arg, $arg);
You have to use a another variable :
$query = "select * from table where col1 = ? or col2 = ?";
$arg2 = $arg;
mysqli_bind_param($stmt, "ss", $arg, $arg2);
michael dot martinek at gmail dot com
30-Dec-2007 05:00
30-Dec-2007 05: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 04:52
30-Dec-2007 04: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 10:24
15-Dec-2007 10: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.
mixleplix1 at yahoo dot com
19-Jun-2007 01:12
19-Jun-2007 01: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 08:44
18-Feb-2007 08: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