PHP and Working with Databases (for the Lazy Sod)

Database Abstraction and Atomic Operations and Classes Explored

By Justin Vincent

The Lazy Sod

Maybe it’s just me, but after building database driven websites in PHP for the past six years I am starting to get more than a little tired of repeating myself. What I mean to say is… how many times, on how many different projects, and with how many different databases do I have to write something along the lines of:

<?php

	mysql_connect("localhost", "mysql_user", "mysql_password") 
		or
	die("could not connect");

	mysql_select_db("mydb");
	
	$result = mysql_query("SELECT id, name FROM mytable");
	
	while ($row = mysql_fetch_array($result)) 
	{
		printf ("ID: %s  Name: %s", $row[0], $row["name"]);
	}

	mysql_free_result($result);

?>

Is there really any need to make my fingers type this kind of gunk so often? Surely there must be an easier way of working with databases than this? And if so, where do I find that kind of information?

You might say ‘abstraction’ and bring up names like PEAR and ADOdb, and you might be right (if the only thing important to you is being able to use the same code with different databases). The problem is you still have to type out lots of stuff whenever you want to deal with a database. For example, here is the required code using ADOdb:

<?

	include('adodb.inc.php');
	
	$conn = &ADONewConnection('access');
	
	$conn->PConnect('northwind');
	
	$recordSet = &$conn->Execute('select * from products');
	
	if (!$recordSet)
	{
		print $conn->ErrorMsg();
	}
	else
	{
		while (!$recordSet->EOF) 
		{
			print $recordSet->fields[0].' '.$recordSet->fields[1].'';
			$recordSet->MoveNext();
		}
        }
	$recordSet->Close();
	$conn->Close();

?>

Nothing wrong with that, you say. I agree. You can use the same code on lots of different databases, you say. Fair enough, but I want to be even lazier than that. Ah, you say.

Atomic Operations

After extensively scouring the net for examples of how to be a really ‘lazy sod’ with databases and PHP, I found absolutely nothing that could help me. So like the old adage goes, “it’s hard work being lazy”, I set about putting some real mental effort into resolving this problem. What I realized was that I should first break down the problem into a list of atomic operations and requirements (for those not in the know, an atomic operation is an operation that does one thing only – and does it well).

After all, when you stand back and look at it, what does working with databases using PHP really mean? As far as I can see, the majority of the time you only need four or five atomic operations to do ‘most things’ that you need to do:

1) Perform a non-result query such as Insert/Update/Commit

2) Get a single variable from the database

3) Get a single row/column from the database

4) Get a list of results from the database

In fact, when I think about it, all the commercial and non-commercial PHP projects that I have ever worked on have never needed any other operation. I’m not even sure you can do any other operation with a database… Before you scream blue murder, I am not talking about SQL queries here, I am talking about the functions that wrap up the SQL queries. Because no matter how complex the SQL query you write, only one set of results will ever be returned — and as we’ll see, that’s a good thing.