bindParam and bindValue

bindValue(),

• Unlike PDOStatement::bindValue(), the variable is bound as a reference and will only be evaluated at the time that PDOStatement::execute() is called.

bindParam()

• call PDOStatement::bindParam() to bind PHP variables to the parameter markers: bound variables pass their value as input and receive the output value, if any, of their associated parameter markers

PHP PDOStatement: bindParam vs bindValue

• It is an ordinary place to repeatedly execute a query, with each iteration using different parameters.
• However, using the conventional query() method and a looping mechanism comes at a cost of both overhead, because of the repeated parsing of more or less identical query for validity, and coding convenience, and the need to repeatedly reconfigure the query using the new values for each iteration.
• Using prepared statement eliminate this problem. It is easy to accomplish in PHP using the PDOStatement class.
• The bindValue and bindParam PDOStatement methods are both use in binding a variable to a parameter (name or question mark placeholder use in SQL statement).
• The binds a parameter exclusively to a specified variable name which is bindParam.
• Bound as a reference while the binds a value which could be a variable, bindValue.
• An integer or string to a parameter.

To make the difference clearer, we can see the code examples:

• Both bind a variable to a placeholder’s parameter.

• In Both bindParam and bindValue, a variable can be binded to a parameter.

< ?php
$name = 'collins';
$sql = 'INSERT INTO feeds (name) VALUES (:name)';
$update = $conn -> prepare($sql);
$update -> bindParam(':name', $name);
$update -> execute();

< ?php
$name = 'collins';
$sql = 'INSERT INTO feeds (name) VALUES (:name)';
$update = $conn -> prepare($sql);
$update -> bindValue(':name', $name);
$update -> execute();

• bindParam binds variable as a reference.
• For example, when a variable is assigned as a reference to another variable, a change in the value of the variable assigned as a reference also affect the parent variable.
• For creating reference, the & is used

Example

$a = 'good boy';
$b = &$a;  // $a and $b both equal "good boy"
$b = "bad boy"; // $a and $b both equal "bad boy"
echo "$a"; // echos "bad boy"

• While checking bindParam description, you could see that the method’s second argument accepts a variable that is pass as a reference and will only be evaluated at the time that the execute() is called.

Thus, we can still change the value of a variable bonded to a parameter even after the bindParam() method had been called like so:

$name = 'collins';
$sql = 'INSERT INTO feeds (name) VALUES (:name)';
$update = $conn -> prepare($sql);
$update -> bindParam(':name', $name);
$name = 'john';
$update -> execute(); // execute with john inserted into the column "name"

• Above code will bind ‘john’ to the :name parameter because, as we mentioned, variable are binded by reference when using bindParam.
• “bindValue” accept different data types.
• Unlike the bindParam() that binds only a variable name to a parameter, with bindValue, you can bind a variable and also an integer, float, and string.

Binding an integer to a parameter via “bindValue”.

< ?php
$sql = 'INSERT INTO feeds (numbers) VALUES (:number)';
$update = $conn -> prepare($sql);
$update -> bindValue(':number', 100);
$update -> execute();

Binding a string to a parameter via “bindValue”.

< ?php
$sql = 'INSERT INTO feeds (names) VALUES (:name)';
$update = $conn -> prepare($sql);
$update -> bindValue(':name', 'collins');
$update -> execute();

• While you are trying to bind say, a string or number (float / integer) to a parameter using bindParam, you’ll get this error:

“Fatal error: Cannot pass parameter 2 by reference”