What are the differences between bindParam and bindValue in PHP PDO prepared statements, and when should each be used?

bindParam and bindValue are both used to bind values to placeholders in PHP PDO prepared statements. The main difference between them is that bindParam binds the parameter by reference, meaning that any changes to the variable after binding will affect the value in the statement, while bindValue binds the parameter by value, meaning that the value is set at the time of binding and changes to the variable later will not affect the value in the statement. bindParam should be used when you want to bind a variable by reference and potentially modify it later, while bindValue should be used when you want to bind a variable by value and ensure that the value remains constant throughout the execution of the statement.

// Using bindParam
$value = 'example';
$stmt = $pdo->prepare('SELECT * FROM table WHERE column = :value');
$stmt->bindParam(':value', $value, PDO::PARAM_STR);
$value = 'new value'; // This will affect the bound value in the statement

// Using bindValue
$value = 'example';
$stmt = $pdo->prepare('SELECT * FROM table WHERE column = :value');
$stmt->bindValue(':value', $value, PDO::PARAM_STR);
$value = 'new value'; // This will not affect the bound value in the statement