bindParam in a loop always writes the last value

Question

I am having trouble updating an array of data in nested foreach loops. I have read many similar issues on SO but non using a nested foreach (see my array schema) and the answer seems to be using the 'by reference' operator '&' on the value - but this is not working for me. Ive tried using the 'by ref' operator on the outer loop var too since it kind of seems logical but to no avail. Here's my code and array dumps :

The input array schema:

array (
  0 => 
  array (
    'ReviewDate' => '2016-05-08 00:00:00',
    'ReviewDetails' => 'Release pending',
    'RentAmount' => '112000.00',
    'ReviewComplete' => 1,
  ),
  1 => 
  array (
    'ReviewDate' => '2022-10-25 00:00:00',
    'ReviewDetails' => 'Short-hold still open',
    'RentAmount' => '21000.00',
    'ReviewComplete' => 0,
  ),
)

This is my prepared statement:

'UPDATE rentals SET `ReviewDate`=:ReviewDate, `ReviewDetails`=:ReviewDetails, `RentAmount`=:RentAmount, `ReviewComplete`=:ReviewComplete WHERE agreemID=14'

My code is:

try 
{
    $cnx->beginTransaction();
    $stmt = $dbconx->prepare( $QryStr );

    foreach ( $formArr as $tbl => $rec ) 
    {
        foreach ( $rec as $key => &$val ) {
            $stmt->bindParam( $key, $val );
        }
    }
    $stmt->execute();

    $cnx->commit(); 
    return $stmt->rowCount();
}   
catch ( PDOException $e ) 
{
    $cnx->rollBack();  
    $this->applog->logerr( __FUNCTION__ ." - ".$e->getMessage() );
    return false;
}

The DB table is update as follows - as you can see records are being overwritten with the final statement:

array (
  0 => 
  array (
    'ReviewDate' => '2022-10-25 00:00:00',
    'ReviewDetails' => 'Short-hold still open',
    'RentAmount' => '21000.00',
    'ReviewComplete' => 0,
  ),
  1 => 
  array (
    'ReviewDate' => '2022-10-25 00:00:00',
    'ReviewDetails' => 'Short-hold still open',
    'RentAmount' => '21000.00',
    'ReviewComplete' => 0,
  ),
)

This is precisely what i was getting before using the '&' by reference char - but its not working for me and i'm getting no errors in my log file. My connection is good as i'm using it everywhere else. Do i have to write out each binding separately? I'm trying to avoid doing that because i have over 30 similar tables to update. Any help appreciated

Use [`bindValue`](http://php.net/manual/en/pdostatement.bindvalue.php) instead to avoid such oddities. One of the variables/arrays is "not fresh" each loop (and a proper answer should explain exactly which one/why); However, `bindValue` avoids the issue entirely as it is 'that value' supplied which is used: no aliasing / references. — user2864740, Jul 14 '18 at 18:03
FWIW, from [`bindParam`](http://php.net/manual/en/pdostatement.bindparam.php): "Unlike PDOStatement::bindValue(), *the [bindParam] variable is bound as a reference and will only be evaluated at the time that PDOStatement::execute() is called.*" — user2864740, Jul 14 '18 at 18:09
https://stackoverflow.com/questions/49677752/pdo-bindparam-not-working-in-loop?rq=1 , https://stackoverflow.com/questions/1179874/what-is-the-difference-between-bindparam-and-bindvalue?rq=1 , https://stackoverflow.com/questions/26164076/pdo-bindparam-not-working-in-foreach?noredirect=1&lq=1 — user2864740, Jul 14 '18 at 18:11
Possible duplicate of [PDO bindParam not working in foreach](https://stackoverflow.com/questions/26164076/pdo-bindparam-not-working-in-foreach) — trincot, Jul 14 '18 at 18:35
@user2864740 - tried this but its still doing the same thing having used bindValue without the '&' reference op (also tried it with '&') but still getting duplicate data in each record. I ting looping is unpredictable since its worked for other users but not everyone. Maybe its environment dependent.. Guess ill have to hand-code each bind statement. — Flim Flam, Jul 14 '18 at 18:51
@trincot - the thread you linked to is using non-named placeholders (we can assume since he hasn't shown us) but since hes using an incremented loop counter var, then hes using the position to match the column name. This doesn't work for me either. I'm trying to do it dynamically without hand-balling out each 'bind' statement - however i think i need the PDO::PARAM_ statement other wise everything is being inserted as a string. — Flim Flam, Jul 14 '18 at 19:04

bindParam in a loop always writes the last value

0 Answers0