MySQL or SQL Server

Question

I'm creating an application that I want to run on either MySQL or SQL Server (not both at the same time) I've created two PHP classes DatabaseMySQL and DatabaseSQLSVR and I'd like my application to know which database class to use based on a constant set up at install.

define(DB_TYPE, "mysql"); // or "sqlsrv"

I'm trying to think of the best way to handle this. My thought is to do an "if else" wherever I instantiate the database:

$db = (DB_TYPE == "mysql") ? new DatabaseMySQL : new DatabaseSQLSVR;

I know there has to be a better way of doing this though. Suppose I want to add a third database type later; I'll have to go and redo all my code. Yuk!! Any help would be much appreciated.

Thank

Answer 1

The usual way is to have them both called the same (say, Database) and each placed in a separate file. Then you would need:

define(DB_TYPE, "mysql"); // or "sqlsrv"
require('db/' . DB_TYPE . '.php');

and you could just $db = new Database();

EDIT: Note that it is essential for your system to work properly to have an interface that both classes implement accordingly.

Answer 2

You should look into to using a technology such as PEAR.

Here is a good article on PEAR. http://www.evolt.org/node/21927

Answer 3

I suggest you create two data access objects that performs the various DB actions your application requires. Use the Factory Pattern to return either the MySQL or MS SQL implementation, and do all data access in your PHP pages using that data access object.

That way, you can tune behavior (as OMG Ponies suggests) as needed for each target database. If you use a common base class, you can inherit the same implementation for both databases and only provide a specific implementation where warranted (though I have not done much OO PHP... not sure if inheritance is supported?).

Answer 4

In the simplest possible terms:

Use define to define 'DB_TYPE' as YourFullyQualifiedClassName, then...

define('DB_TYPE', 'DatabaseMySQL') // or DatabaseSQLSVR or ...
$myDBType = DB_TYPE;
$db = new $myDBType();

Answer 5

The problem is that there's some odd bit of syntax which you never planned for which is handled in a radically different way by different drivers - e.g. explain plans, transactions...

A good starting point would be to use a common abstraction toolkit such as dbx, adodb, or PDO.

In each case, the DBMS driver can be referenced as a data item in the same way as the username or password - no conditional statements.

C.