Database Access Abstraction using PHP

Please send any comments, suggestions, errata etc. about this page to michael@peceny.de, I would be glad to hear about them.

Motivation

When writing an application that makes use of a database (or other kind of data store), the functions used to access the database are usually specific to a certain type/brand (like MySQL, Postgres, Sybase / MS SQL Server, ODBC, ...). When those are directly used by the application, the whole application will be database-dependant: it will be able to interface with only a specific type of database.

APPLICATION --db-dependant--> DATABSE

However, due to the possible need to port the application to use another database, or maybe even allow for configuring the application as to which database to use, it can be desireable to write database-independant code. This can be achieved by introducing a layer of abstraction between the application and the database.

APPLICATION --db-independant--> ABSTRACTION LAYER --db-dependant--> DATABSE

The abstraction layer holds wrappers around the actual database access code (which still is database-dependant, so there will have to be one wrapper for each database supported) that present a common interface to the application.

Now, when needing to port the application to use a different database, only a suitable wrapper has to be provided - the application itself does not have to be changed (more or less - the connection parameters for different databases often vary somewhat). Also, the application can load a different wrapper according to its configuration, thus giving the user a choice as to which database to use.

Simple wrappers

When taking a look at PHP's database access functions, they turn out to be basically more or less the same for different databases. So, a very simple way to go about introducing the database access layer would be to simply provide a common interface for the functions needed by the application.

Examples:*)

// mysql_simple_wrapper.inc
// Simple wrapper for MySQL
function database_connect( $db, $user, $password )
{
  return mysql_connect( $db, $user, $password );
}

function database_query( $query, $conn )
{
  return mysql_query( $query, $conn );
}

function database_fetch_array( $result )
{
  return mysql_fetch_array( $result );
}
// sybase_simple_wrapper.inc
// Simple wrapper for Sybase / MS SQL Server
function database_connect( $db, $user, $password )
{
  return sybase_connect( $db, $user, $password );
}

function database_query( $query, $conn )
{
  return sybase_query( $query, $conn );
}

function database_fetch_array( $result )
{
  return sybase_fetch_array( $result );
}

Usage:

Simply include the appropriate wrapper - include( "wrapper-name"); - and then use the database_xxx() functions.

However, these kind of wrappers are only useful as long as the various database-specific functions work the same way - if they do not have the same functionality things get complicated.

Example:

mysql_fetch_array() and sybase_fetch_array() work identically, while pg_fetch_array() (Postgres) is different since it does not automatically advance through the rows of the query result, but rather expects a row index for a parameter.

An example for more advanced wrappers

Given the limitations of above simple wrappers, a higher level of abstraction appears desireable. Thus, the question is - what is the essence of interaction between the application and the database? In most cases, this will be the application formulating a query string and expecting rows of data in return. Thus, the interface the wrappers provide to the application might look like this:

database_connect( $host, $port, $database, $user, $password )

Connects to the database. The connection parameters are still possibly database-dependant, so the application calling database_connect() might have to distinguish between databases used when connecting.

multi_row_query( $query )

Returns a two-dimensional array holding the query's result, with the first index being numeric (starting at 0) and indicating the result row and the second index being both associative as well as numeric and indicating the field (column).

Example: $result = multi_row_query( "SELECT name, birthday FROM users" );.
$result[2]["birthday"] now holds the data in the "birthday" column of the third row of the query result.

single_row_query( $query )

Specialized version of multi_row_query() for queries that are known to only return a single row: The array returned is only one-dimensional, with just an index (both numeric as well as associative) for the field (column).

single_row_query_vars( $query )

Specialized version of single_row_query() that does not return an array holding the query's result, but rather sets global variables with the column names to the value of the according field of the query result.

Example: single_row_query_vars( "SELECT email, last_login FROM users WHERE name='admin'" ) will set the global variables $email and $last_login to the values returned by the database.

Examples:*)

Usage:

Include the appropriate wrapper - include ( $database_used."_wrapper.inc" ); - and then use the functions it provides to interact with the database.

All use of database specific functions as well as different handling (e.g. Postgres pg_fetch_array() vs. MySQL mysql_fetch_array(), see above) is now located in the wrappers.

*) Note: All code here is provided "as is", with no guarantee that it is usable for any purpose. Also, I will hold no liability for any kind of data loss, damage etc. resulting from its use in any way. Use at your own risk.

Michael Peceny
Jul 15, 2001