MoreWhite

SDBA is a simple database abstraction layer for PHP. You’ve probably heard of ADOdb, PEAR DB abstraction layer, etc, and already concluded that they are not worth the time (to learn) or performance issues that come with them.

Simple Database Abstraction is different:

1. an abstract interface to a single DB
2. a single PHP file less than 150 lines
3. may be used in conjunction with DB-specific functions at any time
4. is far easier and more intuitive than using mysql_* functions
5. reduces PHP code by up to 50%
6. is optimized for speed

The result is virtually no performance or memory overhead to using DB-specific functions.

Simply download the version for your database, and modify it when your database engine changes.

Learn SDBA in 5 mins

Begin with example:

$sql = db_prepare('SELECT id, tag FROM Tags WHERE tag = @tag', $_REQUEST);
$num_rows = db_query($sql) or db_error();

for ($i = 0; $i < $num_rows; $i++) {
     list($id, $tag) = db_read();
     $valid = array('foxtons','hamptons','none');
     $price = db_scalar(db_prepare('SELECT price FROM Properties WHERE agent IN (@valid)'));
     print "$id: $tag ($price)";
}

Connecting to the database explicitly is unnecessary. Connection is opened automatically when needed and closed when the script terminates.

db_prepare(statement,hash): Convienient way to insert parameters into an SQL statement. Strings are automatically quoted and escaped, NULL values are handled as necessary, arrays are comma-seperated, and the result is the full SQL statement. Parameters are looked up in the hash provided by name, so request variables do not have to be made global. However if no hash is provided, the global variables are used as parameters.

db_query(sql): Nice little function that executes an SQL query. Here’s the catch: if its a SELECT it returns the number of rows returned; if its an UPDATE the number of affected rows and if its an INSERT the last automatically generated ID. On error, false is returned. If you need to access the resultset handle directly, it’s stored in the $db_r global variable.

db_error(): Prints out error information from the database and dies.

db_read(optional handle): Reads a row from the database as an array (not a hash). Therefore, there is only one way to get a row using SDBA - using the list construct. This is the fastest approach and keeps code clean and readable, avoiding bugs. This philosophy is best understood when you specify only the fields you need in a SELECT statement - something that should always be done for performance reasons. Note that you can also pass db_read the resultset handle if you are dealing with multiple resultsets in a nested loop, otherwise the last handle is used.

db_scalar(sql): Like db_query but returns the single value (first row, first column) returned by the query.

db_query(sql,page,size): Paging has never been easier. Simply specify the page number to get (starts at 1) and size (records per page) and you are set. The SQL is adjusted accordingly. The function returns an array where the first element is the total number of rows the query could have returned (without page limit) and the second - the total number of pages the query can return. Get them both with: list($rows,$pages) = db_query($sql, 1);

Download

Currenly, only a MySQL version is available: 0.9 [ download ]

If you decide to develop a PostgreSQL version, please contact me so I can add it here.

Installation

1. Rename sdba.txt to sdba.php
2. Open it in your favorite editor and fill in your database configuration in $db_config
3. At the top of your PHP files add require_once ’sdba.php’