Database abstraction in PHP with MDB2
Coming from a Windows environment, the first thing I noticed about PHP was the lack of a standardized way to access databases across vendors. That is, you want to connect to a MySQL server, you use the command mysql_connect(); , for PostgreSQL it is pg_connect(); , for Microsoft SQL server is mssql_connect(); .
On Windows, instead, there is a layer called Open Database Connectivity (ODBC) that translates the calls from the software to the database, providing a common interface and emulating missing features. Each vendor provides a specific driver, that plugs into the architecture. Via ODBC even a text file or an Excel spreadsheet can be connected and queried like it was a database.
It has been natural to me to start searching the net for a PHP equivalent of that layer. Shortly after starting I found the de facto standard for database abstraction in PHP, called MDB2. Even if it has not the flexibility of a real ODBC layer, it's lack of complexity made it very popular among fellow web programmers at first, just to find out it's ability to grow via extensions and performing in a relatively easy way a set of very powerful and demanding operations.
MDB2 is a Pear library, so you need to follow a procedure to install it. Refer to the Pear website for details.
Just after including the MDB2 scripts into your PHP file
<?
require_once ("MDB2.php");
?>
you need to create an instance of the database class, like this
<?
$dsn = "your database type://your user name:your password@your server/your database name";
$db = MDB2::factory($dsn);
?>
so you have a connection string that explains how to connect to the database, and an object with multiple methods to perform database operations. You don't need to know other details about the database apart from the ones you put in the Data Source Name (DSN), everything else is handled by the MDB2 object.
After that, you perform vendor agnostic queries like this
$result = $db->queryAll ($sql);
But, as said, the real power behind MDB2 is it's extensions system, for example
$db->loadModule('Extended');
$tablename = "customers";
$data = array("name"=>"Bob","surname"=>"Michael");
$result= $db->extended->autoExecute($tablename,$data, MDB2_AUTOQUERY_INSERT);
I've effectively inserted a new customer into my table without writing a single word of SQL, and without worrying of the actual table structure.
The same applies to interrogation queries, like this
$tablename = "customers";
$where = "`NAME` = 'BOB' AND `SURNAME` = 'MICHAEL'";
$result= $db->extended->autoExecute($tablename,null,
MDB2_AUTOQUERY_SELECT,
$where,null,true,true);
the only SQL here is the where clause, everything else is managed by the library.
You can even store an XML backup of your entire database with a few commands
$db_schema_options = array(
'use_transactions' => true,
'log_line_break' => '<br>',
'idxname_format' => '%s',
'debug' => true,
'quote_identifier' => true,
'force_defaults' => true,
'portability' => true
);$schema = MDB2_Schema::factory($dsn, $db_schema_options);
$definition = $schema->getDefinitionFromDatabase();
$schema_dump = $schema->dumpDatabase($definition, array(
'output_mode' => 'file',
'output' => $dumpfile
), MDB2_SCHEMA_DUMP_ALL);
where $dumpfile is the file name you want your backup to go to.
The notion of schemas enables your application to seamless update the database across updates. The MDB2 can made automatic modifications of your table definitions to match the ones into the schema.
The library enables to use complex database functions, like transactions, to make your web applications secure and overcome stability problems when delicate procedures are performed, like the cart checkout on a e-commerce website, a money transfer, or any other situation where a power outage or a software interruption can lead to an partial update. Transactions group the operations so that any error will lead to a rollback, preserving the state your data was prior to updating.
If later in the process you want to change your database, for example from MySQL to Microsoft SQL server? Simply change your DSN from mysql to mssql and you are ready!
Did you like this article? Subscribe to my transactional feeds and leave a comment!