I am converting from mysqli to PDO and very much a beginner with this. Here is my update statement for my database 'users'
public function pdo_update_test() {
$sql = "UPDATE users SET visible_password = ?, hashed_password = ?, ";
$sql .="temp_hashed_password = ?, email = ?, first_name= ?, last_name = ?, ";
$sql .="position = ?, location = ?, city = ?, country = ?, institution = ? ";
$sql .="interests = ?, profile_comment = ? WHERE id =" . $this->id;
$query = $handler->prepare($sql);
$result = array($visible_password, $hashed_password, $temp_hashed_password, $email,
$first_name, $last_name, $position, $location, $city, $country, $institution,
$interests, $profile_comment);
$query->execute($result);
if (($query = $handler->prepare($sql)) === false) {
print_r($handler->errorInfo());
}
if ($query->execute($result) === false) {
print_r($query->errorInfo());
}
}
I am using ? rather than nameholders because once I have this working I am going to try to make it abstract so I can use it in all the classes in my site and I have found it easier with ? than nameholders. When I run the following it fails to work. I am sure an obvious error on my part but I can't seem to see the issue....
$user = new User();
$user->id= 256;
$visible_password = "Bob";
$user->pdo_update_test();
I have found a solution to make the whole thing dynamic. I won't presume that its going to be helpful for others (as I am the beginner) but i though I would post it anyway....
If you see problems or have criticisms please let me know
public function pdo_update_test(){
$attributes = $this->attributes();
$attribute_pairs = array();
foreach($attributes as $key => $value) {
if(isset($value))
$attribute_pairs[] = "{$key}='{$value}'";
}
$sql = "UPDATE ".self::$table_name." SET ";
$sql .= join(", ", $attribute_pairs);
$sql .= " WHERE id=". $this->id;
$query = $handler->prepare($sql);
$query->execute(array());
}