PDO abstract model

Answer.php

<?php

class Answer extends ModelPDO {

    public static function getAllByQuestion($question) {
        return self::getAllBy('question_id', $question->id);
    }


    public function __construct($data = false) {
        $schema = array(
            'question_id' => PDO::PARAM_INT,
            'text'        => PDO::PARAM_STR,
            'time'        => PDO::PARAM_STR
        );
        parent::__construct($schema, $data);
    }

}

?>

create.sql

DROP TABLE IF EXISTS users;
DROP TABLE IF EXISTS quizs;
DROP TABLE IF EXISTS questions;
DROP TABLE IF EXISTS answers;

CREATE TABLE users (
    user_id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    user_name VARCHAR(255) NOT NULL UNIQUE,
    user_email VARCHAR(255) NOT NULL,
    user_password VARCHAR(255) NOT NULL
);

CREATE TABLE quizs (
    quiz_id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    quiz_user_id INT NOT NULL REFERENCES users(user_id),
    quiz_title VARCHAR(255) NOT NULL,
    quiz_description TEXT,
    quiz_theme_id INT NOT NULL,
    quiz_created TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    quiz_updated TIMESTAMP
);

CREATE TABLE questions (
    question_id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    question_quiz_id INT NOT NULL REFERENCES quizs(quiz_id),
    question_text TEXT NOT NULL,
    question_required BOOL NOT NULL
);

CREATE TABLE answers (
    answer_id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    answer_question_id INT NOT NULL REFERENCES questions(question_id),
    answer_text TEXT,
    answer_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

ModelPDO.php

<?php

abstract class ModelPDO {

    private static $pdo;

    protected static function getPDO() {
        if (!isset(self::$pdo)) {
            self::$pdo = new PDO(
                'mysql:dbname=' . Config::DB . ';host=' . Config::HOST,
                Config::USER,
                Config::PASS
            );
            self::$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_WARNING);
        }
        return self::$pdo;
    }

    protected static function getModelName() {
        return strtolower(get_called_class());
    }

    protected static function getTableName() {
        return self::getModelName() . 's';
    }

    protected static function getFieldName($field) {
        return self::getModelName() . '_' . $field;
    }

    protected static function getBindName($field) {
        return ":{$field}";
    }

    protected static function getPropertyName($prop) {
        return substr($prop, strlen(self::getModelName()) + 1);
    }

    public static function get($id) {
        return self::getBy('id', $id);
    }

    protected static function getBy($field, $value) {
        $tableName = self::getTableName();
        $fieldName = self::getFieldName($field);
        $bindName = self::getBindName($field);
        $q = "SELECT * FROM {$tableName} ";
        $q .= "WHERE {$fieldName} = {$bindName}";
        $sth = self::getPDO()->prepare($q);
        $sth->bindParam($bindName, $value);
        $sth->execute();
        $data = $sth->fetch(PDO::FETCH_ASSOC);
        if ($data) {
            $modelName = self::getModelName();
            return new $modelName($data);
        }
        return null;
    }

    public static function getAll() {
        $tableName = self::getTableName();
        $q = "SELECT * FROM {$tableName} ";
        $sth = self::getPDO()->prepare($q);
        $sth->execute();
        $data = $sth->fetchAll(PDO::FETCH_ASSOC);
        if ($data) {
            $models = array();
            foreach ($data as $d) {
                $modelName = self::getModelName();
                $models[] = new $modelName($d);
            }
            return $models;
        }
        return null;
    }

    protected static function getAllBy($field, $value) {
        $tableName = self::getTableName();
        $fieldName = self::getFieldName($field);
        $bindName = self::getBindName($field);
        $q = "SELECT * FROM {$tableName} ";
        $q .= "WHERE {$fieldName} = {$bindName}";
        $sth = self::getPDO()->prepare($q);
        $sth->bindValue($bindName, $value);
        $sth->execute();
        $data = $sth->fetchAll(PDO::FETCH_ASSOC);
        if ($data) {
            $models = array();
            foreach ($data as $d) {
                $modelName = self::getModelName();
                $models[] = new $modelName($d);
            }
            return $models;
        }
        return null;
    }


    private $fields = array();

    public function __construct($schema, $data = false) {
        $this->fields['id'] = array('value' => null, 'type' => PDO::PARAM_INT);
        foreach ($schema as $name => $type) {
            $this->fields[$name] = array('value' => null, 'type' => $type);
        }
        if ($data) {
            foreach ($data as $column => $value) {
                $prop = self::getPropertyName($column);
                $this->fields[$prop]['value'] = $value;
            }
        }
    }

    public function save() {
        $tableName = self::getTableName();
        if ($this->fields['id']['value'] != null) {
            foreach ($this->fields as $field => $f) {
                if ($field != 'id' && $f['value'] != null) {
                    $fieldName = self::getFieldName($field); 
                    $bindName = self::getBindName($field);
                    $fields[] = "{$fieldName} = {$bindName}";
                }
            }
            $fieldName = self::getFieldName('id');
            $bindName = self::getBindName('id');
            $set = implode(', ', $fields);
            $q = "UPDATE {$tableName} ";
            $q .= "SET {$set} ";
            $q .= "WHERE {$fieldName} = {$bindName}";
        } else {
            foreach ($this->fields as $field => $f) {
                if ($field != 'id' && $f['value'] != null) {
                    $cols[] = self::getFieldName($field);
                    $binds[] = self::getBindName($field);
                }
            }
            $columns = implode(', ', $cols);
            $bindings = implode(', ', $binds);
            $q = "INSERT INTO {$tableName} ";
            $q .= "({$columns}) VALUES ({$binds})";
        }
        $sth = ModelPDO::getPDO()->prepare($q);
        foreach ($this->fields as $field => $f) {
            $value = $f['value'];
            if ($f['value'] != null) {
                $sth->bindValue(self::getBindName($field), $f['value'], $f['type']); 
            }
        }
        //echo "{$sth->queryString}\n";
        return $sth->execute();
    }
 
    public function __set($name, $value) {
        if (array_key_exists($name, $this->fields)) {
            $this->fields[$name]['value'] = $value;
        }
    }

    public function __get($name) {
        if (array_key_exists($name, $this->fields)) {
            return $this->fields[$name]['value'];
        }
    }

}

?>

Question.php

<?php

class Question extends ModelPDO {

    public static function getAllByQuiz($quiz) {
        return self::getAllBy('quiz_id', $quiz->id);
    }


    public function __construct($data = false) {
        $schema = array(
            'quiz_id'  => PDO::PARAM_INT,
            'text'     => PDO::PARAM_STR,
            'required' => PDO::PARAM_BOOL
        );
        parent::__construct($schema, $data);
    }

    public function getAnswers() {
        return Answer::getAllByQuestion($this);
    }

}

?>

Quiz.php

<?php

class Quiz extends ModelPDO {

    public static function getAllByUser($user) {
        return self::getAllBy('user_id', $user->id);
    }


    public function __construct($data = false) {
        $schema = array(
            'user_id'     => PDO::PARAM_INT,
            'title'       => PDO::PARAM_STR,
            'description' => PDO::PARAM_STR,
            'theme_id'    => PDO::PARAM_INT,
            'created'     => PDO::PARAM_STR,
            'updated'     => PDO::PARAM_STR
        );
        parent::__construct($schema, $data);
    }

    public function getQuestions() {
        return Question::getAllByQuiz($this);
    }

}

?>

test.php

<?php

// auto loads classes
define('ROOT', dirname(__FILE__) . '/');
function load($name) {
	if (class_exists($name, false)) return;
	$filename = ROOT . $name . '.php';
	if (file_exists($filename)){
		require_once($filename);
		return;
	}
}
spl_autoload_register('load');


/* get user by id and display their quizs */
$user3 = User::get(3);
echo "{$user3->name} {$user3->email}\n";
$quizs = $user3->getQuizs();
if ($quizs) {
    foreach ($user3->getQuizs() as $quiz) {
        echo " - {$quiz->title}: {$quiz->description}\n";
    }
} else {
    echo "user has no quizs";
}


/* get user by id, change name and save
$user3 = User::get(3);
echo "User3: {$user3->name} {$user3->email}\n";
$user3->name = 'test3';
$user3->save();
*/

/* create new quiz and save
$quiz2 = new Quiz();
$quiz2->user_id = $user3->id;
$quiz2->title = 'Second Quiz';
$quiz2->description = 'this is my second quiz omg!';
$quiz1->theme_id = 1;
$quiz2->save();
*/

/* create new user and save
$user1 = new User();
$user1->name = 'user1';
$user1->email = 'user1@gmail.com';
$user1->password = 'pass1';
$user1->save();
*/

/* display all users
$users = User::getAll();
echo "All Users:\n";
foreach ($users as $user) {
    echo "{$user->name} ({$user->email})\n";
}
*/

?>

User.php

<?php

class User extends ModelPDO {

    public function __construct($data = false) {
        $schema = array(
            'name'     => PDO::PARAM_STR,
            'email'    => PDO::PARAM_STR,
            'password' => PDO::PARAM_STR
        );
        parent::__construct($schema, $data);
    }

    public function getQuizs() {
        return Quiz::getAllByUser($this);
    }

}

?>