PHP: What is the difference between bindParam () and bindValue () in PDO?

Question:

Actually I am not clear what is the difference between the bindParam() and bindValue() methods bindParam() making queries to my database using PDO.

I would like to understand the difference, if possible with some illustrative example.

And, without going into the field of opinions, to know if each one exists for a specific use or if, on the contrary, they can be used interchangeably.

Answer:

PHP's own documentation explains the difference on the PDOStatement::bindParam() :

Unlike PDOStatement::bindValue() , the variable is bound as a reference and will only be evaluated when PDOStatement::execute() .

That is, in PDOStatement::bindParam() a reference of the variable (a "pointer" PDOStatement::bindParam() is passed so that if you change its value before an execute() , it will make the substitution with the value that said variables have in the moment of calling execute() , taking into account those latest modifications.

In PDOStatement::bindValue() , the current value of the variable is assigned and although it changes over several executions of execute() the substitution remains unchanged.

Both protect equally from SQL injection and have exactly the same internal behavior after substitutions are resolved.


Illustrative example with PDOStatement::bindValue() :

$consulta = $db->prepare('SELECT * FROM `tabla` WHERE ejemplo = :ejemplo');

$ejemplo = 'un_ejemplo';
/* Aquí es donde se tiene en cuenta el valor de $ejemplo: un_ejemplo */
$consulta->bindValue(':ejemplo', $ejemplo);
$ejemplo = 'otro_ejemplo';

$consulta->execute();

The query executes SELECT * FROM tabla WHERE ejemplo = 'un_ejemplo' because the value of :ejemplo with the content of the variable $ejemplo , which is "an_example" at the time bindValue() executed.

Illustrative example with PDOStatement::bindParam() :

$consulta = $db->prepare('SELECT * FROM `tabla` WHERE ejemplo = :ejemplo');

$ejemplo = 'un_ejemplo';
$consulta->bindParam(':ejemplo', $ejemplo);
$ejemplo = 'otro_ejemplo';

/* Aquí es donde se tiene en cuenta el valor de $ejemplo: otro_ejemplo */
$consulta->execute();

The query executes SELECT * FROM tabla WHERE ejemplo = 'otro_ejemplo' because the value of :ejemplo is NOT set as in the previous example, but it obtains the value of the variable $ejemplo when execute() is executed, and at that moment the value is "other_example".


Illustrative example with PDOStatement::bindValue() (2):

$consulta = $db->prepare('INSERT INTO tabla (valor) VALUES (:valor)');

$ejemplo = [ 'a', 'b', 'c', 'd' ];
foreach ($ejemplo as $valor) {
  $consulta->bindValue(':valor', $valor);
  $consulta->execute();
}

For each iteration we have to do a bindValue() and an execute() .

Illustrative example with PDOStatement::bindParam() (2):

$consulta = $db->prepare('INSERT INTO tabla (valor) VALUES (:valor)');

$ejemplo = [ 'a', 'b', 'c', 'd' ];
$valor = ''; /* No es necesario, pero es ilustrativo */
$consulta->bindParam(':valor', $valor);
foreach ($ejemplo as $valor) {
  $consulta->execute();
}

As we have saved the reference to the $valor variable, it is not necessary to call bindParam() before each execute() .

Scroll to Top