PHP PDOStatement: bindParam vs bindValue

It’s commonplace to repeatedly execute a query, with each iteration using different parameters.
However, doing so using the conventional query() method and a looping mechanism comes at a cost of both overhead, because of the repeated parsing of the almost 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. Thankfully, 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).

Although they’re quite similar, they differ in how they work.

The bindParam binds a parameter exclusively to a specified variable name which is bound as a reference while the bindValue binds a value which could be a variable, an integer or string to a parameter.

Let’s see code examples to make the difference clearer.

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. The & is used in creating the reference.
E.g.
```
$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"
```
Taking a look at the description of “bindParam”, 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"
```
The code above will bind ‘john’ to the :name parameter because, like we mentioned, variable are binded by reference when using bindParam.
“bindValue” accept vary data types.
Unlike the bindParam() that binds only a variable name to a parameter, with bindValue, you can bind not just a variable but 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();
```
When you try 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

Conclusion

In this article, I illustrated to us the difference between bindParam and bindValue.
Have questions or contributions, let me know via comment. I’ll do my best to answer them.