This article will demonstrate how to create a database abstraction layer using Interfaces in PHP5.
Introduction
An abstraction layer is basically a way of hiding the methods used to implement some kind of functionality. To get an idea of why this is so useful, imagine you have just spent months working on a project for a site that uses a MySQL database. Now you find that you suddenly cannot use MySQL anymore but instead you need to use SQL Server. This is suddenly a major problem! You now need to go through months of work and change every mysql-specific PHP function such as mysql_connect() before your code will work.
This situation is avoided through the use of an abstraction layer.
Code Breakdown
The first part of the abstraction layer is a database object interface. To quote the PHP manual, an object interface “allows you to create code which specifies which methods a class must implement, without having to define how these methods are handled”. In other words, we can set some rules about what classes must be able to do, but without having to worry about how it is done. For example, it is possible to specify that database classes must all allow us to connect to a database through a connect() method, and that they must all allow us to execute a query via a query() method, but the actual details about how this functionality is implemented are not important.
This is the code for the database object interface:
1
2
3
4
5
6
7
8
9
10
11
12
13
| interface DB {
public function connect();
public function error();
public function errno();
public static function escape_string($string);
public function query($query);
public function fetch_array($result);
public function fetch_row($result);
public function fetch_assoc($result);
public function fetch_object($result);
public function num_rows($result);
public function close();
} |
Because an interface is a set of rules for any class which implements it, any classes implementing this interface must have a connect() method, an error() method, an errno() method etc. We have also specified where arguments must be accepted by the methods, for example the fetch_assoc() method must accept a $result argument.
Be aware that the classes implementing this interface can have more methods than are specified here, but it must have at least these methods. This means that we can be confident that any database class we choose to use will have a connect() method for example.
The next part of the abstraction layer is a base class which all individual, database-specific classes will extend. This allows certain properties to be specified which are common to all databases without having to specify them within each individual class.
1
2
3
4
5
6
7
8
9
10
| class DBBase {
public $last_sql;
public $encryption = "md5";
protected $host = "localhost";
protected $port = 80;
protected $user = "root";
protected $pass = "password";
protected $dbname = "mydb";
protected $link;
} |
This class has no methods at all, but just a set of properties. Note that instead of using private to declare the non-public properties, we need to use protected. The difference between private and protected is that private properties can only be accessed by the class to which they belong, whereas protected properties can be accessed from the class to which they belong, and any children of that class (but nowhere else).
The actual properties themselves are common properties which will be used no matter which database class is chosen. $last_sql for example will be used to hold the last executed query, regardless of whether that query performed on a MySQL database, SQL Server database, PostGreSQL database or anything else.
The final part of the puzzle is the child class that implements the DB interface and extends the DBBase class. This is where you would write the database-specific code. In this example I will show you a MySQL implementation.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
| class MySqlDB extends DBBase implements DB {
private $new_link = true;
private $client_flags = 0;
public function __construct() {
$this->connect();
$this->select_db($this->dbname);
}
public function __destruct() {
$this->close();
}
public function connect() {
$this->link = mysql_connect($this->host, $this->user, $this->pass, $this->new_link, $this->client_flags);
}
public function errno() {
return mysql_errno($this->link);
}
public function error() {
return mysql_error($this->link);
}
public static function escape_string($string) {
return mysql_real_escape_string($string);
}
public function query($query) {
$this->last_sql = $query;
return mysql_query($query, $this->link);
}
public function fetch_array($result, $array_type = MYSQL_BOTH) {
return mysql_fetch_array($result, $array_type);
}
public function fetch_row($result) {
return mysql_fetch_row($result);
}
public function fetch_assoc($result) {
return mysql_fetch_assoc($result);
}
public function fetch_object($result) {
return mysql_fetch_object($result);
}
public function num_rows($result) {
return mysql_num_rows($result);
}
public function close() {
return mysql_close($this->link);
}
public function select_db($db) {
return mysql_select_db($db, $this->link);
}
} |
The first important thing to note here is how the class is defined:
1
| class MySqlDB extends DBBase implements DB |
This defines the name of the class (MySQLDB), makes it a child of DBBase (meaning it inherits all of the properties defined in DBBase) and implements the DB interface – and all in one line!
Next some properties are defined which are used just for MySQL (if they were to be used by all database classes they would be defined in DBBase). If some properties are required that only MySQL cares about, there is no sense in making them available to other databases.
After the properties, the methods which are required by the DB interface are defined. I wont go into each one, as you can see they are mostly just wrappers for PHP’s MySQL functions. However there are a couple of points to note.
1
2
3
4
| public function __construct() {
$this->connect();
$this->select_db($this->dbname);
} |
The method named __construct() is a special method name used by PHP called a constructor. When an object is created, any code in the __construct() method will be automatically run. In this case when a database object is created it will automatically connect and select the correct database.
Notice that the constuctor calls the connect() method which uses class properties such as host, user, pass etc to actually connect to the database. Remember these properties are defined in the class’ parent (DBBase) so they are available here. This means if that if the password to the database is changed for example, it only has to be changed in one place, and the code will automatically work with whichever database class is currently in use.
1
2
3
| public function __destruct() {
$this->close();
} |
Also worth mentioning is __destruct(). Again this is a build in PHP method name which is run when an object is destroyed (either explicitly or at the end of script execution). This is an ideal oppertunity to close the database connection.
1
2
3
4
| public function query($query) {
$this->last_sql = $query;
return mysql_query($query, $this->link);
} |
Also note that the query() method sets the last_sql property. Again this property is available because it is a property of the parent class.
Finally note that the interface does not specify a select_db() method. Remember that it is permissable to have more methods in the class than are specified in the interface, but not less. If it is likely that there might be a need to use select_db in the main code somewhere, it would be wise to include it in the interface so that the code does not break if select_db is called on an object which does not have that method defined.
Useage
First an instance of the class is created in the usual way:
Despite the fact that interface implentation and class extension needs to take place, nothing special has to be done to create an object – the parent class and the interface are handled ‘behind the scenes’. We just tell PHP that we want a MySQL database object and it takes care of the rest.
If there was a need to change to a different database provider, this is the only line of code which would have to be changed. For example, it might be changed to:
This is all that is needed in order to run an SQL server back end (although a corresponding SQL server class is required of course).
Now everywhere that database functionality is required, the generic methods such as connect(), fetch_assoc() etc instead of database-specific alternatives such as mysql_connect() and mysql_fetch_assoc() will always work, and will never need to be updated or replaced in the future.
1
2
3
4
5
| $result = $db->query("SELECT * FROM users WHERE userid = 1 LIMIT 1");
while ($row = $db->fetch_assoc($result)) {
extract($row);
// etc
} |