[edit] Last updated: Sat, 07 Jan 2012

PDOStatement->bindParam

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

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

Beschreibung

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-Liste

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

Rückgabewerte

Gibt bei Erfolg TRUE zurück. Im Fehlerfall wird FALSE zurückgegeben.

Beispiele

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

Siehe auch

PDO::prepare() - Prepares a statement for execution and returns a statement object
PDOStatement::execute() - Executes a prepared statement
PDOStatement::bindValue() - Binds a value to a parameter

PDOStatement->bindValue

PDOStatement->bindColumn

[edit] Last updated: Sat, 07 Jan 2012

add a note User Contributed Notes PDOStatement->bindParam

cyrylas at gmail dot com 10-Jan-2011 01: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 10: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 06: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 03: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 12: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 03: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 11: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 11: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 01: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 11: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 03: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 02: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.

add a note