PHP - several different databases.

Question

I spent the last few hours trying to find the answer to the "best", the most logical, etc. a way to write a php database class to connect to one db postgres and one mysql db at the same time. In addition, I would like to accept the design of Injection Dependency, but I am new to this concept.

So far I have come up with ...

class Database { public function PgSqlConnect() { /* Connect to database */ $host = 'localhost'; $dbname = '---'; $user = '---'; $pass = '---'; $timeout = 5; /* seconds */ try { $pgsql_dbh = new PDO("pgsql:host=$host; dbname=$dbname", $user, $pass); $pgsql_dbh->setAttribute( PDO::ATTR_TIMEOUT, $timeout ); $pgsql_dbh->setAttribute( PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION ); return $pgsql_dbh; } catch( PDOException $e ) { echo 'Unable to connect to database: ' . $e->getMessage(); } } public function MySqlConnect() { /* Connect to database */ $host = 'localhost'; $dbname = '---'; $user = '---'; $pass = '---'; $timeout = 5; /* seconds */ try { $mysql_dbh = new PDO("mysql:host=$host; dbname=$dbname", $user, $pass); $mysql_dbh->setAttribute( PDO::ATTR_TIMEOUT, $timeout ); $mysql_dbh->setAttribute( PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION ); return $mysql_dbh; } catch( PDOException $e ) { echo 'Unable to connect to database: ' . $e->getMessage(); } } } 

Obviously, duplicate code violates the DRY approach. I know and have seen many examples of multiple db connections, but most relate to the same driver and do not provide DI capabilities.

I should also add that I considered placing the connection details in the constructor of the database class, like ...

 $driver = 'mysql'; ... $mysqldb = new Database($driver,$un,$pw,...); $driver = 'pgsql'; ... $pgsqldb = new Database($driver,$un,$pw,...); 

but I don’t know if this is really a good idea and how well it will work with DI.

Many thanks!

Answer 1

First you must create an interface for all database operations.

 interface IDatabase { function connect(); function query(); ... } 

Then you have different driver classes that implement this interface.

 class MySQLDB implements IDatabase { } class PGSQLDB implements IDatabase { } 

This way you can easily use dependency injection.

 class Test { private $db; function __construct(IDatabase $db) { $this->db = $db; } } 

You can call it like:

 $mysqldb = new MySQLDB(); $test = new Test($mysqldb); or $pgsqldb = new PGSQLDB(); $test = new Test($pgsqldb); 

Answer 2

To avoid code duplication, you can extend the abstract class

 abstract class AbstractDb { public function connect() { // common code to avoid duplication echo 'connected!'; } abstract public function escapeField(); abstract public function escapeValue(); } class MySQL extends AbstractDb { public function escapeField() { // Db-specific method } public function escapeValue() { // Db-specific method } } $db = new MySQL; $db->connect(); 

Or use composition, and let the Db class use a different driver for db-specific methods

 interface IDriver { public function escapeField(); public function escapeValue(); } class MySQLDriver implements IDriver { public function escapeField() { // Db-specific method } public function escapeValue() { // Db-specific method } } class Db { public function __construct($driver) { $this->driver = $driver; } public function connect() { // common code here ? idk, it just an example echo 'connect!'; } // this method is db-specific, so we call the driver public function escapeField($field) { return $this->driver->escapeField($field); } public function escapeValue() { // same here } } $db = new Db(new MySQLDriver); $db->connect(); 

There will be traits in php 5.4, so there will be more approaches to avoid code duplication.