My Way of Doing Database Work

Right now, I use MySQL. It's what 95% of my clients are running. Now, don't get me wrong -- I love PostgreSQL and SQLite as well, but MySQL is where the cash is and where almost 100% of all the PHP sitescripts out there are based upon. If I needed a site that really had to scale for several hundred thousand users, then, hands-down, it would be PostgreSQL. And if I had a site where I invented something cute and a quick and dirty database file would do, then SQLite might be a consideration for me, or if I need an embeddable solution, then SQLite would do as well.

Now, in PHP5, you have Pear::DB, Zend_DB, PDO, mysqli, and mysql API in interacting with that MySQL database. However, all speed tests show that mysql API is the fastest except when doing prepared SQL statements. For prepared SQL statements, mysqli API is the fastest. So, since I don't use mysqli-type prepared statements, I use the straight mysql API.

Wrapping that API is very important to me. It lets me swap databases or control a problem from a central place. I stick it in a $db class and I have methods like Run, RunFast, GetRows, GetRowFromRows, GetNextRow, GetRowCount, GetColCount, Translate (builds a simple SELECT statement on the fly), and so on.

After working on enough projects, no one likes find a project where the SQL is sprayed all over the place. So, you'll find my SQL in a single class file with public variables storing parameterized SQL statements where ^ is the symbol I use to indicate where a value would go. I call it like so:

require_once('DB.php'); //this already instantiates the $db object for me in the class file
$sSQL = $settings->SQL_GET_USERS_BY_LASTNAME;
$db->Pack($sSQL, 'Smith');
$rsRows = $db->GetRows($sSQL);
while ($db->GetNextRow($rwRow, $rsRows)) {
   echo $rwRow['email'] . "<BR>\n";
}
$db->Release($rwRow);
$db->Release($rsRows);

And in my $settings class, you'll see something like:

$this->SQL_GET_USERS_BY_LASTNAME = "
SELECT
   *
FROM
   users
WHERE
   disabled = 0
   AND lastname = '^'
ORDER BY
   firstname;
";

See how much cleaner it is to use this sort of technique?

[EDIT: See my post on July 9, 2008 where this is superceded.]