<?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);
}
}
}
}