Got an odd one. Just started working on a new project. Currently working on a user class. The user class will have a save function where it updates the relevant tables related to the user account. The function looks like this: function save() { global $pdo; $sql = 'UPDATE user_account SET '; $counter = 0; foreach ( $this->details as $key=>$field) { if ( $key != 'ua_id') { if ($counter > 0 ) { $sql .= ', '; } $sql .= ''. $key.'=:'.$key; $counter++; } } $sql .= ' WHERE ua_id=:ua_id LIMIT 1'; $stmt =$pdo->prepare($sql); foreach ( $this->details as $key=>$field) { if ($key == 'ua_id') { $field = (int)$field; } $stmt->bindParam(':'.$key,$field); } $stmt->execute(); } PHP: $this->details is basically an array of all the fields of the user_account table. The problem that I have, is that the query executes but no row is affected so no changes are made. $stmt->errorInfo(); shows no error. When I copy the query generated in the save function and then replace the placeholders with the values, it works perfectly fine. I know it will be a small mistake that I made but I can't tell where. Any suggestion what I can try?
hmm interesting that if I don't use bindParam for the ua_id and put it like this $sql .= ' WHERE ua_id='.$this->details['ua_id'].' LIMIT 1'; PHP: then it all works fine. Any idea why that is?
This seems simpler $sql = 'UPDATE `user_account` SET '; $keys = array_keys($this->details); $counter = count($keys) - (in_array('ua_id', $keys)? 1:0); $sql .= implode(', ', array_map( function ($k) { return sprintf("%s = :%s", $k, $k); }, array_keys($this->details) )); $sql .= ' WHERE ua_id=:ua_id LIMIT 1'; // if ua_id isn't in $this->details then we have to find it and add it or the update won't work $stmt =$pdo->prepare($sql); $stmt->execute($this->details); PHP:
I did a var_dump and it was an integer. I changed bindParam to bindValue and it seemed to have fixed the issue. What id the actual difference between the two?
This explains it pretty well: https://stackoverflow.com/questions/1179874/what-is-the-difference-between-bindparam-and-bindvalue https://www.php.net/manual/en/pdostatement.bindparam.php
@malky66 So in his original example code, he is binding everything to $field meaning, all fields in his table got updated with one single value? The last value of $field
Yeah that what I think happened. Echoing out $field while I was looping over them wouldn't have helped as it would have bound it to the last value of field.
I find this code... troubling. One of the whole reasons mysql_ functions went the way of the dodo was to make us STOP slopping variables into query strings.Providing the fields dynamically like this is just begging for trouble and is something I would HIGHLY advise against. PLEASE tell me $this->details isn't coming from client-side!
Prepared statements should be used for absolutely every query, no matter where the source of the data being used is. Can you guarantee that a source of data will never be from a user? I know I couldn't. By using prepared statements with absolutely every query, you don't risk accidentally creating a security risk if the source of data for a query gets changed to user submitted data at a later date