Implementing your database access in PHP as a layer.
By Devadutta D Ghat
e-mail: devadutta(at)acm.org
NOTE: This is the first tutorial I have authored. May not be very good.
Let us handle the task in a question and answer format.
What will I be able to do after I read this tutorial?
You will be able to implement a database access layer.
This will make the backend of your PHP driven site easier to maintain.
Why should I implement a database access layer?
By implementing database access as a layer, you have the following advantages
1. You can migrate from one data base to another (eg mysql to postgreSQL) very easily.
All you have to do is to change the database access layer code.
2. If you want to implement changes to your database access,
you have to modify only that layer's code instead of the whole code.
3. Layered implementation of a system is always easier to
understand and maintain.
How do I begin?
To begin with, you have to define a class in PHP. This class will create an object,
which will contain code for the db access.
Here, I will present the code for MYSQL, which you can easily modify to other databases.
The code for the layer class. Let us assume this is saved in the file database_layer.php
How do I access the layer?
Here is a simple code.
How do I implement changes?
Simple. Let us say you want to switch to postgres,
just change the code in the class sql_db.
You dont have to change the code where the class is used,
for example in the file above, you dont have to change
,
you just have to change fetchrowassoc() in the class.
If you have any doubts, you can mail me. No spam please.
By Devadutta D Ghat
e-mail: devadutta(at)acm.org
NOTE: This is the first tutorial I have authored. May not be very good.
Let us handle the task in a question and answer format.
What will I be able to do after I read this tutorial?
You will be able to implement a database access layer.
This will make the backend of your PHP driven site easier to maintain.
Why should I implement a database access layer?
By implementing database access as a layer, you have the following advantages
1. You can migrate from one data base to another (eg mysql to postgreSQL) very easily.
All you have to do is to change the database access layer code.
2. If you want to implement changes to your database access,
you have to modify only that layer's code instead of the whole code.
3. Layered implementation of a system is always easier to
understand and maintain.
How do I begin?
To begin with, you have to define a class in PHP. This class will create an object,
which will contain code for the db access.
Here, I will present the code for MYSQL, which you can easily modify to other databases.
The code for the layer class. Let us assume this is saved in the file database_layer.php
| Code: |
|
<? /*database_layer.php*/ class sql_db { /* Variables Held by the class */ var $server; // Server name var $uname; //User name var $passwd; //password This feild is actually not required, but let us keep it for good understanding var $database_name; //Database name var $connect_id; //Database connection id var $query_result; //Place to hold the query result var $row; var $numrows; function sql_db($db_host,$db_uname,$db_psswd,$db_name) { /*Constructor. Calls connect whenever an object is created */ $this->server=$db_host; $this->uname=$db_uname; $this->passwd=$db_passwd; $this->database_name=$db_name; $this->connect_id=mysql_connect($db_host,$db_uname,$db_passwd) or die(mysql_error()); mysql_select_db($db_name); } function query($q) { /*Execeutes the query $q and stores the result in query_result */ $this->query_result=mysql_query($q,$this->connect_id) or die(mysql_error()); return $this->query_result; } function fetchrow($qid=0) { /* Fetches a row of the executed query */ if(!$qid) { $qid=$this->query_result; } if($qid) { $this->row=mysql_fetch_array($qid); return $this->row; } else { return false; } } function fetchrowassoc($qid=0) { /* Fetches a row of the executed query into an associative array*/ if(!$qid) { $qid=$this->query_result; } if($qid) { $this->row=mysql_fetch_assoc($qid); return $this->row; } else { return false; } } function fetchnumrows($qid=0) { /* Fetches number of rows in the executed query */ if(!$qid) { $qid=$this->query_result; } if($qid) { $this->numrows=mysql_num_rows($qid); return $this->numrows; } else { return false; } } function fetchnumaffected($qid=0) { /* Fetches number of affected rows in the executed query */ if(!$qid) { $qid=$this->query_result; } if($qid) { $this->numrows=mysql_affected_rows($qid); return $this->numrows; } else { return false; } } } ?> |
How do I access the layer?
Here is a simple code.
| Code: |
|
<? include("database_layer.php"); //Include the layer code in your file $t=new sql_db("localhost","root","","test"); //Object creation $t->query("select * from music"); //Simple query while($res=$t->fetchrowassoc()) { echo "<br>"; foreach($res as $key => $abc) { echo "$key = {$abc}<br>"; } } ?> |
How do I implement changes?
Simple. Let us say you want to switch to postgres,
just change the code in the class sql_db.
You dont have to change the code where the class is used,
for example in the file above, you dont have to change
| Code: |
| while($res=$t->fetchrowassoc()) |
you just have to change fetchrowassoc() in the class.
If you have any doubts, you can mail me. No spam please.
