PHP 5.4.31 Released

PDOStatement::bindParam

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

PDOStatement::bindParam Vincula un parámetro al nombre de variable especificado

Descripción

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

Vincula una variable de PHP a un parámetro de sustitución con nombre o de signo de interrogación correspondiente de la sentencia SQL que fue usada para preparar la sentencia. A diferencia de PDOStatement::bindValue(), la variable es vinculada como una referencia y solamente será evaluada en el momento en el que se llame a PDOStatement::execute().

Muchos parámetros son de entrada, es decir, que son usados en un modo de sólo lectura para construir la consulta. Algunos controladores admiten la invocación de procedimientos almacenados que devuelven datos como parámetros de salida, y algunos también como parámetros de entrada/salida, donde se envían datos y son actualizados al recibirlos.

Parámetros

parameter

El identificador del parámetro. Para sentencias preparadas que usen parámetros de sustición con nombre, esto será un nombre de parámetro con la forma :nombre. Para sentencias preparadas que usen parámetros de sustición de signos de interrogación, esto será la posición índice-1 del parámetro.

variable

Nombre de la variable de PHP a vincular al parámetro de la sentencia SQL.

data_type

El tipo de datos explícito para el parámetro, usando las constantes PDO::PARAM_*. Para devolver un parámetro INOUT desde un procedimiento almacenado, se ha de usar el operador OR a nivel de bits para establecer los bits de PDO::PARAM_INPUT_OUTPUT para el parámetro data_type.

length

La longitud del tipo de datos. Para indicar que el parámetro es un parámetro OUT de un procedimiento almacenado, se debe establecer explícitamente la longitud.

driver_options

Valores devueltos

Devuelve TRUE en caso de éxito o FALSE en caso de error.

Ejemplos

Ejemplo #1 Ejecutar una sentencia preparada con parámetros de sustitución con nombre

<?php
/* Ejecutar una sentencia preparada vinculando varialbes de PHP */
$calorías 150;
$color 'red';
$gsent $gbd->prepare('SELECT name, colour, calories
    FROM fruit
    WHERE calories < :calories AND colour = :colour'
);
$gsent->bindParam(':calories'$caloríasPDO::PARAM_INT);
$gsent->bindParam(':colour'$colorPDO::PARAM_STR12);
$gsent->execute();
?>

Ejemplo #2 Ejecutar una sentencia preparada con parámetros de sustitución de signos de interrogación

<?php
/* Ejecutar una sentencia preparada vinculando varialbes de PHP */
$calorías 150;
$color 'red';
$gsent $gbd->prepare('SELECT name, colour, calories
    FROM fruit
    WHERE calories < ? AND colour = ?'
);
$gsent->bindParam(1$caloríasPDO::PARAM_INT);
$gsent->bindParam(2$colorPDO::PARAM_STR12);
$gsent->execute();
?>

Ejemplo #3 Llamar a un procedimiento almacenado con un parámetro INOUT

<?php
/* Llamar a un procedimiento almacenado con un parámetro INOUT */
$color 'red';
$gsent $gbd->prepare('CALL puree_fruit(?)');
$gsent->bindParam(1$colorPDO::PARAM_STR|PDO::PARAM_INPUT_OUTPUT12);
$gsent->execute();
print(
"Después de hacer puré la fruta, el color es: $color");
?>

Ver también

add a note add a note

User Contributed Notes 17 notes

up
39
Vili
4 years ago
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);
}
?>
up
30
atrandafirc at yahoo dot com
3 years ago
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);
?>
up
14
Steve M
4 years ago
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"
up
4
khkiley at adamsautomation dot com
1 year ago
SQL Server 2008 R2

If this was in the documentation, I didn't stumble across it. When using bound output parameters with a stored procedure, the output parameters are updated AFTER the LAST rowset has been processed.

If your stored procedure does not return any rowsets (no SELECT statements) then you are set, your output parameters will be ready as soon as the stored procedure is processed.

Otherwise you need to process the rows, and then:
<?php $stmt->nextRowset(); ?>

Once that is done for each returning rowset you will have access to the output parameters.
up
6
flannell
1 year ago
Spent all day banging my head against a brick wall.

Tried to use INOUT or OUT and getting the return variable into PHP using Mysql v5.5.16 on XAMPP.

"MySQL doesn't supporting binding output parameters via its C API.  You must use SQL level variables:"

<?php
$stm
= $db->prepare("CALL sp_mysp(:Name, :Email, @sp_result)");

$outputArray = $db->query("select @sp_result")->fetch(PDO::FETCH_ASSOC);
?>

So the 'workaround' for Mysql and PDO is to use two SQL calls.

Hope this helps someone.
up
2
Anonymous
8 years ago
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.
up
1
Mike Robinson
1 year ago
Note that with bindParam the second parameter is passed by reference. This means that the following will produce a warning if E_STRICT is enabled:

<?php
$stmt
->bindParam('type', $object->getType());

// Strict Standards: Only variables should be passed by reference in /path/to/file.php on line 123
?>

If the second parameter is not an actual variable, either set the result of $object->getType(); to a variable and use that variable in bindParam or use bindValue instead.
up
2
cyrylas at gmail dot com
3 years ago
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';
?>
up
0
Ofir Attia
6 days ago
/*
   method for pdo class connection, you can add your cases by    yourself and use it.
*/
class Conn{
....
....
private $stmt;
public function bind($parameter, $value, $var_type = null){
        if (is_null($var_type)) {
            switch (true) {
                               case is_bool($value):
                    $var_type = PDO::PARAM_BOOL;
                    break;
                case is_int($value):
                    $var_type = PDO::PARAM_INT;
                    break;
                case is_null($value):
                    $var_type = PDO::PARAM_NULL;
                    break;
                default:
                    $var_type = PDO::PARAM_STR;
            }
        }
        $this->stmt->bindValue($parameter, $value, $var_type);
    }
up
0
pegas1981 at yandex dot ru
7 months ago
http://technet.microsoft.com/en-us/library/ff628166(v=sql.105).aspx

When binding null data to server columns of type varbinary, binary, or varbinary(max) you should specify binary encoding (PDO::SQLSRV_ENCODING_BINARY) using the $driver_options. See Constants for more information about encoding constants.
Support for PDO was added in version 2.0 of the Microsoft Drivers for PHP for SQL Server.

<?php
$db
= new PDO('sqlsrv:server=SQLSERVERNAME;Database=own_exchange', 'user', 'password');
$sql = "INSERT INTO dbo.files(file_name, file_source) VALUES(:file_name, :file_source)";
$stmt = $db->prepare($sql);
$stmt->bindParam(":file_name", $files->name, PDO::PARAM_STR);
$stmt->bindParam(":file_source", file_get_contents($files->tempName), PDO::PARAM_LOB, 0, PDO::SQLSRV_ENCODING_BINARY);
$stmt->execute();
?>
up
2
jeffwa+php at gmail dot com
7 years ago
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.
up
1
geompse at gmail dot com
4 years ago
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.
up
1
dhammari at q90 dot com
5 years ago
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
up
2
Filofox
8 years ago
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.
up
0
ReK_
3 years ago
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.
up
1
willie at spenlen dot com
7 years ago
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.
up
-4
sergiy dot sokolenko at gmail dot com
4 years ago
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
To Top