PDOStatement
在线手册：中文 英文
PHP手册

PDOStatement::bindParam

(PHP 5 >= 5.1.0, PECL pdo >= 0.1.0)

PDOStatement::bindParam — Binds a parameter to the specified variable name

说明

bool PDOStatement::bindParam ( mixed $parameter , mixed &$variable [, int $data_type = PDO::PARAM_STR [, int $length [, mixed $driver_options ]]] )

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

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.

variable

Name of the PHP variable to bind to the SQL statement parameter.

data_type

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

Length of the data type. To indicate that a parameter is an OUT parameter from a stored procedure, you must explicitly set the length.

driver_options

返回值

成功时返回 TRUE， 或者在失败时返回 FALSE.

范例

Example #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();
?>

Example #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();
?>

Example #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");
?>

参见

PDOStatement
在线手册：中文 英文
PHP手册
PHP手册 - N: Binds a parameter to the specified variable name

用户评论:

cyrylas at gmail dot com (10-Jan-2011 09:23)

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';
?>

ReK_ (19-Oct-2010 06:59)

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.

atrandafirc at yahoo dot com (31-Aug-2010 02:37)

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);
?>

sergiy dot sokolenko at gmail dot com (02-Jul-2010 11:22)

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

Vili (28-May-2010 08:01)

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);
}
?>

geompse at gmail dot com (29-Jan-2010 11:30)

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.

Steve M (19-Nov-2009 07:28)

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"

dhammari at q90 dot com (08-Jul-2009 07:22)

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

jeffwa+php at gmail dot com (11-Jul-2007 09:49)

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.

willie at spenlen dot com (14-Jun-2007 07:49)

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.

Filofox (10-Apr-2006 11:09)

Do not try to use the same named parameter twice in a single SQL statement, for example

<?php
$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.

(05-Feb-2006 10:25)

A caution for those using bindParam() on a placeholder in a
LIKE '%...%' clause, the following code will likely not work:

<?php
$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:

<?php
$s = "%$s%";
$sth->bindParam(':foo', $s);
?>

This should work. Tested against mysql 4.1, PHP 5.1.3.