PDOStatement->bindParam SQL语句参数绑定

Myth丶恋晨 2022-08-13 00:34 198阅读 0赞

# PDOStatement->bindParam #

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

PDOStatement->bindParam — Binds a parameter to the specified variable name

### Description ###

bool **PDOStatement::bindParam** ( [mixed][] $parameter , [mixed][] &$variable \[, int $data\_type \[, 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()][PDOStatement_bindValue], the variable is bound as a reference and will only be evaluated at the time that [PDOStatement::execute()][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.

### Parameters ###

*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. Defaults to **PDO::PARAM\_STR**. 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*

### Return Values ###

Returns **TRUE** on success or **FALSE** on failure.

### Examples ###

**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"); ?>`

### See Also ###

*  [PDO::prepare()][PDO_prepare] \- Prepares a statement for execution and returns a statement object
 *  [PDOStatement::execute()][PDOStatement_execute] \- Executes a prepared statement
 *  [PDOStatement::bindValue()][PDOStatement_bindValue] \- Binds a value to a parameter

[PDOStatement->bindValue![>][caret-r.gif]][PDOStatement_bindValue] [![<][caret-l.gif]PDOStatement->bindColumn][caret-l.gif_PDOStatement-_bindColumn]

--------------------

Last updated: Fri, 06 Mar 2009

[![add a note][]][add a note 1] [add a note][add a note 1] User Contributed Notes  
 **PDOStatement->bindParam**

**Alex**  
[20-Sep-2008 10:00][20-Sep-2008 10_00]

`trying to use: <?php .... $sql = 'select * from mytable where id in(:ids)'; $stmt->bindParam(':ids', '1,2,3,4'); .... ?> will fail.`

**ivanov at abv dot bg**  
[18-Sep-2008 09:16][18-Sep-2008 09_16]

`@bjornie at nowhere You could do the concatenation in the query itself like this: $stmt = $pdh->prepare("SELECT id, uname FROM tbl WHERE uname LIKE '%'||:uname||'%'"); .......`

**chance garcia**  
[18-Aug-2008 02:09][18-Aug-2008 02_09]

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

**Charlie Smith**  
[21-Apr-2008 06:30][21-Apr-2008 06_30]

`$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`

**phil\[AT\]papg\[DOT\]net**  
[12-Mar-2008 06:04][12-Mar-2008 06_04]

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

**Sam Bou**  
[01-Dec-2007 10:56][01-Dec-2007 10_56]

`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.`

**xzilla at users dot sourceforge dot net**  
[13-Sep-2007 03:07][13-Sep-2007 03_07]

`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.`

**bjornie at nowhere**  
[12-Aug-2007 03:34][12-Aug-2007 03_34]

`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.`

**jeffwa+php at gmail dot com**  
[11-Jul-2007 08:49][11-Jul-2007 08_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.``

**m dot van dot urk at comsi dot nl**  
[02-Jul-2007 02:33][02-Jul-2007 02_33]

`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';         }`

**willie at spenlen dot com**  
[14-Jun-2007 06:49][14-Jun-2007 06_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 10:09][10-Apr-2006 10_09]

`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.`

[05-Feb-2006 10:25][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: $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.`

[mixed]: http://writeblog.csdn.net/language.pseudo-types.php#language.types.mixed
[PDOStatement_bindValue]: http://writeblog.csdn.net/pdostatement.bindvalue.php
[PDOStatement_execute]: http://writeblog.csdn.net/pdostatement.execute.php
[PDO_prepare]: http://writeblog.csdn.net/pdo.prepare.php
[caret-r.gif]: http://writeblog.csdn.net/images/caret-r.gif
[caret-l.gif]: http://writeblog.csdn.net/images/caret-l.gif
[caret-l.gif_PDOStatement-_bindColumn]: http://writeblog.csdn.net/pdostatement.bindcolumn.php
[add a note]: http://writeblog.csdn.net/images/notes-add.gif
[add a note 1]: http://writeblog.csdn.net/manual/add-note.php?sect=pdostatement.bindparam&redirect=http://php.oregonstate.edu/manual/en/pdostatement.bindparam.php
[20-Sep-2008 10_00]: http://writeblog.csdn.net/#85845
[18-Sep-2008 09_16]: http://writeblog.csdn.net/#85798
[18-Aug-2008 02_09]: http://writeblog.csdn.net/#85162
[21-Apr-2008 06_30]: http://writeblog.csdn.net/#82677
[12-Mar-2008 06_04]: http://writeblog.csdn.net/#81743
[01-Dec-2007 10_56]: http://writeblog.csdn.net/#79517
[13-Sep-2007 03_07]: http://writeblog.csdn.net/#77807
[12-Aug-2007 03_34]: http://writeblog.csdn.net/#77039
[11-Jul-2007 08_49]: http://writeblog.csdn.net/#76357
[02-Jul-2007 02_33]: http://writeblog.csdn.net/#76138
[14-Jun-2007 06_49]: http://writeblog.csdn.net/#75764
[10-Apr-2006 10_09]: http://writeblog.csdn.net/#64226
[05-Feb-2006 10_25]: http://writeblog.csdn.net/#61505