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:
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:
class  myUDBI
{
	…

	function addDatabase($type, $name, $database, $username, $password, $portnum, $ipaddress)
	{
		switch($type)
		{
			case "ORACLE":
				$tns = "(DESCRIPTION = (ADDRESS_LIST = (ADDRESS = 
						(PROTOCOL = TCP)(HOST = ".$ipaddress.")
						(PORT = ".$portnum.")))(CONNECT_DATA =
						(SERVICE_NAME = ".$database." )))";
	
				$res = ocilogon($username,$password,$tns);
					
				if( $res !== false )
				{
					$this->dbConnection[$name] = array('RES'=>$res,'TYPE'=>$type);
			
					return true;
				}
		
				$this->dbErrorBuffer[count($this->dbErrorBuffer)] = oci_error();
			
				break;
			case "MSSQL":
						
				$res = odbc_connect($database,$username,$password);
						
				if( $res !== false )
				{
					$this->dbConnection[$name] = array('RES'=>$res,'TYPE'=>$type);
				
					return true;
				}
						
				$this->dbErrorBuffer[count($this->dbErrorBuffer)] = odbc_errormsg();
						
				break;
			case "MYSQL":
						
				$res = mysqli_connect($ipaddress,$username,$password,$database,$port);
						
				if( $res !== false )
				{
					$this->dbConnection[$name] = array('RES'=>$res,'TYPE'=>$type);
							
					return true;
				}
						
				$this->dbErrorBuffer[count($this->dbErrorBuffer)] = mysqli_connect_error();
					
				break;
		}
			
		return false;
	}
}
Terminating All Database Connections
When the class instance is closed or destroyed, the function, closeDatabase, is called. This function is designed to iterate through all of the database connections listed in dbConnection and properly terminate each session. Alternatively, this is a public function and can be used with an optional parameter, the database unique identifier. This will terminate the specified instance and remove it from dbConnection.
class  myUDBI
{
	…

	public function closeDatabase($name = null)
	{
		$status = true;
		$keys = array_keys($this->dbConnection);
		
		for( $i = 0; $i < count($keys); $i++ )
		{
			if( $name == null || array_key_exists($name,$this->dbConnection) )
			{
				switch( $this->dbConnection[$keys[$i]]['TYPE'] )
				{
					case "ORACLE":
						
						if( oci_close($this->dbConnection[$keys[$i]]['RES']) )
						{
							unset($this->dbConnection[$keys[$i]]);
						}
						else
						{
							$this->dbErrorBuffer[count($this->dbErrorBuffer)] = 
								oci_error($this->dbConnection[$keys[$i]]['RES']);
								
							$status = false;
						}
						
						break;
					case "MSSQL":
						
						odbc_close($this->dbConnection[$keys[$i]]['RES']);
						
						$this->dbErrorBuffer[count($this->dbErrorBuffer)] = 
							odbc_errormsg($this->dbErrorBuffer[$keys[$i]]['RES']);
								
						unset($this->dbConnection[$keys[$i]]);
						
						break;
					case "MYSQL":
						
						if( !mysqli_close($this->dbConnection[$keys[$i]]['RES']) )
						{
							$this->dbErrorBuffer[count($this->dbErrorBuffer)] = 
							mysqli_error($this->dbErrorBuffer[$keys[$i]]['RES']);

							$status = false;
						}
						else
						{
							unset($this->dbConnection[$keys[$i]]);
						}
						
						break;
				}
			}
		}
		
		return $status;
	}
}



Error Handling
As a rule of thumb when we develop this class, each function should return false if the function did not complete successfully. In the case that an error has occurred, each function should write the corresponding error message to our class variable, dbErrorBuffer. In addition, we shall include two functions to view the dbErrorBuffer error messages. First, we will create a function called viewAllErrors, which will return the dbErrorBuffer array. Secondly, we will create another function that returns only the last error message encountered, aptly named viewLastError.
class  myUDBI
{
	…

	public function viewAllErrors()
	{
		return $this->dbErrorBuffer;
	}
	
	public function viewLastError()
	{
		return $this->dbErrorBuffer[count($this->dbErrorBuffer)];
	}
}


Querying and Returning Results
Naturally, we will want to develop a function to query the database and return results, or just update existing data. In our class, there are several factors to consider. First, we must remember that we have created a replication flag called dbReplicate. This class variable determines whether or not all databases in the dbConnection array will be replicated. In other words, if you send an INSERT, UPDATE, CREATE, or DROP query, does it affect a single database or all of the databases. If you decide that you want to replicate databases, you must also decide which database you will SELECT from. This will become your master database. The sendQuery function will apply this logic. In our example, if dbReplicate is set to true, then the optional argument, name, must be assigned to the master database. Otherwise, sendQuery will commit your query but only a boolean result will be returned.
class  myUDBI
{
	…

	public function sendQuery($query, $name = null)
	{
		if( $name == null || $this->dbReplicate )
		{
			$keys = array_keys($this->dbConnection);
			
			for( $i = 0; $i < count($keys); $i++ )
			{
				if( $name == $keys[$i] )
				{
					$t = $this->runQuery($query,$keys[$i]);
					
					if( $t === false )
					{
						return false;
					}
					else
					{
						$retData = $t;
					}
				}
				else
				{
					if( $this->runQuery($query,$keys[$i]) === false )return false;
				}
			}
			
			return ( $this->dbReplicate && $retData !== true )? $retData:true;
		}
		else
		{
			return $this->runQuery($query,$name);
		}
	}
}

As you may have noticed in the above example, sendQuery does not perform the actual query to the database, but only processes the request. Instead, sendQuery calls another class function named, runQuery. This function performs the actual query to each database connection. The function will process the results from your query, returning false on error or the result set in an array.
class  myUDBI
{
	…

	private function runQuery($query, $name)
	{
		switch( $this->dbConnection[$name]['TYPE'] )
		{
			case "ORACLE":
				
				$res = oci_parse($this->dbConnection[$name]['RES'],
					$this->mutateQuery($query,$this->dbConnection[$name]['TYPE']));
					
				if( $res !== false && oci_execute($res) )
				{
					$data = array();
							
					while( $data[count($data)] = oci_fetch_array($res) );
							
					return ( count($data) == 0 )? true:$data;
				}
				else
				{
					$this->dbErrorBuffer[count($this->dbErrorBuffer)] = 
						oci_error($this->dbConnection[$name]['RES']);
								
					return false;
				}
						
				break;
			case "MSSQL":
						
				$res = odbc_prepare($this->dbConnection[$name]['RES'],
					$this->mutateQuery($query,$this->dbConnection[$name]['TYPE']));
						
				if( $res !== false && odbc_execute($res) )
				{
					$data = array();
							
					while( $data[count($data)] = odbc_fetch_array($res) );
							
					return ( count($data) == 0 )? true:$data;
				}
				else
				{
					$this->dbErrorBuffer[count($this->dbErrorBuffer)] = 
						odbc_errormsg($this->dbErrorBuffer[$name]['RES']);
								
					return false;
				}
						
				break;
			case "MYSQL":
				
				$res = mysqli_prepare($this->dbConnection[$name]['RES'],
					$this->mutateQuery($query,$this->dbConnection[$name]['TYPE']));
				
				if( $res !== false && mysqli_execute($res) )
				{
					$data = array();
							
					while( $data[count($data)] = mysqli_fetch_array($res) );
							
					return ( count($data) == 0 )? true:$data;
				}
				else
				{
					$this->dbErrorBuffer[count($this->dbErrorBuffer)] = 
						mysqli_error($this->dbErrorBuffer[$name]['RES']);
					
					return false;
				}

				break;
			default:
				
				$this->dbErrorBuffer[count($this->dbErrorBuffer)] = 
					"Unrecognized database type - ".$this->dbConnection[$name]['TYPE'];

				return false;
		}
	}
}

There are several points to address about this function before proceeding. First, you may notice that an argument can be passed to a rule, using %s. This will allow us to pass a string within the brackets. Also, because the ‘%’ symbol is used to process arguments, we must allow the symbol to be escaped. In this case, we use the text, ‘[p]’, to escape the ‘%’ symbol.
Final Considerations
Because requirements tend to evolve, so should your class. In some instances you may need a function to test whether a value exists in a table rather than returning any values (see example below). In other cases, you may want to upload a file within the database. In addition, you may want to execute database procedures. Whatever the case, your new class can evolve to your programming needs and yet remain universal, cross-platform independent.
class  myUDBI
{
	…

	public function testValue($query, $name)
	{
		switch( $this->dbConnection[$name]['TYPE'] )
		{
			case "ORACLE":
				
				$res = oci_parse($this->dbConnection[$name]['RES'],
					$this->mutateQuery($query,$this->dbConnection[$name]['TYPE']));
					
				if( $res !== false && oci_execute($res) )
				{
					return oci_fetch($res);
				}
				else
				{
					$this->dbErrorBuffer[count($this->dbErrorBuffer)] = 
						oci_error($this->dbConnection[$name]['RES']);
								
					return false;
				}
						
				break;
			case "MSSQL":
						
				$res = odbc_prepare($this->dbConnection[$name]['RES'],
					$this->mutateQuery($query,$this->dbConnection[$name]['TYPE']));
						
				if( $res !== false && odbc_execute($res) )
				{
					return ( odbc_num_rows($res) > 0 )? true:false;
				}
				else
				{
					$this->dbErrorBuffer[count($this->dbErrorBuffer)] = 
						odbc_errormsg($this->dbErrorBuffer[$name]['RES']);
								
					return false;
				}
						
				break;
			case "MYSQL":
				
				$res = mysqli_prepare($this->dbConnection[$name]['RES'],
					$this->mutateQuery($query,$this->dbConnection[$name]['TYPE']));
				
				if( $res !== false && mysqli_execute($res) )
				{
					return mysqli_stmt_fetch($res);
				}
				else
				{
					$this->dbErrorBuffer[count($this->dbErrorBuffer)] = 
						mysqli_error($this->dbErrorBuffer[$name]['RES']);
					
					return false;
				}

				break;
			default:
				
				$this->dbErrorBuffer[count($this->dbErrorBuffer)] = 
					"Unrecognized database type - ".$this->dbConnection[$name]['TYPE'];

				return false;
		}
	}
}


Final Considerations
Because requirements tend to evolve, so should your class. In some instances you may need a function to test whether a value exists in a table rather than returning any values (see example below). In other cases, you may want to upload a file within the database. In addition, you may want to execute database procedures. Whatever the case, your new class can evolve to your programming needs and yet remain universal, cross-platform independent.
class  myUDBI
{
	…

	public function testValue($query, $name)
	{
		switch( $this->dbConnection[$name]['TYPE'] )
		{
			case "ORACLE":
				
				$res = oci_parse($this->dbConnection[$name]['RES'],
					$this->mutateQuery($query,$this->dbConnection[$name]['TYPE']));
					
				if( $res !== false && oci_execute($res) )
				{
					return oci_fetch($res);
				}
				else
				{
					$this->dbErrorBuffer[count($this->dbErrorBuffer)] = 
						oci_error($this->dbConnection[$name]['RES']);
								
					return false;
				}
						
				break;
			case "MSSQL":
						
				$res = odbc_prepare($this->dbConnection[$name]['RES'],
					$this->mutateQuery($query,$this->dbConnection[$name]['TYPE']));
						
				if( $res !== false && odbc_execute($res) )
				{
					return ( odbc_num_rows($res) > 0 )? true:false;
				}
				else
				{
					$this->dbErrorBuffer[count($this->dbErrorBuffer)] = 
						odbc_errormsg($this->dbErrorBuffer[$name]['RES']);
								
					return false;
				}
						
				break;
			case "MYSQL":
				
				$res = mysqli_prepare($this->dbConnection[$name]['RES'],
					$this->mutateQuery($query,$this->dbConnection[$name]['TYPE']));
				
				if( $res !== false && mysqli_execute($res) )
				{
					return mysqli_stmt_fetch($res);
				}
				else
				{
					$this->dbErrorBuffer[count($this->dbErrorBuffer)] = 
						mysqli_error($this->dbErrorBuffer[$name]['RES']);
					
					return false;
				}

				break;
			default:
				
				$this->dbErrorBuffer[count($this->dbErrorBuffer)] = 
					"Unrecognized database type - ".$this->dbConnection[$name]['TYPE'];

				return false;
		}
	}
}


Using the Class


Now that we have completed our class, we can now use it in other scripts. In the example below, I will create three database connections, set the replication flag to true, and process incoming data. The data will be saved in three different databases for various uses.
<?php


// Database connections
$db = new myUDBI(true);

// Master database
$db->addDatabase("ORACLE",
		"ORA",
		"master.foo.com",
		"UserA",
		"abc123",
		1521,
		"192.168.0.10");

// Marketing database
$db->addDatabase("MYSQL",
		"MRKTG",
		"marketing ",
		"UserB",
		"abc456",
		3306,
		"192.168.0.20");

// Assembly database
$db->addDatabase("MSSQL",
		"ASSEM",
		"assembly ",
		"UserC",
		"abc789",
		null,
		"192.168.0.30");

// incoming data variables
$client = “Company A’;
$expense = 1299.99;
$dateProcess = “20071201’;

// update databases

$qry = “INSERT INTO TBL_CUST
         (CUSTOMER,EXPNSE,ACTIONDATE)
         VALUES
         (‘’.$client.’´,’.$expense.’,DATE[“.$dateProcess.’])’;

If( $db->sendQuery($qry) !== false )
{
         echo “Data Updated!’;
}
else
{
         echo “Error Encountered: “.$db->viewLastError();
}

?>