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 — Passa variáveis para um preparado comando como parâmetros
Descrição
Modo orientado a objeto (método):
Modo procedural:
Passa variáveis para marcadores de parâmetros no comando SQL que foi passado para mysqli_prepare().
Nota: Se o tamanho da informação da variáveis excede o máximo permitido do tamanho de pacote (max_allowed_packet), você deve especificar b no types e usar mysqli_stmt_send_long_data() para enviar a informação em pacotes.
Parâmetros
- stmt
-
Apenas para estilo de procedimento: Um identificador de statement retornado por mysqli_stmt_init().
- types
-
A string que contém um ou mais caracteres que especifica os tipos para as correspondente variáveis passadas:
Caracteres de especificação de tipo Caractere Descrição i corresponde a uma variável de tipo inteiro d corresponde a uma variável de tipo double s corresponde a uma variável de tipo string b corresponde a uma variável que contém dados para um blob e enviará em pacotes - var1
-
O número de variáveis e tamanho da string types precisa combinar com os parâmetros no comando.
Valor Retornado
Retorna TRUE em caso de sucesso ou FALSE em falhas.
Exemplos
Exemplo #1 Modo orientado a objeto
<?php
$mysqli = new mysqli('localhost', 'my_user', 'my_password', 'world');
/* check connection */
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;
/* execute prepared statement */
$stmt->execute();
printf("%d Row inserted.\n", $stmt->affected_rows);
/* close statement and connection */
$stmt->close();
/* Clean up table CountryLanguage */
$mysqli->query("DELETE FROM CountryLanguage WHERE Language='Bavarian'");
printf("%d Row deleted.\n", $mysqli->affected_rows);
/* close connection */
$mysqli->close();
?>
Exemplo #2 Modo procedural
<?php
$link = mysqli_connect('localhost', 'my_user', 'my_password', 'world');
/* check connection */
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;
/* execute prepared statement */
mysqli_stmt_execute($stmt);
printf("%d Row inserted.\n", mysqli_stmt_affected_rows($stmt));
/* close statement and connection */
mysqli_stmt_close($stmt);
/* Clean up table CountryLanguage */
mysqli_query($link, "DELETE FROM CountryLanguage WHERE Language='Bavarian'");
printf("%d Row deleted.\n", mysqli_affected_rows($link));
/* close connection */
mysqli_close($link);
?>
O exemplo acima irá imprimir:
1 Row inserted. 1 Row deleted.
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);