Design a sql query class
Moderator: General Moderators
Design a sql query class
I want to design a sql query class which takes all the sql queries and process. I struggled of what are the variables i should have in my class. If some one knows the answer please tell me. thank you
- Christopher
- Site Administrator
- Posts: 13596
- Joined: Wed Aug 25, 2004 7:54 pm
- Location: New York, NY, US
Re: Design a sql query class
This is a much harder problem than you might think. What are your ideas about the kinds of problems you want to solve?
(#10850)
Re: Design a sql query class
Initially I want to execute select, delete, insert and update queries using the sql class. In the first stage it doesn't want to execute complex queries. My main expect here is re-usability. Or is there another way to keep re-usability?
- Christopher
- Site Administrator
- Posts: 13596
- Joined: Wed Aug 25, 2004 7:54 pm
- Location: New York, NY, US
Re: Design a sql query class
delete, insert and update are pretty easy, select is very, very difficult.
(#10850)
Re: Design a sql query class
And of course a lot depends on what exactly you are selecting, inserting, deleting and updating. A few fields of a single table or a large set of data spread out over multiple related tables?
-
crazycoders
- Forum Contributor
- Posts: 260
- Joined: Tue Oct 28, 2008 7:48 am
- Location: Montreal, Qc, Canada
Re: Design a sql query class
What i would be more concerned of is yet reinventing the wheel. There must be kajillions of classes out there on the web to handle SQL Queries. May i suggest you state the exact nature of your needs and maybe we could direct you to something already built?
Sometimes, objects are not even needed, for example, if it's only for logging and debugging purposes, functions may serve perfectly fine. Just establish a need of what you need that class for. If you can't maybe you don't need a class and should simply stick to the mysql/mysqli/pdo functions!
Sometimes, objects are not even needed, for example, if it's only for logging and debugging purposes, functions may serve perfectly fine. Just establish a need of what you need that class for. If you can't maybe you don't need a class and should simply stick to the mysql/mysqli/pdo functions!
-
take2hikes
- Forum Newbie
- Posts: 14
- Joined: Sat Feb 14, 2009 1:45 pm
Re: Design a sql query class
I am new to PHP and OOP as well. I'm currently working on a Blogging platform ( yes yes, reinventing the wheel- but I'm just trying to learn ).
The following is a Pseudo code excerpt from my DB class:
As you can see, the query_db() is generic. It just takes the query stored in $this->query. It then opens a connection via $this->connect() and sends the query. It also takes an optional true/false value for SELECT queries. If $select is true, then it returns the result from the database query.
I'm sure this is a simple solution, but it works for what I'm planning on doing with it. I'm sure I'll run into some issues with the details, but I'll have to wait until I actually code it.
The following is a Pseudo code excerpt from my DB class:
Code: Select all
//------------------------------------------------------------------------
// CLASS DB
// purpose: contains vars and functions necessary to connect to a
// database and send a query to it.
//------------------------------------------------------------------------
class DB
{
private $db; // var to hold connection pointer
private $query; // var for query
// constructor
function __construct()
{
// initialize all vars to null values
$db = NULL;
$query = NULL;
// end constructor
}
// destructor
function __destruct()
{
// unset all vars once class is out of scope
unset($db);
unset($query);
// end destructor
}
//--------------------------------------------------------------------
// FUNCTION connect
// purpose: protected function to connect to the database
// required args: none
//--------------------------------------------------------------------
private function connect()
{
// define constants for connection info
// open connection to database
// if unable to connect
// return FALSE
// end if
// else connection was successful
// return TRUE
// end else
// end connect function
}
//--------------------------------------------------------------------
// FUNCTION disconnect
// purpose: protected function to disconnect to the database
// required args: none
//--------------------------------------------------------------------
private function disconnect()
{
// if database closed successfully
// return TRUE
// end if
// else connection wasn't closed
// return FALSE
// end else
// end disconnect function
}
//--------------------------------------------------------------------
// FUNCTION query_db
// purpose: protected function to send a query to the database
// optional args: true/false to return query result, ie SELECT query
//--------------------------------------------------------------------
private function query_db($select=false)
{
// if call to connect fails
// return FALSE
// end if
// send query to database
// if query was successful
// if query should return data
// return result
// end if
// return true
// end if
// else query wasn't successful
// return false
// end else
// end query_db function
}
As you can see, the query_db() is generic. It just takes the query stored in $this->query. It then opens a connection via $this->connect() and sends the query. It also takes an optional true/false value for SELECT queries. If $select is true, then it returns the result from the database query.
I'm sure this is a simple solution, but it works for what I'm planning on doing with it. I'm sure I'll run into some issues with the details, but I'll have to wait until I actually code it.
Re: Design a sql query class
I've made something like that:
// DB.php
// SQL.php - The SQL Builder Itself
Usage:
// DB.php
Code: Select all
<?php
class DB
{
public $connection = null;
function __construct()
{
require_once(BASE_DIR . '/application/config/' . __CLASS__ . '.php');
}
function __destruct()
{
$this->Disconnect();
}
function Connect($database, $username, $password, $hostname = 'localhost', $port = 3306)
{
$this->connection = mysql_connect($hostname . ':' . intval($port), $username, $password) or trigger_error(mysql_error(), E_USER_ERROR);
if (is_resource($this->connection) === true)
{
mysql_select_db($database, $this->connection) or trigger_error(mysql_error(), E_USER_ERROR);
return $this->Query('SET NAMES ' . $this->Quote('utf8') . ';');
}
return false;
}
function Disconnect()
{
if (is_resource($this->connection) === true)
{
return mysql_close($this->connection);
}
return false;
}
function Query($sql, $type = 'assoc')
{
if (is_resource($this->connection) === true)
{
$query = mysql_query($sql, $this->connection) or trigger_error(mysql_error(), E_USER_ERROR);
if (is_resource($query) === true)
{
$result = array();
for ($i = 0; $i < mysql_num_rows($query); $i++)
{
$result[$i] = call_user_func('mysql_fetch_' . $type, $query);
}
mysql_free_result($query);
return $result;
}
if ($query === true)
{
switch (strtoupper(substr(trim($sql), 0, strpos(trim($sql), ' '))))
{
case 'INSERT':
return mysql_insert_id($this->connection);
break;
case 'UPDATE':
case 'REPLACE':
case 'DELETE':
return mysql_affected_rows($this->connection);
break;
}
return true;
}
}
return false;
}
function Quote($string)
{
if (get_magic_quotes_gpc() === true)
{
$string = stripslashes($string);
}
if (function_exists('mysql_real_escape_string') === true)
{
if ((isset($this->connection) === true) && (is_resource($this->connection) === true))
{
return '\'' . mysql_real_escape_string(trim($string), $this->connection) . '\'';
}
}
if (function_exists('mysql_escape_string') === true)
{
return '\'' . mysql_escape_string(trim($string)) . '\'';
}
return '\'' . addslashes(trim($value)) . '\'';
}
function Tick($string)
{
$string = explode('.', str_replace('`', '', $string));
foreach ($string as $key => $value)
{
if ($value != '*')
{
$string[$key] = '`' . trim($value) . '`';
}
}
return implode('.', $string);
}
}
?>
Code: Select all
<?php
/*
* JOINs and other complex parts where removed to avoid complexity.
*/
class SQL
{
public $sql = array();
function __toString()
{
$result = null;
if (array_key_exists('query', $this->sql) === true)
{
$result = $this->sql['query'];
if (in_array(strtoupper(substr($this->sql['query'], 0, strpos($this->sql['query'], ' '))), array('SELECT', 'UPDATE', 'DELETE')) === true)
{
if (array_key_exists('where', $this->sql) === true)
{
$result .= "\n" . implode("\n", $this->sql['where']);
}
if (array_key_exists('order', $this->sql) === true)
{
$result .= "\n" . $this->sql['order'];
}
if (array_key_exists('limit', $this->sql) === true)
{
$result .= "\n" . $this->sql['limit'];
}
}
$result .= ';';
}
return $result;
}
function Delete($tables, $ignore = false)
{
$this->sql = array
(
'query' => 'DELETE ' . (($ignore === true) ? 'IGNORE ' : ''),
);
$tables = (is_string($tables) === true) ? explode(',', $tables) : $tables;
foreach ($tables as $key => $value)
{
$tables[$key] = $this->DB->Tick($value);
}
$this->sql['query'] .= 'FROM ' . implode(', ', $tables);
return $this;
}
function Insert($tables, $data, $ignore = false)
{
$this->sql = array
(
'query' => 'INSERT ' . (($ignore === true) ? 'IGNORE ' : ''),
);
$tables = (is_string($tables) === true) ? explode(',', $tables) : $tables;
foreach ($tables as $key => $value)
{
$tables[$key] = $this->DB->Tick($value);
}
foreach ($data as $key => $value)
{
$data[$key] = $this->DB->Tick($key) . ' = ' . $this->DB->Quote($value);
}
$this->sql['query'] .= 'INTO ' . implode(', ', $tables) . ' SET ' . implode(', ', $data);
return $this;
}
function Limit($rows, $offset = null)
{
$this->sql['limit'] = 'LIMIT ';
if (is_null($offset) === false)
{
$this->sql['limit'] .= intval($offset) . ', ';
}
$this->sql['limit'] .= intval($rows);
return $this;
}
function Order($fields, $order = 'ASC')
{
$fields = (is_string($fields) === true) ? explode(',', $fields) : $fields;
foreach ($fields as $key => $value)
{
$fields[$key] = $this->DB->Tick($value);
}
$this->sql['order'] = 'ORDER BY ' . implode(', ', $fields) . ' ' . strtoupper(trim($order));
return $this;
}
function Select($tables, $fields = '*', $distinct = false)
{
$this->sql = array
(
'query' => 'SELECT ' . (($distinct === true) ? 'DISTINCT ' : ''),
);
$fields = (is_string($fields) === true) ? explode(',', $fields) : $fields;
$tables = (is_string($tables) === true) ? explode(',', $tables) : $tables;
foreach ($fields as $key => $value)
{
$fields[$key] = $this->DB->Tick($value);
}
foreach ($tables as $key => $value)
{
$tables[$key] = $this->DB->Tick($value);
}
$this->sql['query'] .= implode(', ', $fields) . ' FROM ' . implode(', ', $tables);
return $this;
}
function Update($tables, $data, $ignore = false)
{
$this->sql = array
(
'query' => 'UPDATE ' . (($ignore === true) ? 'IGNORE ' : ''),
);
$tables = (is_string($tables) === true) ? explode(',', $tables) : $tables;
foreach ($tables as $key => $value)
{
$tables[$key] = $this->DB->Tick($value);
}
foreach ($data as $key => $value)
{
$data[$key] = $this->DB->Tick($key) . ' = ' . $this->DB->Quote($value);
}
$this->sql['query'] .= implode(', ', $tables) . ' SET ' . implode(', ', $data);
return $this;
}
function Where($data, $operator = 'LIKE', $merge = 'AND')
{
foreach ($data as $key => $value)
{
$this->sql['where'][] = ((empty($this->sql['where']) === true) ? 'WHERE' : $merge) . ' ' . $this->DB->Tick($key) . ' ' . $operator . ' ' . $this->DB->Quote($value);
}
return $this;
}
}
?>
Code: Select all
$DB = new DB();
$SQL = new SQL();
echo '<pre>';
print_r($DB->Query($SQL->Select('table')));
echo '</pre>';
$data = array
(
'field1' => 'value1',
'field2' => 'value2',
);
var_dump($DB->Query($SQL->Insert('table', $data)));
var_dump($DB->Query($SQL->Update('table', $data)));
echo '<pre>';
print_r($DB->Query($SQL->Select('table')));
echo '</pre>';