PHP developers often encounter instances when their PHP scripts must adhere to a variety of different platforms. Often this includes database interactivity. While many PHP developers write code to interact with MySQL, larger enterprises do not deploy MySQL. To accommodate a larger base of users, a developer might want to adopt the practice of cross-platform development.
There are several options available to interact with multiple database engines with PHP, such as Pear DB and MDB2. However, there may instances where you will be required to develop your own custom database interface that connects to many different database engines using a single unified syntax. This article will address the development of a class that will do exactly that. In addition, we will include the ability to replicate data among several databases in real time.
This article assumes that you have working knowledge of PHP. We will not address the installation of a specific database module into PHP. Please refer to relevant documentation to accomplish this. To demonstrate the concepts discussed in this article, we will develop a PHP class that connects to Oracle, Microsoft SQL Server, and MySQL.
Before diving into the code, let??s address the difference between our class versus Pear DB and middleware, such as ODBC. Pear DB and its successor, Pear MDB2, are extremely useful and intelligent classes. They provide an intuitive interface to interact with databases, but only a single database at a time. Our class will be designed to be universal and platform-independent. The interface will enable a programmer to write her code once and deploy among many different database engines. As you will see, other technologies like Pear DB and ODBC will complement our class. For example, we will use ODBC in this article to connect to Microsoft SQL Server.
Constructing the Class
In the example below, we have created a class named, myUDBI. The class member variables include:
- dbConnection – This variable is a multi-dimensional array that consists of all database connections.
- dbReplicate ?? A boolean flag that instructs the class to replicate data among all connections.
- dbErrorBuffer ?? An array of error messages encounters during the instance.
- dbTransTable ?? A multi-dimensional array of translation rules to specific database engines.
class myUDBI
{
private $dbConnection = array(); // array of database connections'
private $dbReplicate; // replication flag
private $dbErrorBuffer = array(); // list of error messages
// translation table
private $dbTransTable = array("DATE[C]"=>array("ORACLE"=>"SYSDATE",
"MSSQL"=>"GETDATE()",
"MYSQL"=>"NOW()"),
"ID[%s]"=>array("ORACLE"=>"SELECT %s.CURRVAL FROM DUAL",
"MSSQL"=>"SELECT IDENT_CURRENT(%s)",
"MYSQL"=>"SELECT LAST_INSERT_ID()"),
"INDATE[%s]"=>array("ORACLE"=>"TO_DATE('%s','YYYYMMDD')",
"MSSQL"=>"'%s'",
"MYSQL"=>"'%s'"),
"OUTDATE[%s]"=>array("ORACLE"=>"TO_CHAR(%s,'DD MON YYYY')",
"MSSQL"=>"CONVERT(VARCHAR(11),%s,6)",
"MYSQL"=>"DATE_FORMAT(%s,[p]d [p]b [p]Y)"),
"NEWID[%s],"=>array("ORACLE"=>"%s.NEXTVAL,",
"MSSQL"=>"",
"MYSQL"=>"null,"));
function __construct($repActive)
{
$this->dbReplicate = $repActive;
}
function __destruct()
{
$this->closeDatabase();
}
}
The translation table, dbTransTable, is the most sensitive portion of the class. When constructing this array, you must understand how each database engine responds to various differences in syntax, such as date/time functions and unique identity constraints. It is this area of the code that will require careful thought how each database engine will respond to your specific requests.
In addition, we will include constructor and destructor functions. The constructor function takes one argument, repActive. This is what will set the dbReplicate variable value to, determining whether or not the class will replicate data among all database connections. The destructor function will ensure that all the database connections are closed when the instance of the class is destroyed.
Connecting to the Database Engines
Next, we will add a new class function to populate the dbConnection class variable, the list of all database connections. The class variable, dbConnection, is a multi-dimensional named array. Each connection includes two elements; the resource identifier, ??RES??, and the type of database engine, ??TYPE??. We will name this function, addDatabase. The function arguments should include all of the necessary information to establish a connection with each database engine. In our example we will include:
