The examples on this page assumes the use of the class and connection defined at the top of the Selecting Data page.
bindParamThe methods bindValue and
bindParam differ in only two important respects:
bindValue allows the a contant to be the bound
value; bindParam assigns its value to the prepared
SQL statement when the execute method is called,
while bindValue does so when it itself is
executed. For all of the examples on the Binding Values page the
bindValue method could be replaced with the
bindParam method without any difference in
execution. However, there are some cases where
bindParam provides some distinct advantages:
public function getKeywords( $values ) {
$sql = 'SELECT * FROM keyword WHERE keyword_id = ?';
$stmt = $this->conn->prepare( $sql );
$stmt->setFetchMode(PDO::FETCH_ASSOC);
foreach( $values as $val ) {
$stmt->bindValue( 1, $val );
$stmt->execute();
$result = $stmt->fetchAll();
print( $result[0] . PHP_EOL );
}
}
In this example the parameter $values is an
array containing a list of keyword IDs. The
foreach control structure loops through every
element of values, naming each individual element
$val. Inside this loop $val is bound
to the prepared statement and the statement is then executed.
The resulting values are printed. (Note that although each call
to this SQL statement produces only one row -
keyword_id is the primary key for the table - this
row is still numbered, in this case as row 0.)
This method is rewritten using bindParam
instead:
public function getKeywords( $values ) {
$sql = 'SELECT * FROM keyword WHERE keyword_id = ?';
$stmt = $this->conn->prepare( $sql );
$val = null;
$stmt->bindParam( 1, $val );
$stmt->setFetchMode(PDO::FETCH_ASSOC);
foreach( $values as $val ) {
$stmt->execute();
$result = $stmt->fetchAll();
print( $result[0] . PHP_EOL );
}
}
This version binds the value of $val to the
prepared SQL statement only when execute is
called. Only one call to bindParam is necessary
because as the value of $val is changed inside the
foreach loop the new value is automatically used
during the prepared SQL statement execution.
Which approach should you use? Whichever approach you are
comfortable with. bindValue works under all
circumstances, but bindParam can be more
convenient to write under certain circumstances.
Each placeholder in the SQL string must have a matching bound value. Having more or less numbers of bindings than placeholders throws an exception during execution.