database
(Pseudo-) Database Class
This class is designed to be used with the MySQLi Classes provided in an earlier post
<?php
/**
* Squiloople Framework
*
* LICENSE: Feel free to use and redistribute this code.
*
* @author Michael Rushton <michael@squiloople.com>
* @link http://squiloople.com/
* @category Squiloople
* @package Models
* @subpackage Databases
* @version 1.0
* @copyright © 2011 Michael Rushton
*/
// Define the namespace
namespace Models\Databases;
/**
* Database
*
* Acts as a database for simple queries
*/
final class Database
{
/**
* An instance of \Models\MySQLi\MySQLiConnection
*
* @access private
* @var \Models\MySQLi\MySQLiConnection $_connection
*/
private $_connection;
/**
* An array of the data
*
* @access private
* @var array $_data
*/
private $_data = array();
/**
* Store an instance of \Models\MySQLi\MySQLiConnection
*
* @access public
* @param string $hostname
* @param string $username
* @param string $password
* @param string $database
*/
public function __construct()
{
$this->_connection = call_user_func_array('\Models\MySQLi\MySQLiConnection::setConnection', func_get_args());
}
/**
* Insert a row with the given data
*
* @access public
* @param string $table
* @return \Models\Databases\Database
*/
public function insert($table)
{
// Prepare the statement
$stmt = $this->_connection->prepareInsertStatement()
->addTable($table);
// Prepare, bind, and execute the query
$stmt = $this->_executeQuery($stmt, __FUNCTION__);
// Store the new row's ID
$this->_data['id'] = $stmt->getInsertID();
// Return the \Models\Databases\Database object
return $this;
}
/**
* Delete the row with the given data
*
* @access public
* @param string $table
* @return \Models\Databases\Database
*/
public function delete($table)
{
// Prepare the statement
$stmt = $this->_connection->prepareDeleteStatement()
->addTable($table)
->addWhere('id = i:id');
// Prepare, bind, and execute the query
$this->_executeQuery($stmt, __FUNCTION__);
// Return the \Models\Databases\Database object
return $this;
}
/**
* Update the row with the given data
*
* @access public
* @param string $table
* @return \Models\Databases\Database
*/
public function update($table)
{
// Prepare the statement
$stmt = $this->_connection->prepareUpdateStatement()
->addTable($table)
->addWhere('id = i:id');
// Prepare, bind, and execute the query
$this->_executeQuery($stmt, __FUNCTION__);
// Return the \Models\Databases\Database object
return $this;
}
/**
* Select the rows with the given data
*
* @access public
* @param string $table
* @return \Models\Databases\Database
*/
public function select($table)
{
// Prepare the select statement
$stmt = $this->_connection->prepareSelectStatement()
->addTable($table);
->setLimit('1');
// Prepare, bind, and execute the query
$stmt = $this->_executeQuery($stmt, __FUNCTION__);
// Store the row
$this->_data = (array) current($stmt->getOne()->getData());
// Free the result
$stmt->freeResult();
// Return the \Models\Databases\Database object
return $this;
}
/**
* Prepare, bind, and execute the query
*
* @access private
* @param \Models\MySQLi\MySQLiStatement $stmt
* @param string $function
* @return \Models\MySQLi\MySQLiStatement
*/
private function _executeQuery($stmt, $function)
{
// Iterate through the data
foreach ($this->_data as $field => $value)
{
// Store the field and value in an array
$parameters[] = $field . ':' . $value;
// Convert the datatype into an initial or throw an exception if not valid for MySQL
switch (gettype($value))
{
case ('integer'):
$value = 'i';
break;
case ('double'):
$value = 'd';
break;
case ('string'):
$value = 's';
break;
case ('blob'):
$value = 'b';
break;
default:
throw new \Exception('Unknown data type for MySQL query');
}
// If the function is "select" then add a WHERE clause
if ($function == 'select')
{
$stmt->addWhere($field . ' = ' . $value . ':' . $field);
}
// Otherwise if the function is not "delete" and the field is not "id" then add a SET clause
elseif ($function != 'delete' && $field != 'id')
{
$stmt->addValue($field, $value . ':' . $field);
}
}
// Prepare the query
$stmt = $stmt->prepareQuery();
// Set the values of the bound parameters
call_user_func_array(array($stmt, 'setParameters'), $parameters);
// Return the \Models\MySQLi\MySQLiResult object
return $stmt;
}
/**
* Set the data
*
* @access public
* @param string $field
* @param mixed $value
*/
public function __set($field, $value)
{
$this->_data[$field] = $value;
}
/**
* Get the field data
*
* @access public
* @param string $field
* @return mixed
*/
public function __get($field)
{
// If the field data is not present then return null
if (!isset($this->_data[$field]))
{
return null;
}
// Return the field data
return $this->_data[$field];
}
}
MySQLi Classes
MySQLi Connection
<?php
/**
* Squiloople Framework
*
* LICENSE: Feel free to use and redistribute this code.
*
* @author Michael Rushton <michael@squiloople.com>
* @link http://squiloople.com/
* @category Squiloople
* @package Models
* @subpackage MySQLi
* @version 1.0
* @copyright © 2010 Michael Rushton
*/
// Define the namespace
namespace Models\MySQLi;
/**
* MySQLiConnection
*
* Control a MySQLi connection
*/
final class MySQLiConnection
{
/**
* An instance of the class
*
* @access private
* @static
* @var \Models\MySQLi\MySQLiConnection|null $_instance
*/
private static $_instance;
/**
* The connection resource
*
* @access private
* @var mysqli $_connection
*/
private $_connection;
/**
* Try to connect to the server
*
* @access private
* @param array $connect
*/
private function __construct($connect)
{
// If a connection cannot be made then throw an exception
if (!$this->_connection = call_user_func_array('mysqli_connect', $connect))
{
throw new \Exception('Unable to connect to the database server');
}
}
/**
* Try to create an instance of the object, and then return it
*
* @access public
* @static
* @param string $hostname
* @param string $username
* @param string $password
* @param string $database
* @return \Models\MySQLi\MySQLiConnection
*/
public static function setConnection()
{
// If an instance has not been created then create one
if (!isset(self::$_instance))
{
self::$_instance = new self(func_get_args());
}
// Return the \Models\MySQLi\Connection instance
return self::$_instance;
}
/**
* Return the connection
*
* @access public
* @return mysqli|false
*/
public function getConnection()
{
return $this->_connection;
}
/**
* Try to select the database
*
* @access public
* @param string $database
* @return \Models\MySQLi\MySQLiConnection
*/
public function setDatabase($database)
{
// If the database cannot be selected then throw an exception
if (!$this->_connection->select_db($database))
{
throw new \Exception('Unable to select the database');
}
// Return the \Models\MySQLi\MySQLiConnection instance
return $this;
}
/**
* Try to prepare the query
*
* @access public
* @param string $query
* @param string $result
* @return \Models\MySQLi\MySQLiResult
*/
public function prepareQuery($query, $result = '')
{
// Store all the matches to be used for bound parameters
preg_match_all('/[idsb]:([a-zA-Z0-9_$]+|`[^`]+`)/', $query, $vars);
// If the re-formatted query cannot be prepared then throw an exception
if (!$stmt = $this->_connection->prepare(preg_replace('/[idsb]:([a-zA-Z0-9_$]+|`[^`]+`)/', '?', $query)))
{
throw new \Exception('Unable to prepare the query');
}
$result = '\Models\MySQLi\MySQLi' . ucfirst(strtolower($result)) . 'Result';
// Instantiate and return the \Models\MySQLi\MySQLiResult object
return new $result($stmt, $vars[0]);
}
/**
* Prepare an INSERT statement
*
* @access public
* @return \Models\MySQLi\MySQLiInsertStatement
*/
public function prepareInsertStatement()
{
return new MySQLiInsertStatement;
}
/**
* Prepare a DELETE statement
*
* @access public
* @return \Models\MySQLi\MySQLiDeleteStatement
*/
public function prepareDeleteStatement()
{
return new MySQLiDeleteStatement;
}
/**
* Prepare an UPDATE statement
*
* @access public
* @return \Models\MySQLi\MySQLiUpdateStatement
*/
public function prepareUpdateStatement()
{
return new MySQLiUpdateStatement;
}
/**
* Prepare a SELECT statement
*
* @access public
* @return \Models\MySQLi\MySQLiSelectStatement
*/
public function prepareSelectStatement()
{
return new MySQLiSelectStatement;
}
/**
* Try to disconnect from the server
*
* @access public
*/
public function disconnect()
{
// If the server connection cannot be closed then throw an exception
if (!$this->_connection->close())
{
throw new \Exception('Unable to close the connection');
}
// Destroy the instance
self::$_instance = null;
}
/**
* Throw an exception if a clone is attempted
*
* @access public
*/
public function __clone()
{
throw new \Exception('Attempt to clone ' . __CLASS__);
}
/**
* Disconnect from the server
*
* @access public
*/
public function __destruct()
{
$this->disconnect();
}
}