Location: PHPKode > projects > Moc10 PHP Library > library/Moc10/Record.php
<?php
/**
 * Moc10 Library
 *
 * LICENSE
 *
 * This source file is subject to the new BSD license that is bundled
 * with this package in the file LICENSE.TXT.
 * It is also available through the world-wide-web at this URL:
 * http://www.moc10phplibrary.com/LICENSE.TXT
 * If you did not receive a copy of the license and are unable to
 * obtain it through the world-wide-web, please send an email
 * to hide@address.com so we can send you a copy immediately.
 *
 * @category   Moc10
 * @package    Moc10_Record
 * @author     Nick Sagona, III <hide@address.com>
 * @copyright  Copyright (c) 2009-2011 Moc 10 Media, LLC. (http://www.moc10media.com)
 * @license    http://www.moc10phplibrary.com/LICENSE.TXT     New BSD License
 */

/**
 * Moc10_Record
 *
 * @category   Moc10
 * @package    Moc10_Record
 * @author     Nick Sagona, III <hide@address.com>
 * @copyright  Copyright (c) 2009-2011 Moc 10 Media, LLC. (http://www.moc10media.com)
 * @license    http://www.moc10phplibrary.com/LICENSE.TXT     New BSD License
 * @version    1.9.7
 */

class Moc10_Record
{

    /**
     * Database connection
     * @var resource
     */
    public $db = null;

    /**
     * Rows of multiple return results from a database query
     * in an ArrayObject format.
     * @var array
     */
    public $rows = array();

    /**
     * Default database type
     * @var string contains the database type
     */
    protected $_type = null;

    /**
     * Default database name
     * @var string contains the database name
     */
    protected $_dbname = null;

    /**
     * Default database hostname
     * @var string contains the database hostname
     */
    protected $_hostname = null;

    /**
     * Default database username
     * @var string contains the database username
     */
    protected $_username = null;

    /**
     * Default database password
     * @var string contains the database password
     */
    protected $_password = null;

    /**
     * Table name of the database table
     * @var string
     */
    protected $_tableName = null;

    /**
     * Primary ID field name of the database table
     * @var string
     */
    protected $_primaryId = null;

    /**
     * Field names of the database table
     * @var array
     */
    protected $_fields = array();

    /**
     * Language object
     * @var Moc10_Language
     */
    protected $_lang = null;

    /**
     * Constructor
     *
     * Instantiate the database record object.
     *
     * @param  array $fields
     * @param  string $type
     * @param  string $dbase
     * @param  string $host
     * @param  string $user
     * @param  string $pass
     * @return void
     */
    protected function __construct($fields = null, $type = null, $dbase = null, $host = null, $user = null, $pass = null)
    {

        $this->_lang = new Moc10_Language();

        // If the $fields argument is set, set the _fields properties.
        if (!is_null($fields)) {
            foreach($fields as $key => $value) {
                $this->_fields[$key] = $value;
            }
        }

        $this->_type = $type;
        $this->_dbname = $dbase;
        $this->_hostname = $host;
        $this->_username = $user;
        $this->_password = $pass;

        $this->db = Moc10_Db::getInstance($this->_type, $this->_dbname, $this->_hostname, $this->_username, $this->_password);

    }

    /**
     * Set method to set the property to the value of _fields[$name].
     *
     * @param  string $name
     * @param  mixed $value
     * @throws Exception
     * @return void
     */
    public function __set($name, $value)
    {

        // Check to see if the field key exists.
        if (!array_key_exists($name, $this->_fields)) {
            throw new Exception($this->_lang->__("The field '%1' does not exist.", $name));
        } else {
            $this->_fields[$name] = $value;
        }

    }

    /**
     * Get method to return the value of _fields[$name].
     *
     * @param  string $name
     * @throws Exception
     * @return mixed
     */
    public function __get($name)
    {

        // Check to see if the field key exists.
        if (!array_key_exists($name, $this->_fields)) {
            throw new Exception($this->_lang->__("The field '%1' does not exist.", $name));
        } else {
            return $this->_fields[$name];
        }

    }

    /**
     * Return the isset value of _fields[$name].
     *
     * @param  string $name
     * @return boolean
     */
    public function __isset($name)
    {

        return isset($this->_fields[$name]);

    }

    /**
     * Unset _fields[$name].
     *
     * @param  string $name
     * @return void
     */
    public function __unset($name)
    {

        $this->_fields[$name] = null;

    }

    /**
     * Set all the table fields at once.
     *
     * @param  array $fields
     * @throws Exception
     * @return void
     */
    public function setAll($fields = null)
    {

        // If null, clear the fields.
        if (is_null($fields)) {
            $this->_fields = array();
            $this->rows = array();
        // Else, if an array, set the fields.
        } else if (is_array($fields)) {
            $this->_fields = $fields;
            $this->rows[0] = new ArrayObject($fields, ArrayObject::ARRAY_AS_PROPS);
        // Else, throw an exception.
        } else {
            throw new Exception($this->_lang->__('The parameter passed must be either an array or null.'));
        }

    }

    /**
     * Find a database row by the primary ID passed through the method argument.
     *
     * @param  int|string $id
     * @param  int|string $limit
     * @throws Exception
     * @return void
     */
    public function findById($id, $limit = 1)
    {

        if (is_null($this->_primaryId)) {
            throw new Exception($this->_lang->__('This primary ID of this table either is not set or does not exist.'));
        } else {

            // Create the DB connection.
            $this->db = Moc10_Db::getInstance($this->_type, $this->_dbname, $this->_hostname, $this->_username, $this->_password);

            // Set the SQL query to select the row.
            $sql = "SELECT * FROM " . $this->_tableName . " WHERE " . $this->_primaryId . " = '" . $this->db->interface->escape($id) . "' LIMIT " . $this->db->interface->escape($limit);

            // Execute the query.
            $this->db->interface->query($sql);

            // Set the return results.
            $this->_setResults();

        }

    }

    /**
     * Find a database row by the field passed through the method argument.
     *
     * @param  string|array $field
     * @param  int|string $value
     * @param  int|string $limit
     * @return void
     */
    public function findBy($field, $value = null, $limit = 1)
    {

        // Create the DB connection.
        $this->db = Moc10_Db::getInstance($this->_type, $this->_dbname, $this->_hostname, $this->_username, $this->_password);

        // Set the SQL query to select the row.
        $sql = "SELECT * FROM " . $this->_tableName;

        if (is_array($field)) {
            $sqlAry = array();
            foreach ($field as $key => $value) {
                $sqlAry[] = "{$key} = '" . $this->db->interface->escape($value) . "'";
            }
            $sql .= " WHERE " . implode(' AND ', $sqlAry) . " LIMIT " . $this->db->interface->escape($limit);
        } else {
            $sql .= " WHERE {$field} = '" . $this->db->interface->escape($value) . "' LIMIT " . $this->db->interface->escape($limit);
        }

        // Execute the query.
        $this->db->interface->query($sql);

        // Set the return results.
        $this->_setResults();

    }

    /**
     * Find all of the database rows by the field passed through the method argument.
     *
     * @param  string $order
     * @param  string|array $field
     * @param  int|string $value
     * @param  int|string $limit
     * @return void
     */
    public function findAll($order = null, $field = null, $value = null, $limit = null)
    {

        // Create the DB connection.
        $this->db = Moc10_Db::getInstance($this->_type, $this->_dbname, $this->_hostname, $this->_username, $this->_password);

        // Set the SQL query to select the row.
        $sql = "SELECT * FROM " . $this->_tableName;

        if (!is_null($field)) {
            if (is_array($field)) {
                $sqlAry = array();
                foreach ($field as $key => $value) {
                    $sqlAry[] = "{$key} = '" . $this->db->interface->escape($value) . "'";
                }
                $sql .= " WHERE " . implode(' AND ', $sqlAry);
            } else if (!is_null($value)) {
                $sql .= " WHERE {$field} = '" . $this->db->interface->escape($value) . "'";
            }
        }

        // Set the SQL query to a specific order, if given.
        if (!is_null($order)) {
            $sql .= " ORDER BY " . $this->db->interface->escape($order);
        }

        // Set any limit to the SQL query.
        if (!is_null($limit)) {
            $sql .= " LIMIT " . $this->db->interface->escape($limit);
        }

        // Execute the query.
        $this->db->interface->query($sql);

        // Set the return results.
        $this->_setResults();

    }

    /**
     * Find singular and distinct entries in the database based on the search criteria.
     *
     * @param  array $distinctFields
     * @param  string $order
     * @param  string|array $field
     * @param  int|string $value
     * @param  int|string $limit
     * @return void
     */
    public function distinct($distinctFields, $order = null, $field = null, $value = null, $limit = null)
    {

        // Create the DB connection.
        $this->db = Moc10_Db::getInstance($this->_type, $this->_dbname, $this->_hostname, $this->_username, $this->_password);

        // Set the SQL query to select the row.
        $sql = "SELECT DISTINCT ";

        if (is_array($distinctFields)) {
            $sql .= implode(', ', $distinctFields);
        } else {
            $sql .= $distinctFields;
        }

        $sql .= " FROM " . $this->_tableName;

        if (!is_null($field)) {
            if (is_array($field)) {
                $sqlAry = array();
                foreach ($field as $key => $value) {
                    $sqlAry[] = "{$key} = '" . $this->db->interface->escape($value) . "'";
                }
                $sql .= " WHERE " . implode(' AND ', $sqlAry);
            } else if (!is_null($value)) {
                $sql .= " WHERE {$field} = '" . $this->db->interface->escape($value) . "'";
            }
        }

        // Set the SQL query to a specific order, if given.
        if (!is_null($order)) {
            $sql .= " ORDER BY " . $this->db->interface->escape($order);
        }

        // Set any limit to the SQL query.
        if (!is_null($limit)) {
            $sql .= " LIMIT " . $this->db->interface->escape($limit);
        }

        // Execute the query.
        $this->db->interface->query($sql);

        // Set the return results.
        $this->_setResults();

    }

    /**
     * Search the database for rows based on the search criteria.
     *
     * @param  array $likeFields
     * @param  string $order
     * @param  int|string $limit
     * @return void
     */
    public function search($likeFields, $conj = 'AND', $order = null, $limit = null)
    {

        // Create the DB connection.
        $this->db = Moc10_Db::getInstance($this->_type, $this->_dbname, $this->_hostname, $this->_username, $this->_password);

        // Set the SQL query to select the row.
        $sql = "SELECT * FROM " . $this->_tableName . " WHERE ";

        $sqlLikes = array();

        // Set the SQL query with any given LIKE search criteria.
        foreach($likeFields as $key => $value) {
            $sqlLikes[] = "{$key} LIKE '" . $this->db->interface->escape($value) . "'";
        }

        $sql .= implode(' ' . $conj . ' ', $sqlLikes);

        // Set the SQL query to a specific order, if given.
        if (!is_null($order)) {
            $sql .= " ORDER BY " . $this->db->interface->escape($order);
        }

        // Set any limit to the SQL query.
        if (!is_null($limit)) {
            $sql .= " LIMIT " . $this->db->interface->escape($limit);
        }

        // Execute the query.
        $this->db->interface->query($sql);

        // Set the return results.
        $this->_setResults();

    }

    /**
     * Join data from two tables that share a related field.
     *
     * @param  string $table
     * @param  string $tableid
     * @param  string $order
     * @param  string $field
     * @param  int|string $value
     * @param  int|string $limit
     * @return void
     */
    public function join($table, $tableid, $order = null, $field = null, $value = null, $limit = null)
    {

        // Create the DB connection.
        $this->db = Moc10_Db::getInstance($this->_type, $this->_dbname, $this->_hostname, $this->_username, $this->_password);

        // Set the SQL query to select the row.
        $sql = "SELECT * FROM " . $this->_tableName . " LEFT JOIN {$table} ON " . $this->_tableName . ".{$tableid} = {$table}.{$tableid}";

        // Set the SQL query to find a specific conditional, if given.
        if ((!is_null($field)) && (!is_null($value))) {
            $sql .= " WHERE {$field} = '" . $this->db->interface->escape($value) . "'";
        }

        // Set the SQL query to a specific order, if given.
        if (!is_null($order)) {
            $sql .= " ORDER BY " . $this->db->interface->escape($order);
        }

        // Set any limit to the SQL query.
        if (!is_null($limit)) {
            $sql .= " LIMIT " . $this->db->interface->escape($limit);
        }

        // Execute the query.
        $this->db->interface->query($sql);

        // Set the return results.
        $this->_setResults();

    }

    /**
     * Execute a custom SQL query.
     *
     * @param  string $sql
     * @return void
     */
    public function execute($sql)
    {

        // Create the DB connection and execute the query.
        $this->db = Moc10_Db::getInstance($this->_type, $this->_dbname, $this->_hostname, $this->_username, $this->_password);
        $this->db->interface->query($sql);

        if (stripos($sql, 'select') !== false) {

            // Set the return results.
            $this->_setResults();

        } else if (stripos($sql, 'delete') !== false) {

            $this->_fields = array();
            $this->rows = array();

        }

    }

    /**
     * Save the database record.
     *
     * @param  string $action
     * @return void
     */
    public function save($new = true)
    {

        // Create the DB connection.
        $this->db = Moc10_Db::getInstance($this->_type, $this->_dbname, $this->_hostname, $this->_username, $this->_password);

        if (is_null($this->_primaryId)) {

            if ($new == false) {

                $sql = "UPDATE " . $this->_tableName . " SET ";

                $sqlFields = array();

                foreach ($this->_fields as $key => $value) {
                    $sqlFields[] = "{$key} = '" . $this->db->interface->escape($value) . "'";
                }

                $sql .= implode(', ', $sqlFields);

                $this->db->interface->query($sql);

            } else {

                $sql = "INSERT INTO " . $this->_tableName;

                $sqlColumns = array();
                $sqlValues = array();

                foreach ($this->_fields as $key => $value) {
                    $sqlColumns[] = $key;
                    $sqlValues[] = "'" . $this->db->interface->escape($value) . "'";
                }

                $sql .= " (" . implode(', ', $sqlColumns) . ") VALUES (" . implode(', ', $sqlValues) . ")";

                $this->db->interface->query($sql);

            }

        } else {

            // Determine if the record exists to be updated, or is a new record to be inserted, setting the SQL query and executing it.
            if (isset($this->_fields[$this->_primaryId])) {

                $sql = "UPDATE " . $this->_tableName . " SET ";

                $sqlFields = array();

                foreach ($this->_fields as $key => $value) {
                    $sqlFields[] = "{$key} = '" . $this->db->interface->escape($value) . "'";
                }

                $sql .= implode(', ', $sqlFields);
                $sql .= " WHERE " . $this->_primaryId . " = '" . $this->_fields[$this->_primaryId] . "'";

                $this->db->interface->query($sql);

            } else {

                $sql = "INSERT INTO " . $this->_tableName;

                $sqlColumns = array();
                $sqlValues = array();

                foreach ($this->_fields as $key => $value) {
                    $sqlColumns[] = $key;
                    $sqlValues[] = "'" . $this->db->interface->escape($value) . "'";
                }

                $sql .= " (" . implode(', ', $sqlColumns) . ") VALUES (" . implode(', ', $sqlValues) . ")";

                $this->db->interface->query($sql);
                $this->_fields[$this->_primaryId] = $this->db->interface->lastID();
                $this->rows[0][$this->_primaryId] = $this->db->interface->lastID();

            }

        }

    }

    /**
     * Delete the database record.
     *
     * @throws Exception
     * @return void
     */
    public function delete($field = null, $value = null)
    {

        // Create the DB connection and execute the query.
        $this->db = Moc10_Db::getInstance($this->_type, $this->_dbname, $this->_hostname, $this->_username, $this->_password);

        if (is_null($this->_primaryId)) {

            if ((is_null($field)) && (is_null($value))) {
                throw new Exception($this->_lang->__('The field and value parameters were not defined to describe the row(s) to delete.'));
            } else {

                // Set the SQL query to delete the row.
                $sql = "DELETE FROM " . $this->_tableName . " WHERE " . $field . " = '" . $this->db->interface->escape($value) . "'";

                $this->db->interface->query($sql);

                $this->_fields = array();
                $this->rows = array();

            }

        } else {

            // Set the SQL query to delete the row.
            $sql = "DELETE FROM " . $this->_tableName . " WHERE " . $this->_primaryId . " = '" . $this->_fields[$this->_primaryId] . "'";

            $this->db->interface->query($sql);

            $this->_fields = array();
            $this->rows = array();

        }

    }

    /**
     * Set the query results.
     *
     * @return void
     */
    protected function _setResults()
    {

        // If there is more than one result returned, create an array of results.
        if ($this->db->interface->numRows() > 1) {
            $this->_fields = array();
            while (($row = $this->db->interface->fetch()) != false) {
                $this->rows[] = new ArrayObject($row, ArrayObject::ARRAY_AS_PROPS);
            }
        // Else, set the _fields array to the single returned result.
        } else {
            while (($row = $this->db->interface->fetch()) != false) {
                $this->_fields = $row;
                $this->rows[0] = new ArrayObject($row, ArrayObject::ARRAY_AS_PROPS);
            }
        }

    }

}
Return current item: Moc10 PHP Library