What is the difference between PDOStatement::bindParam()
and PDOStatement::bindValue()
?
The simplest way to put this into perspective for memorization by behavior (in terms of PHP):
bindParam:
referencebindValue:
variableFor the most common purpose, you should use bindValue
.
bindParam
has two tricky or unexpected behaviors:
bindParam(':foo', 4, PDO::PARAM_INT)
does not work, as it requires passing a variable (as reference).bindParam(':foo', $value, PDO::PARAM_INT)
will change $value
to string after running execute()
. This, of course, can lead to subtle bugs that might be difficult to catch.Source: http://php.net/manual/en/pdostatement.bindparam.php#94711
From Prepared statements and stored procedures
Use bindParam
to insert multiple rows with one time binding:
<?php
$stmt = $dbh->prepare("INSERT INTO REGISTRY (name, value) VALUES (?, ?)");
$stmt->bindParam(1, $name);
$stmt->bindParam(2, $value);
// insert one row
$name = 'one';
$value = 1;
$stmt->execute();
// insert another row with different values
$name = 'two';
$value = 2;
$stmt->execute();
Here are some I can think about :
bindParam
, you can only pass variables ; not valuesbindValue
, you can pass both (values, obviously, and variables)bindParam
works only with variables because it allows parameters to be given as input/output, by "reference" (and a value is not a valid "reference" in PHP) : it is useful with drivers that (quoting the manual) : 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.
With some DB engines, stored procedures can have parameters that can be used for both input (giving a value from PHP to the procedure) and ouput (returning a value from the stored proc to PHP) ; to bind those parameters, you've got to use bindParam, and not bindValue.
You don't have to struggle any longer, when there exists a way lilke this:
$stmt = $pdo->prepare("SELECT * FROM someTable WHERE col = :val");
$stmt->execute([":val" => $bind]);
From the manual entry for PDOStatement::bindParam
:
[With
bindParam
] UnlikePDOStatement::bindValue()
, the variable is bound as a reference and will only be evaluated at the time thatPDOStatement::execute()
is called.
So, for example:
$sex = 'male';
$s = $dbh->prepare('SELECT name FROM students WHERE sex = :sex');
$s->bindParam(':sex', $sex); // use bindParam to bind the variable
$sex = 'female';
$s->execute(); // executed with WHERE sex = 'female'
or
$sex = 'male';
$s = $dbh->prepare('SELECT name FROM students WHERE sex = :sex');
$s->bindValue(':sex', $sex); // use bindValue to bind the variable's value
$sex = 'female';
$s->execute(); // executed with WHERE sex = 'male'
Source: Stackoverflow.com