PDOStatement::bindParam

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

PDOStatement::bindParam — Bindet einen Parameter an den angegebenen Variablennamen

Beschreibung

public PDOStatement::bindParam(
    string|int $param,
    mixed &$var,
    int $type = PDO::PARAM_STR,
    int $maxLength = 0,
    mixed $driverOptions = null
): bool

Bindet eine PHP-Variable an den entsprechenden benannten oder durch ein Fragezeichen gekennzeichneten Platzhalter der vorbereiteten SQL-Anweisung. Im Gegensatz zu PDOStatement::bindValue() wird die Variable als Referenz gebunden und wird erst ausgewertet, wenn PDOStatement::execute() aufgerufen wird.

Meistens handelt es sich bei den Parametern um Eingabeparameter, d. h. um Parameter, die nur lesend verwendet werden, um die Abfrage zu erstellen (sie können aber dennoch nach type umgewandelt werden). Einige Treiber ermöglichen jedoch die Ausführung gespeicherter Prozeduren, die Daten über Ausgabeparameter zurückgeben können. Solche Parameter werden häufig gleichzeitig als Ein- und Ausgabeparameter verwendet, die sowohl Daten senden als auch aktualisiert werden, um sie zu empfangen.

Parameter-Liste

param: Der Bezeichner des Parameters. Bei einer vorbereiteten Anweisung, die benannte Platzhalter verwendet, ist dies ein Parametername der Form :name. Bei einer vorbereiteten Anweisung mit Fragezeichen-Platzhaltern ist dies die Position des Parameters, beginnend mit 1 (1-indiziert).
var: Der Name der PHP-Variable, die an den Parameter der SQL-Anweisung gebunden werden soll.
type: Ein expliziter Datentyp für den Parameter, der durch eine der PDO::PARAM_*-Konstanten angegeben wird. Soll ein INOUT-Parameter von einer gespeicherten Prozedur zurückgegeben werden, muss der bitweise OR-Operator verwendet werden, um die PDO::PARAM_INPUT_OUTPUT-Bits für den Parameter type zu setzen.
maxLength: Die Länge des Datentyps. Um anzuzeigen, dass ein Parameter ein OUT-Parameter einer gespeicherten Prozedur ist, muss die Länge explizit angegeben werden; nur sinnvoll, wenn der Parameter type PDO::PARAM_INPUT_OUTPUT ist.
driverOptions

Rückgabewerte

Gibt bei Erfolg true zurück. Bei einem Fehler wird false zurückgegeben.

Fehler/Exceptions

Gibt einen Fehler der Stufe E_WARNING aus, wenn das Attribut PDO::ATTR_ERRMODE auf PDO::ERRMODE_WARNING gesetzt ist.

Löst eine PDOException aus, wenn das Attribut PDO::ATTR_ERRMODE auf PDO::ERRMODE_EXCEPTION gesetzt ist.

Beispiele

Beispiel #1 Ausführen einer vorbereiteten Anweisung mit benannten Platzhaltern

<?php
/* Ausführen einer vorbereiteten Anweisung durch Binden von PHP-Variablen */
$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);
/* Den Namen kann auch ein Doppelpunkt ":" vorangestellt werden (optional) */
$sth->bindParam(':colour', $colour, PDO::PARAM_STR);
$sth->execute();
?>

Beispiel #2 Ausführen einer vorbereiteten Anweisung mit Fragezeichen-Platzhaltern

<?php
/* Ausführen einer vorbereiteten Anweisung durch Binden von PHP-Variablen */
$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);
$sth->execute();
?>

Beispiel #3 Aufrufen einer gespeicherten Prozedur mit einem INOUT-Parameter

<?php
/* Aufrufen einer gespeicherten Prozedur mit einem INOUT-Parameter */
$colour = 'rot';
$sth = $dbh->prepare('CALL puree_fruit(?)');
$sth->bindParam(1, $colour, PDO::PARAM_STR|PDO::PARAM_INPUT_OUTPUT, 12);
$sth->execute();
print "Nach dem Pürieren der Früchte, ist die Farbe: $colour";
?>

Siehe auch

PDO::prepare() - Bereitet eine Anweisung zur Ausführung vor und liefert ein Anweisungsobjekt
PDOStatement::execute() - Führt ein Prepared Statement aus
PDOStatement::bindValue() - Bindet einen Wert an einen Parameter

Improve This Page

Learn How To Improve This Page • Submit a Pull Request • Report a Bug

＋add a note

User Contributed Notes 23 notes

down

247

atrandafirc at yahoo dot com ¶

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

down

178

Vili ¶

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

down

jcastromail at yahoo dot es ¶

2 years ago

PHP 8.1 changed the way how this method works.

Now, the fourth method must not be null. It could be 0 (for an int) but it could not be null, otherwise, it will raise a deprecated warning.

old:
$sth->bindParam('calories', $calories, PDO::PARAM_INT);
new:
$sth->bindParam('calories', $calories, PDO::PARAM_INT,0);

down

Steve M ¶

14 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"

down

pegas1981 at yandex dot ru ¶

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

down

dhammari at q90 dot com ¶

15 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

down

khkiley at adamsautomation dot com ¶

12 years 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.

down

cyrylas at gmail dot com ¶

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

down

Anonymous ¶

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

down

Mike Robinson ¶

11 years 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.

down

Filofox ¶

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

down

jeffwa+php at gmail dot com ¶

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

down

geompse at gmail dot com ¶

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

down

Ofir Attia ¶

10 years 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);
    }

down

heather dot begazo at gmail dot com ¶

9 years ago

The documentation says this about the length parameter for bindParam:  

"To indicate that a parameter is an OUT parameter from a stored procedure, you must explicitly set the length. "

For db2, I found that setting the length for the "INPUT_OUTPUT" parameters causes a problem for varchar parameters that are input parameters.  The problem I found is that the stored procedure was called, but varchar input parameters were set to null inside my stored procedure and as a result, the stored procedure could not work properly.  

Here is the signature for my stored procedure:

CREATE OR REPLACE PROCEDURE MY_SCHEMA_NAME.MY_STORED_PROCEDURE_NAME ( IN RUN_ID INTEGER,IN V_SCHEMA_NAME VARCHAR(128),
  OUT out_rc INTEGER,OUT out_err_message VARCHAR(100),OUT out_sqlstate CHAR(5) ,OUT out_sqlcode INT)

Here is the php code that works:

$command = "Call MY_SCHEMA_NAME.MY_STORED_PROCEDURE_NAME (?,?,?,?,?,?,?)";
$stmt = $this->GuestDb->prepare($command);
$stmt->bindParam(1, $RUN_ID, PDO::PARAM_INT); 
$stmt->bindParam(2, $V_SCHEMA_NAME, PDO::PARAM_STR);
$stmt->bindParam(3, $V_TABNAME, PDO::PARAM_STR);
$stmt->bindParam(4, $out_rc, PDO::PARAM_INT|PDO::PARAM_INPUT_OUTPUT);
$stmt->bindParam(5, $out_err_message, PDO::PARAM_STR|PDO::PARAM_INPUT_OUTPUT);
$stmt->bindParam(6, $out_sqlstate, PDO::PARAM_STR|PDO::PARAM_INPUT_OUTPUT);
$stmt->bindParam(7, $out_sqlcode, PDO::PARAM_INT|PDO::PARAM_INPUT_OUTPUT);

Here is the php code that does not work:

$command = "Call MY_SCHEMA_NAME.MY_STORED_PROCEDURE_NAME (?,?,?,?,?,?,?)";
$stmt = $this->GuestDb->prepare($command);
$stmt->bindParam(1, $RUN_ID, PDO::PARAM_INT,12); 
$stmt->bindParam(2, $V_SCHEMA_NAME, PDO::PARAM_STR,128);
$stmt->bindParam(3, $V_TABNAME, PDO::PARAM_STR,100);
$stmt->bindParam(4, $out_rc, PDO::PARAM_INT|PDO::PARAM_INPUT_OUTPUT,12);
$stmt->bindParam(5, $out_err_message, PDO::PARAM_STR|PDO::PARAM_INPUT_OUTPUT,100);
$stmt->bindParam(6, $out_sqlstate, PDO::PARAM_STR|PDO::PARAM_INPUT_OUTPUT,6);
$stmt->bindParam(7, $out_sqlcode, PDO::PARAM_INT|PDO::PARAM_INPUT_OUTPUT,12);

down

gilhildebrand at gmail dot com ¶

9 years ago

MySQL will return an error if a named placeholder has a hyphen in it:
UPDATE wardrobe SET `T-Shirt`=:T-SHIRT WHERE id=:id

Will return the following error: PDOException' with message 'SQLSTATE[HY093]: Invalid parameter number: parameter was not defined'

To resolve, just remove hyphens from named placeholders:
UPDATE wardrobe SET `T-Shirt`=:TSHIRT WHERE id=:id

down

Vladimir Kovpak ¶

9 years ago

<?php
/**
 * Bind bit value:
 */

$sql = 'SELECT * FROM myTable WHERE level & ?';
$sth = \App::pdo()->prepare($sql);
$sth->bindValue(1, 0b0101, \PDO::PARAM_INT);
$sth->execute();
$result = $sth->fetchAll(\PDO::FETCH_ASSOC);

down

flannell ¶

12 years 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.

down

Ejaz Ansari ¶

8 years ago

For those who are confused on insert query using PDO-bindparam:

$sql = $db->prepare("INSERT INTO db_fruit (id, type, colour) VALUES (? ,? ,?)");

$sql->bindParam(1, $newId);
$sql->bindParam(2, $name);
$sql->bindParam(3, $colour);
$sql->execute();

down

marco dot marsala at live dot it ¶

11 months ago

Examples doesn't highlight that this works as expected:

<?php
/* Execute a prepared statement by binding PHP variables */
$sth = $dbh->prepare('SELECT name, colour, calories
    FROM fruit
    WHERE calories < :calories AND colour = :colour');
$sth->bindParam('calories', $calories, PDO::PARAM_INT);
/* Names can be prefixed with colons ":" too (optional) */
$sth->bindParam(':colour', $colour, PDO::PARAM_STR);
$calories = 150;
$colour = 'red';
$sth->execute();
?>

calories get set to 150 in db, colour get set to red in db, because, as documentation points out: "the variable is bound as a reference and will only be evaluated at the time that PDOStatement::execute() is called."

down

weronika dot nowak dot code at gmail dot com ¶

2 years ago

🧨🧨🧨
<?php
$q = "SELECT id, name FROM test WHERE name like '%:foo%'";
$s = "carrot";
$sth = $dbh->prepare($q);
$sth->bindParam(':foo', $s);
$sth->execute();
?>

It's true, this ⬆ won't work but you can use concatenation ⬇:

<?php
$q = "SELECT id, name FROM test WHERE name like '%' || 
:foo || '%' ";
$s = "carrot";
$sth = $dbh->prepare($q);
$sth->bindParam(':foo', $s);
$sth->execute();
?>

down

willie at spenlen dot com ¶

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

down

-2

Tristen Edwin ¶

6 years ago

Here's how to build a dynamic WHERE LIKE at run time when the user can submit n keywords

<?php

if (array_key_exists('storyPieces', $_POST)) {
    $story_pieces = explode(',' $_POST['storyPieces']);
        foreach($story_pieces as $piece) {
        if ($conditional_count == 0) {
            $where_clause .= 'story LIKE ? ';
            $conditional_count++;
       } else {
            $where_clause .= 'AND story LIKE ? ';
            $conditional_count++;
       }
       }
}

// then after you've prepared

if (isset($story_pieces)) {
    foreach($story_pieces as $key => &$piece) {
        $piece = "%" . $piece . "%";
        $sth->bindParam($key + 1, $piece, PDO::PARAM_STR);
    }
}

?>

＋add a note