PDOStatement->bindParam
(PHP 5 >= 5.1.0, PECL pdo >= 0.1.0)
PDOStatement->bindParam — Binds a parameter to the specified variable name
Description
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.
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() - 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->bindParam
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.
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||'%'");
.......
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();
}
?>
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
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();
?>
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.
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.
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.
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.
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';
}
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.
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.
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.