Thin PHP ORM

model.class.php

<?php

/**
 * Database Base Model class
 */
class Model {

    // Hold raw properties for save() which take precedence over regular properties
    // used in combination with set_raw() as in set_raw('somefield', 'CURDATE()')
    // Will allow you to pass in raw SQL, so use with caution
    // This array is unset after every save().
    private $_raw_properties = array();

    /**
     * Default constructor
     *
     * @param int id
     */
    public function __construct($id = null) {

        if (!empty($id)) {
            $pdo = Utility::get_database_connection();
            $statement = $pdo->prepare("SELECT * FROM {$this->_table} WHERE {$this->_pk} = ?");
            $result = $statement->execute(array($id));

            if (!$result) {
                $error_info = $statement->errorInfo();
                throw new Exception("Couldn't load {$this->_table} {$this->_pk}: {$id} ({$error_info[2]}).");
            } else {
                $row = $statement->fetch(PDO::FETCH_ASSOC);
                if (empty($row)) {
                    throw new Exception("Couldn't load {$this->_table} {$this->_pk}: {$id}.");
                }
                $this->inflate($row);
            }
        }
    }

    /**
     * Inflate and save an object of this type
     *
     * @param array[string]mixed $row
     * @return classname
     * @static
     */
    public static function create($row) {
        $classname = get_called_class();
        $obj = new $classname();
        $obj->inflate($row);
        $obj->save();
        return $obj;
    }

    /**
     * Inflate an object from a keyval array
     *
     * @param array[string]mixed $row
     */
    public function inflate($row) {
        if (!empty($row)) {
            foreach ($row as $col => $val) {
                $this->$col = $val;
            }
        }
    }

    /**
     * Magic method - all gets routed through here
     *
     * @param string $var property name
     * @return mixed value of property
     */
    public function __get($var) {
        return ($var) ? $this->$var : null;
    }

    /**
     * Magic method - all sets routed through here
     *
     * @param string $var property name
     * @param mixed $val value to set
     * @return $val
     */
    public function __set($var, $val) {
        return ($var) ? $this->$var = $val : null;
    }

    // For child to override
    public function _pre_insert_hook() {
        
    }

    // For child to override
    public function _pre_update_hook() {
        
    }

    // For child to override
    public function _pre_save_hook() {
        
    }

    /**
     * Magic method - Transforms get_property or set_property into native calls
     * 
     * @param string $method
     * @param array[int]mixed $arg_array
     * @return mixed
     */
    public function __call($method, $arg_array) {
        if (substr($method, 0, 4) == 'get_') {
            $prop = substr($method, 4);
            return $this->$prop;
        }
        if (substr($method, 0, 4) == 'set_') {
            $prop = substr($method, 4);
            return call_user_func_array(array($this, '__set'), array($prop, $arg_array[0]));
        }
        throw new Exception('Undefined method: ' . $method);
    }

    public function set_raw($field, $val) {
        $this->_raw_properties[$field] = $val;
    }

    /**
     * Update or insert a record from model data
     *
     * @return bool success
     */
    public function save() {

        $this->_pre_save_hook();

        $pdo = Utility::get_database_connection();

        $params = array();

        if ($this->_get_key()) {

            $this->_pre_update_hook();

            $sql = "UPDATE {$this->_table} SET ";

            $reflector = new ReflectionClass($this);
            $properties = $reflector->getProperties(ReflectionProperty::IS_PUBLIC | ReflectionProperty::IS_PROTECTED);

            foreach ($properties as $property) {
                $col = $property->getName();

                // "private" field skip
                if (substr($col, 0, 1) === '_') {
                    continue;
                }

                if ($col != $this->_pk) {
                    if (isset($this->_raw_properties[$col])) {
                        $sql .= "`$col` = " . $this->_raw_properties[$col] . ", ";
                    } else {
                        $sql .= "`$col` = ?, ";
                        $params[] = $this->$col;
                    }
                }
            }
            $sql = substr($sql, 0, -2);

            $sql .= " WHERE {$this->_pk} = ?";
            $params[] = $this->_get_key();

            $statement = $pdo->prepare($sql);
            $result = $statement->execute($params);
        } else {

            $this->_pre_insert_hook();

            $sql = "INSERT INTO {$this->_table} ";

            $col_list = '';
            $val_list = '';

            $reflector = new ReflectionClass($this);
            $properties = $reflector->getProperties(ReflectionProperty::IS_PUBLIC | ReflectionProperty::IS_PROTECTED);

            foreach ($properties as $property) {

                $col = $property->getName();

                // "private" field skip
                if (substr($col, 0, 1) === '_') {
                    continue;
                }

                if ($col != $this->_pk) {
                    $col_list .= "`$col`, ";
                    if (isset($this->_raw_properties[$col])) {
                        $sql .= "`$col` = " . $this->_raw_properties[$col] . ", ";
                    } else {
                        $val_list .= "?, ";
                        $params[] = $this->$col;
                    }
                }
            }

            $col_list = substr($col_list, 0, -2);
            $val_list = substr($val_list, 0, -2);

            $sql .= "($col_list) VALUES ($val_list)";

            $statement = $pdo->prepare($sql);
            $result = $statement->execute($params);

            // Only time an id should be set
            $keyfield = $this->_pk;
            $this->$keyfield = $pdo->lastInsertId();
        }

        if (!$result) {
            $error_info = $statement->errorInfo();
            throw new Exception("Couldn't save {$this->_table} {$this->_pk}: " . $this->_get_key() . " {$error_info[2]}.");
        }

        unset($this->_raw_properties);

        return $result;
    }

    /**
     * Delete the current object from the db
     *
     * @return boolean success
     */
    public function delete() {

        $pdo = Utility::get_database_connection();
        $statement = $pdo->prepare("DELETE FROM {$this->_table} WHERE {$this->_pk} = ? LIMIT 1");
        $result = $statement->execute(array($this->_get_key()));

        if (!$result) {
            $error_info = $statement->errorInfo();
            throw new Exception("Couldn't delete {$this->_table} {$this->_pk}: " . $this->_get_key() . " {$error_info[2]}.");
        }

        return $result;
    }

    /**
     * Delete records from database matching query
     *
     * @param string $search a valid where clause without 'WHERE' using '?' placeholders
     * @param array[int]mixed $params values corresonding to placeholders
     * @param int $limit
     * @return boolean success
     */
    public static function delete_where($search = null, $params = null, $limit = null) {

        $classname = get_called_class();
        $object = new $classname();

        $pdo = Utility::get_database_connection();

        $where = null;
        $limit = null;

        if ($search != null) {
            $where = "WHERE {$search}";
        }

        if ($limit !== null && $limit > 0) {
            $limit = "LIMIT $limit ";
        }

        $sql = "DELETE FROM {$object->_table} {$where} {$limit}";

        $statement = $pdo->prepare($sql);
        $result = $statement->execute($params);

        if (!$result) {
            $error_info = $statement->errorInfo();
            throw new Exception("Couldn't delete from {$object->_table}: {$error_info[2]}.");
        }

        return $result;
    }

    /**
     * Return a list of all the current records
     *
     * @param string $search  a valid where clause without 'WHERE'
     * @param array[int]mixed  $params  array of parameters matching where clause
     * @param Paginator $objPaginator if supplied determines order, start and perpage
     * @param string $order   columns to sort by without 'ORDER BY'
     * @param string $columns list of fields overiding default *
     * @param int    $start   0 based result to start with 'LIMIT'
     * @param int    $perpage number of results to show
     *
     * @return Objects[]
     */
    public static function select($search = null, $params = null, $objPaginator = null, $order = null, $start = null, $perpage = null) {

        $classname = get_called_class();
        $object = new $classname();

        $pdo = Utility::get_database_connection();

        $rowcalc = '';

        if ($objPaginator) {
            $start = $objPaginator->get_start();
            $perpage = $objPaginator->get_perpage();
            $order = $objPaginator->get_order();
            $rowcalc = "SQL_CALC_FOUND_ROWS";
        }

        $where = null;
        $limit = null;

        if ($search != null) {
            $where = "WHERE {$search}";
        }

        if ($order != null) {
            $order = "ORDER BY {$order}";
        }

        if ($start !== null && $perpage > 0) {
            $limit = "LIMIT $start, $perpage ";
        }

        $sql = "SELECT {$rowcalc} * FROM {$object->_table} {$where} {$order} {$limit}";

        $statement = $pdo->prepare($sql);
        $result = $statement->execute($params);

        if (!$result) {
            $error_info = $statement->errorInfo();
            throw new Exception("Couldn't get all {$object->_table}: {$error_info[2]}.");
        }

        $objects = array();
        foreach ($statement->fetchAll(PDO::FETCH_ASSOC) as $row) {
            // Instantiate array of "this" kind of objects
            $objSelf = new $classname();
            $objSelf->inflate($row);
            $objects[] = $objSelf;
        }

        if ($objPaginator) {
            // find found rows
            $statement = $pdo->prepare("SELECT FOUND_ROWS() as found");
            $result = $statement->execute();
            $row = $statement->fetch(PDO::FETCH_ASSOC);

            $objPaginator->set_total($row['found']);
        }

        return $objects;
    }

    /**
     * Insert multiple rows at once from an array of associative arrays
     *
     * @param array[int][string]mixed $rows
     * @param int $chunk_size break inserts at this many rows
     * @param string $dupe_clause example ON DUPLICATE KEY UPDATE....
     * @return int
     */
    public static function insert_multi($rows, $chunk_size = 100, $dupe_clause = null) {

        $count = 0;
        if (count($rows) == 0) {
            return $count;
        }

        $classname = get_called_class();
        $object = new $classname();

        $pdo = Utility::get_database_connection();

        $chunks = array_chunk($rows, $chunk_size);

        foreach ($chunks as $rows) {

            $col_list = '';
            $val_list = '';
            $params = array();

            $sql = "INSERT INTO {$object->_table} ";

            foreach ($rows[0] as $col => $val) {
                // prevent injections by restricting value to single word
                $col = preg_replace('/[^a-zA-Z0-9_]/', '', $col);
                $col_list .= "`$col`, ";
                $val_list .= "?, ";
            }

            $col_list = substr($col_list, 0, -2);
            $val_list = substr($val_list, 0, -2);

            $sql .= "($col_list) VALUES ";
            foreach ($rows as $row) {
                $sql .= "(" . $val_list . "), ";
                foreach ($row as $key => $val) {
                    $params[] = $val;
                }
            }

            $sql = substr($sql, 0, -2);
            $sql .= " " . $dupe_clause;
            $statement = $pdo->prepare($sql);
            $result = $statement->execute($params);

            if (!$result) {
                $error_info = $statement->errorInfo();
                throw new Exception("Couldn't multi insert into {$table} {$error_info[2]}.");
            }

            $count += $statement->rowCount();
        }

        return $count;
    }

    /**
     * Return this record's primary key
     *
     * @return int
     */
    public function _get_key() {
        $idkey = $this->_pk;
        return $this->$idkey;
    }

}