Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 3 of 3
  1. #1
    Senior Coder djm0219's Avatar
    Join Date
    Aug 2003
    Location
    Wake Forest, North Carolina
    Posts
    1,285
    Thanks
    4
    Thanked 201 Times in 198 Posts

    PDO Update or Binding Problem

    I have been moving a 10+ year old application from MySQL to PDO and have just run into a problem with binding parameters that is driving me crazy. It is probably going to be staring me in the face but I can't see it for the life of me.

    To avoid blindly updating every column in a table I keep track of what has actually be modified, build up the appropriate SQL statement keeping track of the column names that have changed along with their values then binding the values after the SQL statement has been built.

    The problem I am running into is that if more than one field (column) is updated the very first column takes on the value of the last column. If a single column is updated there is not a problem.

    All of the binds are being done using bindParam and the integer value for an explicit data type. Some of the columns use an underscore (_) and some contain a number. I have eliminated both of those as possible reasons for the problem I am seeing.

    I am using PHP 5.4.17 and have searched the bugs to see if there might be anything remotely related to what I am seeing with no luck at all.

    Here is the built up SQL statement right before the PDO Prepare is done

    PHP Code:
    UPDATE `repm_usersSET `EMail` = :EMail , `Middle_Initial` = :Middle_Initial , `Address1` = :Address1 WHERE `UserID` = :UserID LIMIT 1 
    As you can see all of the parameters to be bound are named. In the update example above the values bound were:

    EMail - deb@deb.com
    Middle_Initial - E
    Address1 - Po Box 818
    UserID - 1336
    After update the Middle_Initial column contains the first letter of the Address1 column which is a P. The rest of the columns contain the correct values. PDO debug shows that everything seems to be getting sent correctly though the debug information does not include the actual values so I can't be 100% sure what values are making it to the PDO Execute.

    The code that builds up the SQL statement and stashes the columns to be updated and their values:

    PHP Code:
    //                                                                            
    // If we get here there is a field on the form that needs to be updated       
    // so add the appropriate values to the command string we are building        
    // up.                                                                        
    //                                                                            
                                                                                  
    $UpSQL .= '`' $Field['Field_Name'] . '` = :' $Field['Field_Name'] . ' , ';
    $upfields[$Field['Field_Name']] = $AgentDetails[$Field['Field_Name']]; 
    The code that does the binds:

    PHP Code:
    //                                                                               
    // We need to create the bindings for all of the fields that are being updated.  
    // We kept track of the field names that changed so we will be able to create    
    // all of the bindings correctly.                                                
    //                                                                               
                                                                                     
    foreach ($upfields as $field=>$fvalue) {                                         
                                                                                                            
        
    $AgentUp->bindParam(':' $field,$fvalue,$utilities->repm_PDO_bind($fvalue));
                                                                                     
    }  
    // end of foreach through each of the fields being updated                    
                                                                                     
    $AgentUp->bindParam(':UserID',$UserID,$utilities->repm_PDO_bind($UserID)); 
    Although it should not matter I moved the last named parameter in the SQL so it was also the last bind to be done. That change did not make a difference either.

    The PDO debug output is:

    Code:
    SQL: [135] UPDATE `repm_users` SET `EMail` = :EMail , `Middle_Initial` = :Middle_Initial , `Address1` = :Address1 WHERE `UserID` = :UserID LIMIT 1
    Params:  4
    Key: Name: [7] :UserID
    paramno=-1
    name=[7] ":UserID"
    is_param=1
    param_type=1
    Key: Name: [6] :EMail
    paramno=-1
    name=[6] ":EMail"
    is_param=1
    param_type=2
    Key: Name: [15] :Middle_Initial
    paramno=-1
    name=[15] ":Middle_Initial"
    is_param=1
    param_type=2
    Key: Name: [9] :Address1
    paramno=-1
    name=[9] ":Address1"
    is_param=1
    param_type=2
    Last edited by djm0219; 09-12-2013 at 08:45 PM.
    Dave .... HostMonster for all of your hosting needs

  • #2
    Senior Coder CFMaBiSmAd's Avatar
    Join Date
    Oct 2006
    Location
    Denver, Colorado USA
    Posts
    2,958
    Thanks
    2
    Thanked 304 Times in 296 Posts
    The second parameter in bindParam is a reference to where the data is at and is evaluated when the ->execute() method is called. All the references are to $fvalue and the last one will be used for all values.

    If you need these to be variables, rather than values, i.e. you are going to use the prepared query more than one time with different sets of values, you need to pass a reference to the element in the $upfields array where the data is actually stored, or just use bindValue if these are one-time/one-use values.
    If you are learning PHP, developing PHP code, or debugging PHP code, do yourself a favor and check your web server log for errors and/or turn on full PHP error reporting in php.ini or in a .htaccess file to get PHP to help you.

  • Users who have thanked CFMaBiSmAd for this post:

    djm0219 (09-12-2013)

  • #3
    Senior Coder djm0219's Avatar
    Join Date
    Aug 2003
    Location
    Wake Forest, North Carolina
    Posts
    1,285
    Thanks
    4
    Thanked 201 Times in 198 Posts
    Son of a <insert expletive here>. Simply changing the bindparam to bindvalue worked as desired. Sigh, I knew it was going to be simple and obvious Thank you!
    Dave .... HostMonster for all of your hosting needs


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •