Diese Seite bei php.net
The PHP manual text and comments are covered by the Creative Commons Attribution 3.0 License © the PHP Documentation Group - Impressum - mail("TO:Reinhard Neidl",...)
(PHP 5 >= 5.1.0, PECL pdo >= 0.1.0)
PDOStatement->bindParam — Binds a parameter to the specified variable name
Binds a PHP variable to a corresponding named or question mark placeholder in the SQL statement that was use to prepare the statement. Unlike PDOStatement::bindValue(), the variable is bound as a reference and will only be evaluated at the time that PDOStatement::execute() is called.
Most parameters are input parameters, that is, parameters that are used in a read-only fashion to build up the query. Some drivers support the invocation of stored procedures that return data as output parameters, and some also as input/output parameters that both send in data and are updated to receive it.
Parameter identifier. For a prepared statement using named placeholders, this will be a parameter name of the form :name. For a prepared statement using question mark placeholders, this will be the 1-indexed position of the parameter.
Name of the PHP variable to bind to the SQL statement parameter.
Explicit data type for the parameter using the PDO::PARAM_* constants. To return an INOUT parameter from a stored procedure, use the bitwise OR operator to set the PDO::PARAM_INPUT_OUTPUT bits for the data_type parameter.
Length of the data type. To indicate that a parameter is an OUT parameter from a stored procedure, you must explicitly set the length.
Gibt bei Erfolg TRUE zurück. Im Fehlerfall wird FALSE zurückgegeben.
Beispiel #1 Execute a prepared statement with named placeholders
<?php
/* Execute a prepared statement by binding PHP variables */
$calories = 150;
$colour = 'red';
$sth = $dbh->prepare('SELECT name, colour, calories
FROM fruit
WHERE calories < :calories AND colour = :colour');
$sth->bindParam(':calories', $calories, PDO::PARAM_INT);
$sth->bindParam(':colour', $colour, PDO::PARAM_STR, 12);
$sth->execute();
?>
Beispiel #2 Execute a prepared statement with question mark placeholders
<?php
/* Execute a prepared statement by binding PHP variables */
$calories = 150;
$colour = 'red';
$sth = $dbh->prepare('SELECT name, colour, calories
FROM fruit
WHERE calories < ? AND colour = ?');
$sth->bindParam(1, $calories, PDO::PARAM_INT);
$sth->bindParam(2, $colour, PDO::PARAM_STR, 12);
$sth->execute();
?>
Beispiel #3 Call a stored procedure with an INOUT parameter
<?php
/* Call a stored procedure with an INOUT parameter */
$colour = 'red';
$sth = $dbh->prepare('CALL puree_fruit(?)');
$sth->bindParam(1, $colour, PDO::PARAM_STR|PDO::PARAM_INPUT_OUTPUT, 12);
$sth->execute();
print("After pureeing fruit, the colour is: $colour");
?>
Please note, that PDO format numbers according to current locale. So if, locale set number format to something else, that standard that query WILL NOT work properly.
For example:
in Polish locale (pl_PL) proper decimal separator is coma (","), so: 123,45, not 123.45. If we try bind 123.45 to the query, we will end up with coma in the query.
<?php
setlocale(LC_ALL, 'pl_PL');
$sth = $dbh->prepare('SELECT name FROM products WHERE price < :price');
$sth->bindParam(':price', 123.45, PDO::PARAM_STR);
$sth->execute();
// result:
// SELECT name FROM products WHERE price < '123,45';
?>
This confused me for some time because it is never explicitly mentioned, but PDO will automagically encapsulate parameters for you, so a prepared query that is manually escaped like so:
"INSERT INTO table (column) VALUES (':value');"
Will actually end up being double-quoted and can cause problems.
I know this has been said before but I'll write a note on it too because I think it's important to keep in mind:
If you use PDO bindParam to do a search with a LIKE condition you cannot put the percentages and quotes to the param placeholder '%:keyword%'.
This is WRONG:
"SELECT * FROM `users` WHERE `firstname` LIKE '%:keyword%'";
The CORRECT solution is to leave clean the placeholder like this:
"SELECT * FROM `users` WHERE `firstname` LIKE :keyword";
And then add the percentages to the php variable where you store the keyword:
$keyword = "%".$keyword."%";
And finally the quotes will be automatically added by PDO when executing the query so you don't have to worry about them.
So the full example would be:
<?php
// Get the keyword from query string
$keyword = $_GET['keyword'];
// Prepare the command
$sth = $dbh->prepare('SELECT * FROM `users` WHERE `firstname` LIKE :keyword');
// Put the percentage sing on the keyword
$keyword = "%".$keyword."%";
// Bind the parameter
$sth->bindParam(':keyword', $keyword, PDO::PARAM_STR);
?>
Note that you cannot mix named and positional parameters in one query:
<?php
$stmt = $conn->prepare('SELECT * FROM employees WHERE name LIKE :name OR email LIKE ?');
$name = 'John%';
$email = 'john%';
$stmt->bindParam(':name', $name);
$stmt->bindParam(1, $email);
$stmt->execute();
?>
Fatal error: Uncaught exception 'PDOException' with message 'SQLSTATE[HY093]: Invalid parameter number: mixed named and positional parameters' in ...
Running PHP 5.3.2 on Linux x86-64
This works ($val by reference):
<?php
foreach ($params as $key => &$val) {
$sth->bindParam($key, $val);
}
?>
This will fail ($val by value, because bindParam needs &$variable):
<?php
foreach ($params as $key => $val) {
$sth->bindParam($key, $val);
}
?>
if you are storing files (or binary data), using PARAM_LOB (and moreover trying to do this with Oracle), don't miss this page :
http://php.net/manual/en/pdo.lobs.php
You will there notice that PDO-PGSQL and PDO-OCI don't work the same at all : not the same argument nor the same behaviour.
Note that when using PDOStatement::bindParam an integer is changed to a string value upon PDOStatement::execute(). (Tested with MySQL).
This can cause problems when trying to compare values using the === operator.
Example:
<?php
$active = 1;
var_dump($active);
$ps->bindParam(":active", $active, PDO::PARAM_INT);
var_dump($active);
$ps->execute();
var_dump($active);
if ($active === 1) {
// do something here
// note: this will fail since $active is now "1"
}
?>
results in:
int(1)
int(1)
string(1) "1"
There seems to be some confusion about whether you can bind a single value to multiple identical placeholders. For example:
$sql = "SELECT * FROM user WHERE is_admin = :myValue AND is_deleted = :myValue ";
$params = array("myValue" => "0");
Some users have reported that attempting to bind a single parameter to multiple placeholders yields a parameter mismatch error in PHP version 5.2.0 and earlier. Starting with version 5.2.1, however, this seems to work just fine.
For details, see bug report 40417:
http://bugs.php.net/bug.php?id=40417
@charlie smith
That behavior is expected since you are binding named parameters outside of your loop. to achieve your desired results:
<?php
$query = 'INSERT INTO pdotest SET storeid = :storeid, testid = :testid';
$statement = $con->prepare($query);
foreach ($result as $row) {
$statement->bindParam(':storeid',$row['storeid']);
$statement->bindParam(':testid',$row['testid']);
$statement->execute();
}
?>
$result[] = array(
'storeid' => '1',
'testid' => '101'
);
$result[] = array(
'storeid' => '2',
'testid' => '102'
);
print_r($result);
$row['storeid'] = '9';
$row['testid'] = '900';
$query = 'INSERT INTO pdotest SET storeid = :storeid, testid = :testid';
$statement = $dbconnection->prepare($query);
$statement->bindParam(':storeid',$row['storeid']);
$statement->bindParam(':testid',$row['testid']);
foreach ($result as $row) {
$statement->execute();
}
Will insert 9,900 twice not the intended result of:
1,101
2,102
Building on from previous comment by Sam Bou...
bindParam() can be used in a loop like this:
<?php
...
$stmt = $db->prepare("INSERT INTO users (email) VALUES (:email)");
$stmt->bindParam(':email', $val, PDO::PARAM_STR);
foreach($emails as $val) $stmt->execute();
?>
If you're using bindParam in a loop such as this:
$counter=1;
foreach($email as $val){
$stmt->bindParam($counter, $val, PDO::PARAM_STR);
$counter++;
}
It will fail because $val is local and the variable is bound as a reference and will only be evaluated at the time that PDOStatement->execute() is called.
So use bindValue instead.
currently this is not supported in the PostgreSQL driver either, though AIUI this is supported in the PostgreSQL C API, so it could be added.
I can't use wildcards in my prepared statements, using it like follows:
$stmt = $pdh->prepare("SELECT id, uname FROM tbl WHERE uname LIKE :uname OR email LIKE :email OR firstname LIKE :fn OR lastname LIKE :ln");
$un = "%" . $un . "%";
$em = "%" . $em . "%";
$fn = "%" . $fn . "%";
$ln = "%" . $ln . "%";
$stmt->bindParam(":uname", $un);
$stmt->bindParam(":email", $em);
$stmt->bindParam(":fn", $fn);
$stmt->bindParam(":ln", $ln);
This is not giving me any error messages or exceptions, it's just not returning any rows. (And there are rows matching the query...)
Running PHP5.2.3, MySQL5.0.45 and Apache2.2.4 in Windows.
Took me forever to find this elsewhere in the notes in the manual, so I'd thought I'd put this tidbit here to help others in the future.
When using a LIKE search in MySQL along with a prepared statement, the *value* must have the appropriate parentheses attached before the bindParam() statement as such:
<?php
$dbc = $GLOBALS['dbc'];
$sql = "SELECT * FROM `tbl_name` WHERE tbl_col LIKE ?";
$stmt = $dbc->prepare($sql);
$value = "%{$value}%";
$stmt->bindParam($i, $value, PDO::PARAM_STR);
?>
Trying to use
<?php
$stmt->bindParam($i, "%{$value}%", PDO::PARAM_STR);
?>
will fail.
You can't bind a table name in the query.
So the following code isn't working:
$a = 'klanten';
$sQuery = "SELECT COUNT(*) FROM ? WHERE email = 'info@site.uk' AND wachtwoord = 'welcome'";
$rResult2 = $login->db->prepare($sQuery);
$rResult2->bindValue(1, $a);
$rResult2->execute();
}
catch (PDOException $e) {
die( $e-getMessage());
}
if ($rResult2->fetchColumn() == 0) {
echo 'false';
} else {
echo 'true';
}
If you're using the MySQL driver and have a stored procedure with an OUT or INOUT parameter, you can't (currently) use bindValue(). See http://bugs.php.net/bug.php?id=35935 for a workaround.
Do not try to use the same named parameter twice in a single SQL statement, for example
$sql = 'SELECT * FROM some_table WHERE some_value > :value OR some_value < :value';
$stmt = $dbh->prepare($sql);
$stmt->execute( array( ':value' => 3 ) );
...this will return no rows and no error -- you must use each parameter once and only once. Apparently this is expected behavior (according to this bug report: http://bugs.php.net/bug.php?id=33886) because of portability issues.
A caution for those using bindParam() on a placeholder in a
LIKE '%...%' clause, the following code will likely not work:
$q = "SELECT id, name FROM test WHERE name like '%:foo%'";
$s = "carrot";
$sth = $dbh->prepare($q);
$sth->bindParam(':foo', $s);
$sth->execute();
What is needed is something like the following:
$s = "%$s%";
$sth->bindParam(':foo', $s);
This should work. Tested against mysql 4.1, PHP 5.1.3.
Diese Seite bei php.net