php - PDO Prepared statement Inside a class

Question

I am trying to use an update method inside my Database class where I could be able to update a record. I am trying to make sure that I could use the method in another instance where i dont have to repeat writing the same statement. Here is my code:

<?php 
require 'init.php';
class Database {
    private $conn;

    public function __construct() {
        try {
            $this->conn = new PDO('mysql:host=localhost;dbname=school', DB_USER, DB_PASS);
            $this->conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
        } catch(PDOException $e) {
            return false;
        }
    }

    public function update($table, $key, $value, $id) {
        $stmt = $this->conn->prepare("UPDATE $table SET $key = $value WHERE id = :id");
        return $stmt->execute(array($key => $value, 'id' => $id));
    }
}
$database = new Database();

My problem is i get some errors when i try to instantiate the class $result = $database->update('admin', 'username', 'golobo', 13); the question is what am I doing wrong?

Thanks

Answer 1

You aren't using the bindings feature of PDO quite right. You should do something like the following:

public function update($table, $key, $value, $id) {
    $stmt = $this->conn->prepare(
        "UPDATE $table SET $key = :value WHERE id = :id"
    );
    return $stmt->execute(array(
        ':value' => $value,
        ':id' => $id
    ));
}

First, you need to put the entire string to be bound into the key of the binding array. So you put ':id' rather than 'id'. Also you were putting the variables directly into the query in the case of $table and $value, but then attempting to bind them to each other, which doesn't make sense.

Edit: tables and column names can't be bound using PDO.