How do you perform a dynamic php, PDO prepared statement Update?

Question

I'm having trouble finding good documentation on pdo update prepared statements and even more trouble finding documentation on dynamically updating the database with pdo prepared statements. I've gotten my dynamic insert to work but am having trouble with the update. The error I'm getting is:

Warning: PDOStatement::execute() [pdostatement.execute]: SQLSTATE[HY093]: Invalid parameter number: parameter was not defined in /Users/scottmcpherson/Sites/phpsites/projectx/application/models/db.php on line 91 error

Here is the class I created minus a couple of methods that are irrelevant to this problem:

<?php 
require_once("../config/main.php");

class Database{

protected static $dbFields = array('username', 'password');
public $db;
public $tableName = 'users';
public $id = 1;
public $username = "Jonny";
public $password = "Appleseed";

public function __construct() {
    $this->connect();
}
public function connect(){
try {
    $this->db = new PDO("mysql:host=".DB_SERVER."; dbname=".DB_NAME, DB_USER, DB_PASS);

    } catch (PDOException $e) {
    echo 'Connection failed: ' . $e->getMessage();
    }
}
public function properties() {
    $properties = array();
    foreach (self::$dbFields as $field) {
        if (isset($this->field) || property_exists($this, $field)) {
            $properties[$field] = $this->$field;            
        }
    }
    return $properties;
}

public function propertyValues() {  
    $property = $this->properties();
    $propertyValues = array();
    foreach ($property as $key => $value) {
        $propertyValues = ":" . implode(", :", array_keys($property));
    }
    return $propertyValues;
}
public function polishedVals(){
       // The end result of this function is:
       // username=:username, password=:password
    $props = $this->properties();
    $phaseOne = array();
    foreach ($props as $key => $value) {
        $phaseOne[$key] = ":".$key;
    }
        $phaseTwo = array();
        foreach ($phaseOne as $key => $value) {
            $phaseTwo[] = "{$key}={$value}";
        }
        $polishedVals = implode(", ", $phaseTwo);
    return $polishedVals;
}
public function update(){

    $stmt  = "UPDATE ". $this->tableName." SET ";
    $stmt .= $this->polishedVals();
    $stmt .= "WHERE id=" . $this->id;   
    $stmt  = $this->db->prepare($stmt);
    if($stmt->execute($this->properties())) {
        echo "yes";
    } else {
        echo "error ";
    }
}
}

$database = new Database();

echo$database->update();


 ?>

With all the variables replaced with the actual values, the result I'm going for with the update() method would look like this:

public function update(){

    $stmt  = "UPDATE users SET ";
    $stmt .= "username=:username, password=:password ";
    $stmt .= "WHERE id=1";  
    $stmt  = $this->db->prepare($stmt);
    if($stmt->execute($this->properties())) {
        echo "yes";
    } else {
        echo "error ";
    }
}

In addition to spotting this problem, please let me know if you see any other issues with this code. I'm still kind of new to PHP.

Edit: I've now created a new method that adds a : to the beginning of each key in the properties array:

public function colProperties(){
    $properties = $this->properties();
    $withCols = array();
    foreach($properties as $key => $value){
        $withCols[":".$key] = $value;

    }
    return $withCols;
}

So my update() method now looks like: public function update(){

    $stmt  = "UPDATE ". $this->tableName." SET ";
    $stmt .= $this->polishedVals();
    $stmt .= "WHERE id=" . $this->id;   
    $stmt  = $this->db->prepare($stmt);

    if($stmt->execute($this->colProperties())) {
        echo "yes";
    } else {
        echo "error ";
    }
}

and if I var_dump($this->colProperties) I get: array(2) { [":username"]=> string(5) "Jonny" [":password"]=> string(9) "Appleseed" } And still getting the same error.

Answer 1

The error is that in between

$stmt .= $this->polishedVals();
$stmt .= "WHERE id=" . $this->id; 

There needs to be a space in between the WHERE clause as the polishedVals() method does not add a space after the implode. So, you'll have something like

UPDATE User SET city=:city, location=:locationWHERE User.id=28

Which causes the error. Simple bug.

Answer 2

I don't think that passing parameters to an UPDATE query requires a different method than a SELECT one. The information in the PDOStatement->execute() manual page should apply:

<?php
/* Execute a prepared statement by passing an array of insert values */
$calories = 150;
$colour = 'red';
$sth = $dbh->prepare('SELECT name, colour, calories
    FROM fruit
    WHERE calories < :calories AND colour = :colour');
$sth->execute(array(':calories' => $calories, ':colour' => $colour));
?>

You are using named parameters so execute() expects an associative array. Use var_dump() to display $this->properties() right before execute():

var_dump($this->properties())

Make sure you keys match exactly.