Search This Blog

May 28, 2012

PHP: Example of OOP - Database Class

One of the common nightmares for developers is to maintain repeated code in a single program which happen to be declared over and over again. Basically, what I'm trying to say is that it is frustrating to mae simple changes on the code yet finding yourself debugging the program due to since there are so many untracked code. Thanks to immersion of Object Oriented Programming which lessen the bummer by introducing the DRY (Don't Repeat Yourself). A tenet that allows you to group similar tasks into classes to avoid repeatition and make an easy maintain code. It also save your time since you don't need to keep declaring your code over and over again. If you haven't learn OOP, it's time for you to step up, you can visit and learn more about PHP Objected Oriented Programming by the following resources.

I have here an example of OOP code and please be reminded that this is only for demonstration purposes. This class is called "Database". It allows you to query, fetch record etc... by using the methods defined on the class.

First File:

class Database 
 public $host     = "hostname";
 public $database = "databasename"; 
 public $username = "username"; 
 public $password = "password"; 
 public $conId    = 0;  
 public $queryId  = 0;  
    public $record   = array();  
    public $row;          
    public $errNo    = 0; 
    public $error    = ""; 
 //call connection method upon constructing 
 public function __construct(){
 //connection to the database
 public function createConnection() 
        if( 0 == $this->conId ) 
            $this->conId=mysql_connect( $this->host, $this->username, $this->password ); 
        if( !$this->conId ) 
   $this->stopExec( "Trying to connect.... Result: failed" ); 
        if( !mysql_query( sprintf( "use %s", $this->database ), $this->conId ) ) 
            $this->stopExec( "cannot use database ".$this->database ); 

 //execute query string 
 public function query($queryString) 
  $this->queryId = mysql_query( $queryString, $this->conId ); 
        $this->row = 0; 
        $this->errNo = mysql_errno(); 
        $this->error = mysql_error(); 
        if(!$this->queryId ) 
   $this->stopExec( "Invalid SQL String: ".$queryString ); 
  return $this->queryId; // return the resource id of query. You can either then call fetchRecord method. 
 public function fetchRecord($type = 1)
  //1 --> for array
  //2 --> for object
  if($type == 1){
   $record = mysql_fetch_array($this->queryId); //return array
   $record = mysql_fetch_object($this->queryId); //return object
  return $record;
 //stop the execution of query when there's an error
 public function stopExec( $msg ) 
  printf( "Database error: %s <br>n", $msg ); 
  printf( "MySQL Error: %s (%s)<br>n", $this->errNo, $this->error ); 
 //get the number of row
 public function numRows() 
  return mysql_num_rows( $this->queryId ); 


To try the code above. Save it as "database.php" then create another php file and paste the code below. Make sure that the two files are on the same directory and you had change the following variable values on the database class.

 public $host     = "hostname";
 public $database = "databasename"; 
 public $username = "username"; 
 public $password = "password"; 
Second file:

$db = new Database();
$sql = "SELECT * from tablename"; 

Since, it is only for demonstration, it is not advisable to use this code for big project because of insufficiency. Using ORM (Object Relation Mapping) is still the best way in managing database in larger project. Although you can still use this is small scale project provided that you still need to edit their visibility properties etc. I hope this somewhat helpful to all. 


Mfana Ronald Conco said...

Nice read, please attach a note to say that it's only for demonstration purpose - use an ORM for the real implementation.

Janzell Jurilla said...

Hi Mfana,

Yes indeed. It is only for demonstration purposes. And sorry I forgot to put a disclaimer at the bottom. Thanks for bringing this up.


Bergen CountyNJ said...

It would be helpful if you explained why is this code insufficient for a production environment. Is it insecure? Inefficient? Would it suffice for a project that is anticipated to stay small? What are the minimum additions required to make this safe for a small application?

sarabjeet said...

Great post indeed and thanks for all the information, it was very helpful i really like that you are providing information on PHP and MYSQL with basic JAVASCRIPT,being enrolled in i was looking for such information online to assist me on php and mysql and your information helped me a lot. Thanks.

W Shiyam said...
This comment has been removed by the author.
W Shiyam said...

A very useful post, I really appreciate it . . . and I would like to ask a Help as well; using this class how can i "Display Multiple Records" ? . . .Plz . .
Thank you . .

Hipsteriai ant lsd said...

$this->conId=mysql_connect( $this->host, $this->username, $this->password );

don't forget to use mysql_set_charset() to select right charset...

Hipsteriai ant lsd said...


and use mysqli or pdo. mysql driver still works only for old app backward compatibility!

Web development Company said...

Cool! I love your site. Very well done. Thanks for organizing everything to where I can find it easier. Keep up the good work! I love it!