Database Class

[timtamboy63]

Hey guys,

I'm learning OOP PHP and I created a basic class to help out with database handling.
Here it is, fell free to use and modify it. If you do modify it, it'd be cool if you post here as it would be interesting to see what people have done with it

Anyway:

//Basic Class to deal with databases, created by timtamboy63
class Database {
    protected $dbuser = 'root';
    protected $dbpass = 'passwordhere';
    protected $dbhost = 'localhost';
    protected $dbname;
    public $connection;
    public $queryresult;
    public $query;
    public $numrows;
    
    function Database($dbname){
        //the constructor function
        $this->dbname = $dbname;
    }
    function connect(){
        $this->connection = mysql_connect($this->dbhost, $this->dbuser, $this->dbpass) or die(mysql_error());
        mysql_select_db($this->dbname,  $this->connection) or  die('Database could not be selected, Error: ' .  mysql_error());
        return true;
    }
    function disconnect(){
        mysql_close($this->connection) or die('Could not close database. Error: ' . mysql_error());
        return true;
    }
    function query($query, $type = 2){
        //type can be either 0 which does not do anyhting to it
        //it can be 1 which will just sanitize the mysql query with mysql_real_escape_string
        //or  it can be 2 which will also apply htmlentities(and protect  against  xss), $this is reccomended for anything that gets stored in  the database  and displayed later
        switch($type){
            case 0:
                $this->query = trim($query);
                $this->queryresult = mysql_query($this->query);
                return true;
                break;
            case 1:
                $this->query = mysql_real_escape_string(trim($query));
                $this->queryresult = mysql_query($this->query);
                return true;
                break;
            case 2:
                $this->query = htmlentities(mysql_real_escape_string(trim($query)));
                $this->queryresult = mysql_query($this->query);
                return true;
                break;
            default:
                die('Function  query in class database used incorrectly.  The second argument only  accepts the values 0, 1 and 2(default &  most secure). Read the  source for more information');
                return false;
                break;
        }
        
    }
    function fetchArray(){
        while($row = mysql_fetch_array($this->queryresult)){
            //change this to whatever you want.
            print $row['id'] . '
';
        }
    }
    function numRows(){
        $this->numrows = mysql_num_rows($this->queryresult);
        return $this->numrows;
    }
    
    
}

/* Example usage:
  
$phonebook = new Database('Phonebook');
$phonebook->connect();
$query = 'SELECT * FROM phonebook';
$phonebook->query($query);
$phonebook->fetchArray();
$phonebook->disconnect();

 */



?> 

More on chrixel.com

timtamboy63 Reviewed by timtamboy63 on 23rd Jul 2010. Database Class Hey guys, I'm learning OOP PHP and I created a basic class to help out with database handling. Here it is, fell free to use and modify it. If you do modify it, it'd be cool if you post here as it would be interesting to see what people have done with it http://chrixel.com/forums/public/style_emoticons/default/tongue.gif Anyway: //Basic Class to deal with databases, created by timtamboy63 class Database { protected $dbuser = 'root'; Rating: 5

[Exel]

Nice, thanks ;D

[rapid-rockzz]

thanks a lot dude

preety good script

[Comkid]

No offence, but why not just use PDO?

[timtamboy63]

1: I never knew about it
2: PDO seems quite complex, and i'd rather not learn something that complex if im hardly going to use it. This calls is just my common db functions rolled into one class

[Comkid]

PDO has multiple DB types built in, and it has automatic protection from SQL injection, also, not that advanced

[timtamboy63]

eh, i still dont wanna learn something i dont have to
Also I prefer knowing how things are done, just incase I ever have to do it manually.
Btw i've updated it, some of the functions were retarded

[litewarez]

change

function fetchArray(){
        while($row = mysql_fetch_array($this->queryresult)){
            //change this to whatever you want.
            print $row['id'] . '
';
        }
    } 

to

function FetchObject()
{
    $Holder = array();
    while($row = mysql_fetch_object($this->queryresult))
    {
        $Holder[] = $row;
    }
    return $Holder;
} 

So that you can do

foreach($phonebook->fetchArray() as $Object)
{
    echo $Object->ColumnName;
} 

and i did giggle at:

                $this->query = mysql_real_escape_string(trim($query));
                $this->queryresult = mysql_query($this->query);
                return true; 

your only ment to escape the variables inside the string not the whole string, that will fuck everything up .

No offence, but why not just use PDO?

PDO is very much the same but sometimes you need to go custom for big projects instead of using other peoples libs

[litewarez]

Also i forgot to explain tht you should use Wrappers for a DBL

Let me explain a little as some people been talking about PDO:

Creating your own DBAL (Database Abstraction Layer) allows you to build a site around a database system and switch db types at any point from MySql to MySqli etc.

Creating a DBAL is not really that hard its only constructed of 2 primary elements:

• Layer Interaction Class
• Layers Themselves

For Example:

|------------|              |---------------|              |---------------|
|     User      | <------> | Layer Interact | <------> |       Layer       |
|------------|              |---------------|              |---------------|

Now the Layer is a class that specifically talks to the database like mysql_connect and mysql_sect_db etc.

so heres a php example

include "database.php";
$Database = new Database(Layers::MySql,"localhost","root","","phpmyadmin",false);

if($Database->Connect())
{
    if($Database->Query("SELECT * FROM pma_history"))
    {
        echo "Query Ok";
    };
} 

No with DBAL you want to be able to change Layers::MySql to Layers::MySqli without breaking the quires

and the way this is done is with the Layer and the Layer Interactor.

Heres a example of The Layer Interactor:

class Database
{
    //Database Specifics
    private $ConnectionParams,$Layer,$ConnectionResource;
    
    //Directories / Files
    private $BaseDirectory,$LayerFile;
    
    //States
    public $connected = false;
    
    function __construct($DBLayer,$Hostname,$Username,$password,$database,$autoconnect = false)
    {
        $this->ConnectionParams = array(
             'Layer'    => $DBLayer,
            'Hostname'    => $Hostname,
            'Username'    => $Username,
            'Password'    => $Password,
            'Database'    => $Database,
            'Connect'    => (bool)$autoconnect
        );
        
        //Set the BaseDirectory
        $this->BaseDirectory = str_replace("\\","/",dirname(__FILE__));
        
        if(is_dir($this->BaseDirectory) === false)
        {
            trigger_error("Layers directory is undextable, please create or fix.",E_USER_ERROR);
        }
        
        //Set the LayerFile
        $this->LayerFile = $this->BaseDirectory . '/layers/' . $DBLayer . '.layer.php';
        
        if(file_Exists($this->LayerFile) === false)
        {
            trigger_error(sprintf("Unable to load layer for <b>%s</b>",$DBLayer),E_USER_ERROR);
        }
        
        //Load the required layer
        require_once $this->LayerFile;
        
        $LayerClass = "Layer_" . $DBLayer;
        
        if(!class_exists($LayerClass))
        {
            trigger_error(sprintf("Unable to find class for database layer <b>%s</b>",$LayerClass),E_USER_ERROR);
        }
        
        $this->Layer = new $LayerClass($this->ConnectionParams);
        
        if($this->ConnectionParams['Connect'] === true)
        {
            $this->Connect();
        }
    }
    
    public function Connect()
    {
        if($this->connected === true)
        {
            return true;
        }
        
        if(false !== ($this->ConnectionResource = $this->Layer->EstablishConnection()))
        {
            $this->connected = true;
            
            //SelectDatabase
            return $this->Layer->SetDatabase() ? true : false;
        }
        return false;
    }
    
    public function ChangeDatabase($database)
    {
        return $this->Layer->SetDatabase($database);
    }
    
    public function Query($Query)
    {
        return $this->Layer->Query($Query);
    }
    
    
}

//Constant Layers
abstract class Layers
{
        const MySql    = "mysql";
    const MySqli    = "mysqli";
    //Add more as you develope those
    
}

//Interface for layers
interface ILayer
{
    public function __construct($ConnectionParams);
    public function EstablishConnection();
    public function Query($Query);
}
?> 

Now if you notice within the COnstructor if Database im looking at what Database Type the user has selected and including the Layer from the Layers Folder.

The layers methods should never change, Just the Logic!

so heres the MySql Layer script:

class Layer_mysql implements ILayer
{
    private $ConnectionParams,$ConnectionResource,$LastQuery;
    
    public function __construct($ConnectionParams)
    {
        $this->ConnectionParams = $ConnectionParams;
    }
    
    public function EstablishConnection()
    {
        try
        {
            $this->ConnectionResource = mysql_connect($this->ConnectionParams["Hostname"],$this->ConnectionParams["Username"],$this->ConnectionParams["Password"]);
            if($this->ConnectionResource !== false)
            {
                return $this->ConnectionResource;
            }
        }catch(Exception $e){}
        
        return false;
    }
    
    public function SetDatabase($Database = false) //Change / Select
    {
        $Database = ($Database !== false) ? $Database : $this->ConnectionParams['Database'];
        try
        {
            return mysql_select_db($Database);
        }catch(Exception $e)
        {
            return false;
        }
        
        return false;
    }
    
    public function Query($Query)
    {
        try
        {
            $this->LastQuery = mysql_query($Query);
            
            if($this->LastQuery !== false)
            {
                return true;
            }
        }catch(Exception $e)
        {
            return false;
        }
    }
} 

also you will notice that this implements ILayer, this is to make sure the Layer has the right functions inside to be compatible with the Database Interactor.

So if you wanted to make a layer for MySql, you can just change the code within the methods of the class to suite mysql, aslong as the layer only returns the bool,array,object,string and not resources, the data should always stay the same for the user.

Hope this gives you more insight into the DBAL type structure.

[GoPantheoN]

Nice One tim & good explaination Lite