Now my question is, is there a way to generalize or make all these queries generic
You can't make the queries themselves generic, otherwise they would all be the same

ah ha *cough* but you can make there output the same or, if you like to sound clever, polymorphic. This means that they output with the same names so as to be as similar as possible.
Database Abstraction layers will make your Queries cleaner and provide easy access to do data input checking and prepare statements.
And slow down the application, and prevent you from making database specific optimizations. But that's another story.
Parameterised SQL is the correct way to do things. It makes a SQL injection attack utterly impossible.
Code: Select all
SELECT @message_id := $message_id;
There is a PHP variable in that SQL isn't there? Do you agree that a PHP variable is present? Right, then you can still inject SQL, although I think it does improve things.
SQL should all be in one place. PHP developers may not write good SQL, and Database Administrators may not write good PHP, so don't mix the two together.
This is a very good point I totally agree with.
Here's how I am doing it, applogies for the quantity of code, I think it is necessary though:
Code: Select all
<?php
class TillingDatabaseAccesss
{
/**
* The database connection
*
* @var Mysqli
*/
protected $_db;
/**
* Number of queries executed via exec()
*
* @var int
*/
public $queriesExecuted = 0;
/**
* Whether a transaction is currently open
*
* @var bool
*/
private $_transaction = false;
/**
* @var MagicTable
*/
public $setting; // instance of MagicTable for the 'Setting' table
/**
* Create link to database and instaniate $this->setting
*
* @throws SystemException
*/
public function __construct()
{
$this->_db = new Mysqli(Tilling::$databaseHost, 'xxx', 'xxx', 'xxx');
if (!$this->_db instanceof Mysqli) {
throw new SystemException('Failed to connect to database');
} else {
$this->setting = new MagicTable($this->_db, 'Setting', 'handle', 'value');
}
OF_Field::registerDatabase($this);
}
/**
* Close link to database
*/
public function __destruct()
{
if ($this->_db instanceof Mysqli) $this->_db->close();
}
/**
* Execute a query and throw an exception if it fails.
*
* @param string $q
* @param string $name the name of the function calling exec(); use __FUNCTION__
* @return mysqli_result
*/
protected function exec($q, $name, $write = false)
{
$status = $this->_db->query($q);
$this->queriesExecuted++;
if (!$status) {
$name.= ' generated the following error: '.$this->_db->error;
$errNo = $this->_db->errno;
if ($this->_transaction) {
$this->rollback();
}
throw new QueryException($name, $errNo, $q);
} else {
return $status;
}
if ($write) {
// may want to save this data to another database
}
}
/**
* Commits a query. Get any meta data about the last query that you need
* before calling this.
*/
protected function commit()
{
$this->_db->commit();
$this->_transaction = false;
}
/**
* Rollsback an unsucessful query
*/
protected function rollback()
{
$this->_db->rollback();
$this->_transaction = false;
}
/**
* Begins a transaction
*/
protected function begin()
{
$this->_transaction = true;
$this->_db->query('BEGIN');
}
/**
* Execute a query and return the first column of the first row
*
* @param string $q
* @param string $name
* @return mixed
*/
protected function execSingleValue($q, $name)
{
$result = $this->exec($q,$name);
$this->commit();
$row = $result->fetch_row();
return $row[0];
}
public function escape($str)
{
return $this->_db->escape_string($str);
}
/**
* This method overloading function is used to preprocess all input to a
* query with escape string.
*
* The user of the class will be able to call a private function such _getSetting()
* via call with getSetting() and have the input to _getSetting escaped for him.
*
* You can of course bypass __call() by calling the function _getSetting() directly
* but only when it is defined as public.
*/
public function __call($method, $arg)
{
foreach ($arg as $id => $value) {
if (ctype_digit($value)) {
$arg[$id] = (int)$value;
} else if (is_string($value)) {
$arg[$id] = $this->_db->escape_string($value);
}
}
$method = '_'.$method;
if (method_exists($this, $method)) {
return call_user_func_array(array($this, $method), $arg);
} else {
throw new Exception('Tried to call non-existant method ' . __CLASS__ . '::' . $method.'()');
}
}
}
Then I've got another class, which is massive but here are pieces
Code: Select all
require_once 'TillingDatabaseAccess.php';
class TillingDatabase extends TillingDatabaseAccesss
{
// these three are polymorphic
/**
* Get site title and id
*
* @param int $limit
* @return Mysqli_result
*/
protected function _getRecentSites($limit)
{
$q = 'SELECT siteId AS id,title FROM Site ORDER BY id DESC LIMIT '.$limit;
return $this->exec($q, __FUNCTION__);
}
/**
* Get client name and id
*
* @param int $limit
* @return Mysqli_result
*/
protected function _getRecentClients($limit)
{
// 'name AS title' so that these three recent functions are uniform
$q = 'SELECT clientId AS id,name AS title FROM Client ORDER BY id DESC LIMIT '.$limit;
return $this->exec($q, __FUNCTION__);
}
/**
* Get report title and id
*
* @param int $limit
* @return Mysqli_result
*/
protected function _getRecentReports($limit)
{
$q = 'SELECT reportId AS id,title FROM Report ORDER BY id DESC LIMIT '.$limit;
return $this->exec($q, __FUNCTION__);
}
// this is polymorphic, one delete query for all tables!
/**
* Delete specific rows from any table
*
* @param string $tableName
* @param string $rows
*/
protected function _deleteFrom($tableName, $rows)
{
$rows = implode(',', $rows);
$id = strtolower($tableName[0]) . substr($tableName, 1) . 'Id';
$q = 'DELETE FROM ' . $tableName . ' WHERE ' . $id . ' IN (' . $rows . ')';
$this->begin();
$this->exec($q, __FUNCTION__);
$affected = $this->_db->affected_rows;
$this->commit();
return $affected;
}
...loads more