Database Class

timtamboy63 · Jul 23, 2010

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:

PHP:

//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 :P

Exel · Jul 23, 2010

Nice, thanks ;D

rapid-rockzz · Jul 23, 2010

thanks a lot dude

preety good script

Comkid · Jul 23, 2010

No offence, but why not just use PDO?

timtamboy63 · Jul 23, 2010

1: I never knew about it :P
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 · Jul 23, 2010

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

timtamboy63 · Jul 23, 2010

eh, i still dont wanna learn something i dont have to :P
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 · Jul 23, 2010

change

PHP:

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

to

PHP:

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

So that you can do

PHP:

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

and i did giggle at:

PHP:

                $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

.

Comkid said:
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 · Jul 23, 2010

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:

Code:

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

PHP:

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:

PHP:

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:

PHP:

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 · Jul 23, 2010

Nice One tim & good explaination Lite

timtamboy63 · Jul 23, 2010

Wow thats really complicated, maybe I'll get it once I do a bit more PHP, I've only been doing it for a week now :P

Also I didnt particularly get the fetchObject thing, are there any links to explain it? I havent encountered the as keyword before.

Also yeah, I noticed that I was escaping the whole string :\
Luckily I realised and split it into different functions

Anyway, thanks for taking the time to post such a lengthy reply :P

litewarez · Jul 23, 2010

Fetch object is simple

think of an object as a class

PHP:

class User
{
    var $name = "Litewarez";
    var $rank = "Administrator";
}

Then when you do Fetch object it returns the values in a class, so

PHP:

$Object = $database->fetchObject();
$Array = $database->fetchArray();

echo $Array['name'];// Litewarez
echo $Object->name;// Litewarez

so the names are the database colums,

so if i had a db table users with 3 columns (id,name,pass) i can use $Object->pass for example.

Heres some more examples that isn't really DB related

PHP:

$Object = new stdClass(); //Blank class that can be used like an array

//Slandered variables
$Object->IAmTheChosenOne = true;
$Object->MyFatherIsABasterd = false;
$Object->MyMotherSucksYouKNowWhat = "?";

//Inner Array
$Object->array = array("hello" => "helloValue");
$Object->array['hello'];

//Inner Object
$Object->AnotherObject = new StdClass();
$Object->AnotherObject->key = "value";

timtamboy63 · Jul 24, 2010

Hm, I think I get it, but I don't really get the advantage it has over fetch_array?

Also when you said so i can do:

PHP:

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

is that fetcharray meant to be fetch Object()? because you told me to replace it?
Anyway, I will be doing some more research into it, thanks

litewarez · Jul 24, 2010

yea FetchObject();

the advantage is the style of coding, you become naturally to Object Using adn help you grow with OOP.

also take this idea:

Lets say i have a table called users with the columns (id,username,password,hash,reg_date)

and i created a class called GeneralUser

PHP:

class GeneralUser
{
    var $id;
    var $username;
    var $password;
    var $hash;
    var $regdate;


    public function UpdateName($new_username)
    {
         //Run Query To Update Id
    }
}

This object can be used as a fetchObject so an example

PHP:

$resource = mysql_query("SELECT * FROM users");

while($user = mysql_fetch_object($resource,"GeneralUser"))
{
    if($user->id > 200)
    {
        $user->UpdateName("new username");
    }
}

this allows us to apply "methods" as such specifically for table rows.

Most programmers would normally code a username update block a few times but by created an OOP version you only have to code it once and use it when needed, this is what OOP is about, creating code that can be used over and over again in multiple formats as such.

timtamboy63 · Jul 24, 2010

Ah I see, thanks

Database Class

timtamboy63

Active Member

Exel

Active Member

rapid-rockzz

Active Member

Comkid

Active Member

timtamboy63

Active Member

Comkid

Active Member

timtamboy63

Active Member

litewarez

Active Member

litewarez

Active Member

GoPantheoN

Active Member

timtamboy63

Active Member

litewarez

Active Member

timtamboy63

Active Member

litewarez

Active Member

timtamboy63

Active Member