Why Does PDO Throw a \'Cannot pass parameter by reference\' Error, and How Can I Fix It Using bindValue?

Avoiding the "Cannot pass parameter by reference" Error with bindParam

When working with PDO, you may encounter the puzzling error "Cannot pass parameter by reference" when using bindParam with a constant value like PDO::PARAM_NULL.

The reason for this error lies in the nature of bindParam. Unlike bindValue, which binds a value to a parameter at the time of calling, bindParam binds a variable by reference. This means that bindParam expects a variable that can be modified during query execution. Using a constant value like PDO::PARAM_NULL, which cannot be modified, triggers the error.

To resolve this issue, the solution is to use bindValue instead of bindParam for constant values. bindValue accepts a value as the second parameter and does not require a reference.

Here's an example using bindValue correctly:

$stmt = $dbh->prepare('INSERT INTO table(v1, v2, ...) VALUES(:v1, :v2, ...)');
$stmt->bindValue(':v1', null, PDO::PARAM_INT);

In this case, bindValue assigns the value null to the :v1 parameter with the data type PDO::PARAM_INT.

Note that while the PHP documentation suggests using bindValue(':param', null, PDO::PARAM_NULL), it may not work in all cases. Therefore, it is recommended to use PDO::PARAM_INT or the appropriate data type instead.

The above is the detailed content of Why Does PDO Throw a \'Cannot pass parameter by reference\' Error, and How Can I Fix It Using bindValue?. For more information, please follow other related articles on the PHP Chinese website!