Database Extraction Layer

heya all, hers part 4 of my framework tutorial and today were going to be looking at Database Extraction

Now when i say Database Extraction what im talking about is a kind of layer between your php and your database!

now most people just do mysql query where its needed.. but the problem with this is, you have to change it for every query.. with the DBL you only have to do it within the object were about to create, thus changing for all your queries across the site !

heres a visual example so you can understand!

As you can see the process above shows why a database extraction layer is good!

You "Pages" asks the "Registry" to send a command to the "DBL" witch then calls the supplies method and talks to the MySQL server! witch returns the data

ok so in this object were going to build as follows
  • Connection Handler
  • Query handler
  • Looping handles (auto loop resuslts so you dont have to)
  • Free Results
  • Connection desctruct


The result we want to achieve is to have have to only do 1 line of code in our view pages and fetch back an array of results!

Any way enough explaining already lets start doing something !

We need to create a config file to hold our database connection info in so lets go ahead and do that !

Create a new file called config.php

Add this code and populate with your database server information!

PHP Code: 
<?php
if(!defined('BASE_PATH')){exit;} // always remember ppl

/*
    * The reason the config is an stdClass is so its an object like everything else within the registry
*/
$_CONFIG = new stdClass();

//Create a blank stdClass for the database

$_CONFIG->database = new stdClass();

//Lets add some configs
$_CONFIG->database->hostname = 'localhost';
$_CONFIG->database->username = 'root';
$_CONFIG->database->password = '';
$_CONFIG->database->database = 'myFramework';

//Also lets set a charset
$_CONFIG->database->charset = 'UTF8';

//All other config infoirmation will be here as we go along!
?>
Ok save to Save the file in /system/Config.php


ok so now thats save we can add it to our registry!

NOTE: Always set the config to the registry FIRST so object constructors can have full use of them as there primary object

Ok open up your startup.php file now and do the following

Find:
PHP Code: 
include SYSTEM_BASE_PATH . '/engine/Input.php'; 
Add before:
PHP Code: 
include . SYSTEM_BASE_PATH . '/Config.php'; 
Now we need to add it to the registry so

Find:

PHP Code: 
Registry::set('Input', new Input()); 
Add before:
PHP Code: 
Registry::set('Config', $_CONFIG); 
Ok so now we have the config included we can access it anywhere within the scope of the script with

PHP Code: 
Registry::get('Config')

//Or for database
Registry::get('Config')->database 
OK save all files and everything should be all gravy!!


Ok now lets move on to the DBL!

Firstly let me say that were not going to do an auto connect because some pages we might not use a database so it will save resources in that sence!

PHP Code: 
<?php
if(!defined('BASE_PATH')){exit;}

Class Database{

    //holder for DB settings, Private so hackers cant get then from an external method like RFI
    private $config;

    //Resource containers
    private $connection = false;
    private $database_selection = false;
    private $query = false; // this will hold the query resource so we can do method passing

    function __construct()
    {
        //We get the settings and store them in a private var, also removing old ones
        $this->config = Registry::get('Config')->database;        
        Registry::get('Config')->database = false;
    }

    /*
        Heres the connect function!
    */

    public function connect()
    {
        //Firstly connection
        $this->__connect();

        //Now select DB
        $this->__selectDatabase();

        //Set database charsets as defined in config!
        $this->query(sprintf("SET NAMES %s",$this->config->charset));
        $this->query(sprintf("SET CHARACTER SET %s",$this->config->charset));
        $this->query(sprintf("SET CHARACTER_SET_CONNECTION = utf8",$this->config->charset));
        $this->query("SET SQL_MODE = '' ");
    }

    //Ok lets build a mysql_query function
    public function query($query)
    {
        if($this->isReady())
        {
            $this->query = mysql_query($query) or trigger_error('Error perforing last query! : ' . mysql_error(),E_USER_ERROR);

            //We will return true because if if theres no error triggered then must be true!
            return true;
        }
    }

    //ok theres the query out the way lets do a fetch associative method
    
    public function getAssoc() //* means array of all results
    {
        if($this->query) // make sure we have a query
        {
            //We create a blank class to hold multiple information in
            $results = new stdClass();

            //Get the number of rows
            $results->count = $this->getNumRows();

            //Create a blank array to hold all the rows within
            $results->rows = array();

            //Loop the results
            while($row = $this->getSingleRow())
            {
                 $results->rows[] = $row;
            }

            //Here we can set the first row into its own variable if needed
            $results->first_row = (isset($results->rows[0]) ? $results->rows[0] : false);

            //return the object to the initial call
            return $results;
        }
    }

    public function getSingleRow()
    {
        if($this->query)
        {
            return mysql_fetch_assoc($this->query,$this->connection);
        }
    }

    public function getNumRows()
    {
        if($this->query)
        {
            return mysql_num_rows($this->query,$this->connection);
        }
    }

    //We can use to check if we were able to make queries 
    public function isReady()
    {
        return ($this->connection !== false && $this->database_selection !== false);
    }

    private function __selectDatabase()
    {
        if($this->connection)
        {
            $this->database_selected = mysql_select_db($this->config->database,$this->connection);
            //Make sure we have selected a database
            if(!$this->database_selection)
            {
                trigger_error('Could not find database',E_USER_ERROR);
            }
        }else
        {
            trigger_error('Attempted to select a database with no connection',E_USER_ERROR);
        }
    }    

    private function __connect()
    {
        return $this->connection = mysql_connect(
            $this->config->hostname,
            $this->config->username,
            $this->config->password
        ) or trigger_error('Mysql connection failure',E_USER_ERROR);
    }

    function __desctruct()
    {
        if($this->connection)
        {
            mysql_close($this->connection);
        }

        //unset avariables
        unset($this->config,$this->database_selection,$this->query);
    }

}
?>

Ok so there you have it, a simple abstraction layer that we will work with more later on for things like security ect!

for now we need to save the file as follows

system/engine/Database.php

and then we include in the startup file! you should know how to do that by now

and follow by the adding the database to the registry with!

PHP Code: 
Registry::set('Database',new Database()); 
heres an example of why its so important to have a DBL

its pretty simple to use, just do the following in your view pages!

PHP Code: 
<?php

//include the startup etc

$database = Registry::get('database');

//Connect
$database->connect();

//Query
$database->query('SELECT * FROM downloads');

//no need for loops in view pages
$reuslts = $database->getAssoc();

//Remember we have first row,count,and all rows help in the $results stdClass
?>
litewarez Reviewed by litewarez on . Tutorial [Creating a PHP Framework] {advanced} (PART 4) Database Extraction Layer heya all, hers part 4 of my framework tutorial and today were going to be looking at Database Extraction Now when i say Database Extraction what im talking about is a kind of layer between your php and your database! now most people just do mysql query where its needed.. but the problem with this is, you have to change it for every query.. with the DBL you only have to do it within the object were about to create, thus changing for all your queries across the Rating: 5