Portable database calls in PHP

It's been a while since I've posted real code, so I thought I'd share a little snippet from a PHP database class that has been my bread and butter for a couple years now.

It started out simple enough, just a nice little wrapper for some of the more repetitious database calls.  It has been growing in recent months, however, as I found myself wishing for expanded functionality.

One of the most recent additions to that wishlist is that I want a class property set after every database query that holds either the number of rows returned or affected, depending on the type of query passed to the class function.  What complicates this is that I've been writing the class to handle multiple types of databases.  I didn't want to have a bunch of switch() statements to cover mysql_num_rows(), mssql_num_rows(), mysql_affected_rows(), and so forth.  So this is my answer.

First, a class property is set by the user defining the type of database transport: mssql, mysql, &c.  The format of this is important, in that it must match one of the database vendor groups that PHP recognizes.  Simple enough.  (This could be an enumerated list of constant values, I suppose, but I'll worry about that another day.)

Now here's where things get fun.  Inside our class query function, we do the following:

Make sure the property is lower case, and keep it local, just for cleaner-looking code.

$transport = strtolower($this->transport);

Also set our default row count to something that represents an error value (we'll set it to a real value once we complete a valid database call).

$this->rowcount = -1;

Now we define the function used for the query.  This will translate to mysql_query() or mssql_query() and so on, depending on the value of the $transport variable.

$query_string = $transport.'_query($this->query_string)';

Finally, we can make the query call and get the result set.  This is the real magic, and shortens a rather long list of case statements into one simple call.  Note that to get anything back from  eval(), we have to explicitly make it pass a return value.  That's why we're wrapping the call inside a return() statement.

$result = eval('return('.$query_string.');');

We follow the same sort of logic to build an associative array containing the entire result set.  This saves us the trouble of doing it in our application, keeping it all neatly contained in this function.

$row_fetch = $transport.'_fetch_assoc($result)';

while($row = eval('return('.$row_fetch.');')) {

    $recordset[] = $row;

}

To get the number of records either returned or affected, we need to determine whether this was a SELECT statement, or if it was an UPDATE, INSERT, DELETE, &c.  We're forced to do this because there are two different functions that handle this counting function, depending on the type of SQL transaction.  So, we'll just see if it's a SELECT statement, and if it isn't, assume that we need to make an x_affected_rows() call.  All we need is the first word, and hope that the user didn't do anything really silly with the query.

$query_type = explode(" ", strtoupper(trim($this->query_string)), 1);

switch($query_type[0]) {

    case 'SELECT':

        $count_string = $transport.'_num_rows($this->db_handle)';

        break;

    default:

        $count_string = $transport).'_affected_rows($this->db_handle)';

        break;

}

$this->rowcount = eval('return('.$count_string.');');

That's pretty much it.  Now we have the recordset if one was returned from the query, as well as the number of rows affected by the query.  We can pass the recordset back in a return() call, or return null if none exists, and the user can take action based on either the recordset or the record count.

Best of all, it's pretty portable.  PostgreSQL, MySQL, MSSQL, Sybase, and on down the line.  So long as the transport property is properly defined, we can use this just about anywhere.  In my experience, other database types are rare enough that I don't need to worry about them, so I'm not going to bother with any special conditions for them.

I am aware that eval() isn't the most popular way of handling things, and it does come with a performance cost and security risk, but in the applications I'm working with, neither are really an issue, as the performance requirements are rather low, and the userbase is limited in experience and located on an isolated intranet.  If you're going to use this code, I would strongly suggest that you take measures to ensure that any SQL query passed to the class function meets your security requirements.