(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];
}
}
This class allows for easier manipulation of a single table. To instantiate it, simply call it as normal passing as parameters the hostname, the username, the password, and (optionally) the database. Note that this only needs to be done once; subsequent instantiations can be called without passing any parameters
try
{
$databaseOne = new \Models\Database('localhost', 'username', 'password', 'database');
$databaseTwo = new \Models\Database;
}
catch (Exception $e)
{
echo $e->getMessage();
}
To select a row, set the values of the fields, where the fields are represented as properties, and call the select() method passing as the only parameter the table name. This will set the properties of the \Models\Database objects to match the fields of the row selected.
try
{
$database->id = 1;
$database->select('users');
echo $database->name;
}
catch (Exception $e)
{
echo $e->getMessage();
}
// Outputs the name of the first row
To update a record follow the same format, but with the properties representing SET clauses — except the ‘id’ property which represents the WHERE clause — using the update() method.
try
{
$database->id = 1
$database->username = 'Michael'
$database->update('users');
}
catch (Exception $e)
{
echo $e->getMessage();
}
// Updates the username of row 1 to 'Michael'
To delete a row, use the delete() method. Only the “id” property will be used, and this as the WHERE clause.
try
{
$database->id = 1;
$database->delete('users');
}
catch (Exception $e)
{
echo $e->getMessage();
}
// Deletes the row with ID 1
To insert a row use the insert() method passing as properties the SET clauses. On inserting the row the ‘id’ property will be set to the inserted row’s ID.
try
{
$database->username = 'Andrew';
$database->insert('users');
echo $database->id;
}
catch (Exception $e)
{
echo $e->getMessage();
}
// Inserts a new row with username 'Andrew' and returns the row's ID