Creating Object Oriented Database Connection using PHP

This tutorial will help you create a simple object oriented implementation of Database connection using the PHP scripting language.

First, we will create the parent class which contains all the abstract functions for our database transactions.

abstract class Database
{
abstract protected function connect($host, $username, $password, $dbname);
abstract protected function query($query);
abstract protected function fetchRow($rs);
abstract protected function fetchAll($rs);
abstract protected function getNumRows();
abstract protected function disconnect();
}

Then create a child class. This is a sample class using the MySQL Database Engine.

class MySqlDatabase extends Database
{

private static $instance = null;
private $numRows = null;
private $connect = null;

private function __construct()
{}

public static function getInstance()
{
if(null == self::$instance)
self::$instance = new MySqlDatabase();
return self::$instance;
}

public function connect($host, $username, $password, $dbname)
{
if( !$this->connect = mysql_connect($host, $username, $password) )
{
die("cannot connect to the database");
}

if( !$select = mysql_select_db($dbname) )
{
die("$dbname cannot be selected from the database");
}
return $this->connect;
}

public function query($query)
{
if( !$rs = mysql_query($query) )
{
die("$query cannot execute this query");
}

if( eregi("^SELECT",$query) )
{
$this->numRows = mysql_num_rows($rs);
}
return $rs;
}

public function fetchAll($rs)
{
$rows = array();
while( $row = mysql_fetch_array($rs, MYSQL_ASSOC) )
{
$rows[] = $row;
}
return $rows;
}

public function fetchRow($rs)
{
if( !$row = mysql_fetch_array($rs, MYSQL_ASSOC) )
{
die("Error fetching row");
}
return $row;
}

public function getNumRows()
{
return $this->numRows;
}

public function disconnect()
{
if( !$close = mysql_close($this->connect) )
{
die("Error closing connection");
}
return $close;
}
}

Sample usage:

$instance = MySqlDatabase::getInstance(); # will return an instance of mysql database connection class
$instance->connect(HOST, USERNAME, PASSWORD, DBNAME);
$instance->query("SELECT...");

You can also try using different database engine implementation like Oracle or SQL Server.

Hope you find it useful :)