Binding params for PDO statement inside a loop doesn't work

Question

I'd like to insert data to my database using bindParam.

Here is a shorter version of my code:

$reindex['f_name'] = 'Tom';
$reindex['l_name'] = 'Riddle';
$reindex['date'] = '2020-12-12';

$sql = "INSERT INTO tbl_user (f_name, l_name, date) VALUES (:f_name, :l_name, :date)";
$stmt = $pdo->prepare($sql);

foreach ($reindex as $key => $value) {
    echo $key . '<br>'; // look at output
    echo $value . '<br><br>';
    $stmt->bindParam($key, $value);
}

$stmt->execute();

Output of echo:

f_name
Tom

l_name
Riddle

I don't get any error messages. I set error reporting like this:

ini_set('display_errors', 1);
ini_set('display_startup_errors', 1);
error_reporting(E_ALL);

This would work but I have to define every bind separately:

$stmt->execute([
    'f_name' => $reindex['f_name'],
    'l_name' => $reindex['l_name'],
    'date' => $reindex['date']
]);

Answer 1

If you apply this from the PDO manual

PDOStatement::bindParam

Binds a PHP variable to a corresponding named or question mark placeholder in the SQL statement that was used to prepare the statement. Unlike PDOStatement::bindValue(), the variable is bound as a reference and will only be evaluated at the time that PDOStatement::execute() is called.

You will understand that in your loop you are using the same variable X times. Overwriting it each time round the loop.

So when the actual binding is done, at the time you ->execute() your prepared query, you only have one value, the last from your loop in those variables