Page 1 of 1

Design a sql query class

Posted: Wed Mar 11, 2009 10:44 pm
by jhack
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

Re: Design a sql query class

Posted: Thu Mar 12, 2009 12:25 am
by Christopher
This is a much harder problem than you might think. What are your ideas about the kinds of problems you want to solve?

Re: Design a sql query class

Posted: Thu Mar 12, 2009 10:23 pm
by jhack
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?

Re: Design a sql query class

Posted: Fri Mar 13, 2009 2:49 am
by Christopher
delete, insert and update are pretty easy, select is very, very difficult.

Re: Design a sql query class

Posted: Fri Mar 13, 2009 3:53 am
by matthijs
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?

Re: Design a sql query class

Posted: Fri Mar 13, 2009 9:11 am
by crazycoders
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!

Re: Design a sql query class

Posted: Mon Mar 16, 2009 10:48 am
by take2hikes
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:

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

Posted: Tue Mar 17, 2009 8:14 pm
by alixaxel
I've made something like that:

// 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);
    }
}
 
?>
 
// SQL.php - The SQL Builder Itself

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;
    }
}
 
?>
 
Usage:

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>';