I have a very old (3 years to be exact) code base written in PHP that I have wanted to revamp for a while. I had written a database layer for this system that wound up being quite cumbersome to use. Too many openDatabase, executeQuery, getRow, freeResults, closeDatabase calls to make it practical to do any maintenance on the site.

While browsing around last week I came across the ez_sql PHP Class written by Justin Vincent and decided to check it out as an alternative. Many times, just the acrobatics (all redundant work, mind you) involved in getting data from a database make you decide that the system is “good enough” given the work involved.

I installed ez_sql in under 5 minutes and did a quick prototype accessing my build database. Take a look at the difference:

Here is a function before using ez_sql:


function getNumberofActiveSilos()
{
$siloCount = 0;

$database = openDatabase();

if ($database != null)
{
$query = "select count(distinct SILO_ID) AS SILO_COUNT";
$query .= " from BUILD_INFO";

$resultSet = executeQuery($query);

if ($resultSet != null)
{
$row = getNextRow($resultSet);

if ($row != null)
$siloCount = $row["SILO_COUNT"];

freeResults($resultSet);
}

closeDatabase($database);
}

return($siloCount);
}


A little verbose isn’t it? Too much to do to get anything from the database. No wonder I haven’t looked at the code for three years. Here’s the same function after migrating the code to use ez_sql:


function getNumberofActiveSilos() {
global $db;

$query = "select count(distinct SILO_ID) from BUILD_INFO";
$var = $db->get_var($query);

return($var);
}


As you can see, the second function makes doing database work much more palatable, as it removes all of the muck out of the process and allows you to get to the point. There is no question what is going on now. For those times when you actually have to return a result set, ez_sql creates a class with each column as a data member for you to retrieve, making you work so much easier. Simply brilliant design all around.

If you are doing PHP programming with a database you definitely have to check out this library.