One of the most important (and least loved) activities in a programmer’s life is debugging code. When debugging PHP, there are several strategies, ranging from strategic use of print_r to elaborate systems that send debugging information to specific debug tables in a database.

In this article, we look at a simple tip for finding errors in SQL code when using PEAR::DB.

Debugging PEAR::DB

The PEAR modules provide a great deal of power and convenience for PHP programmers. One of the most widely used modules is DB, with its support for a ridiculous number of databases. PEAR::DB also includes some extremely helpful methods like autoPrepare() and autoExecute(). The downside to these methods is that they are (in many respects) a “black box” approach. You provide a key-value (associated) array along with the table name, insert or update constant, and predicate. What you hope to get is a new or updated record in your database.

When it doesn’t happen, there isn’t a whole lot of information (at first glance). Since the autoExecute() method constructs the SQL statement for you, you cannot simply use “echo $sql” as you might if you were constructing the SQL statements yourself.

However, the DB module also has a very simple mechanism for showing you what is happening behind the scenes.

Suppose you are updating a table in your database and the update doesn’t seem to be doing anything. Your code might look something like this:

$db->autoExecute($table, $_POST, DB_AUTOQUERY_UPDATE, "row_id=$id");

Header("Location: my_edit_page.php");

To find out why the update is not occurring, you could modify that same code to look like this:

$db->setOption('debug', true);

$my_query = $db->autoExecute($table, $_POST, DB_AUTOQUERY_UPDATE, "row_id=$id");

if (DB::isError($my_query)) {
	print $my_query->getDebugInfo();
}

// Header("Location: my_edit_page.php");

Now, when you attempt to save, the redirect will not happen and you will see both the SQL statement that PEAR::DB attempted to use as well as a message describing why it did not work.

Once you know where to look, debugging PHP is quite simple!