Last month we took a brief look at ADOdb, and saw how to perform simple SELECT, INSERT and UPDATE queries. If you're new to ADOdb, I suggest you read that article first. ADOdb has numerous features that are more advanced too, and we look at some of them this month.

The database is often the culprit for poorly performing applications. Not having to repeat database queries is one way of improving performance. This can be achieved by caching the entire page (there are many of these solutions out there, for example, PEAR->Cache), or if you still need to generate a dynamic page, but only want the query to be cached, you can cache the query results only, which ADOdb allows you to do easily. Before you look at caching to save your poorly performing application though, I suggest you first try and optimize the query. Some simple indexing can make all the difference - there are far too many 'professional' solutions out there that are poorly indexed. You can read more about that in this article. Now, let's look at how ADOdb allows you to cache database results. In this example, based on the queries we ran last time, ADOdb stores the cached results in a file stored in /var/tmp/adodb_cache for 10 minutes after the query is first performed.

include("$adodb_path/db_values.inc.php");
include("$adodb_path/adodb.inc.php");
$db = NewADOConnection("$database_type");
$db->Connect("$host", "$user", "$password", "employees");

$ADODB_CACHE_DIR = "/var/tmp/adodb_cache"; //Directory to store cached files

$sql = "SELECT surname, age FROM employees";
$rs = &$db->CacheExecute(600,$sql);  // Executes, and caches the results for 600 seconds
if (!$rs) {
  print $db->ErrorMsg(); // Displays the error message if no results could be returned
}
else {
  while (!$rs->EOF) {
    print $rs->fields[0].' '.$rs->fields[1].'<BR>'; 
     // fields[0] is surname, fields[1] is age
    $rs->MoveNext();  //  Moves to the next row
  }  // end while
} // end else

The CacheExecute() function takes two parameters: the first is the time for the cached file to remain in existence, in seconds, and the second is the SQL statement. The first parameter is optional (it probably would have made more sense for the developers of ADOdb to make it the second parameter), and if you do not specify a time, the default will be 3600 seconds, or 1 hour. The cached files are named adodb_*.cache, and you can safely delete them from the filesystem as well. You should schedule a regular Unix crontab or Windows task to clean up expired cache files. Note that the boolean PHP parameter magic_quotes_runtime must be off in order for caching to work. You can change this if necessary at runtime by adding: set_magic_quotes_runtime(0) to your code before calling the database classes. You can clear the cache at any time by calling the CacheFlush() function. ADOdb also recommends that the boolean PHP parameter register_globals be set to off, for security reasons (which it is by default in recent versions of PHP).

More information about your results

There are some common questions developers ask of their result sets, the most common being how many records. ADOdb easily answers this with the RecordCount() function. RowCount() is a synonym.

$sql = "SELECT surname, age FROM employees";
$rs = &$db->CacheExecute(600,$sql);  // Executes, and caches the results for 600 seconds
print $rs->RecordCount() . " rows returned]"; // Display number of rows returned

Probably second on the list of demands is the number of fields returned, and here ADOdb again provides a function - FieldCount().

$sql = "SELECT surname, age FROM employees";
$rs = &$db->CacheExecute(600,$sql);  // Executes, and caches the results for 600 seconds
print $rs->FieldCount() . " columns returned]"; // Display number of rows returned

Limiting Results

Last time we discussed how using a database library makes your application more portable. I experienced a particularly painful migration when moving from MySQL to Informix once, mainly due to the non-ANSII standard LIMIT clause (for example, MySQL allows a statement such as SELECT name FROM employee LIMIT 15), a very useful feature that is not implemented in the same way in Informix. (It would be written, in an equally non-standard manner, as SELECT FIRST 15 name FROM employee in Informix). It makes little sense to use a database library if you're still going to hardcode non-standard SQL into your queries. Fortunately, ADOdb has a way to handle limits - the SelectLimit() function.

$sql = "SELECT surname, age FROM employees";
$rs = &$db->SelectLimit($sql, 10, 100); // Select 10 rows, starting at row 100
if (!$rs) {
  print $db->ErrorMsg(); // Displays the error message if no results could be returned
}
else {
  while (!$rs->EOF) {
    print $rs->fields[0].' '.$rs->fields[1].'<BR>'; 
     // fields[0] is surname, fields[1] is age
    $rs->MoveNext();  //  Moves to the next row
  }  // end while
} // end else

The SelectLimit()function takes the SQL statement as the first parameter, followed by the number of rows returned, and finally the offset (the first row to return). Note that this is the opposite order of parameters to MySQL's LIMIT clause. SelectLimit() is useful for displaying a portion of search results on a web page, with Previous and Next buttons to navigate through the results. All too often I've seen code that returns all the results from the database, using PHP to do the filtering - an almighty waste of effort! You can also use the CacheSelectLimit() function to cache these type of results.

Transactions

Transactions are an important feature of many applications. (For the beginner, transactions are where you want to have a number of queries that either fail or succeed together. The classic example is a bank transaction. Money comes off your account into someone else's, and if either transaction fails, they must both fail.)

Transactions can be tricky to manage on a code level. Constant error checking is required to determine whether to COMMIT (the transactions have all succeeded, and are finalized), or ROLLBACK (one or more transactions have failed, and the entire lot must be reversed). ADOdb has some useful functions that make transaction handling painless. This example adds 10 to one employee's balance, and subtracts it from another, handling the two queries as one transaction.

$sql1 = "UPDATE employees SET balance=balance-10 WHERE id=15";
$sql2 = "UPDATE employees SET balance=balance+10 WHERE id=22";
$db->StartTrans();
$db->Execute($sql);
$db->Execute($sql2);
$db->CompleteTrans();

There is an older way for ADOdb to handle transactions you may come across when dealing with old code, using the BeginTrans(), CommitTrans() and RollbackTrans functions, but these required you to perform the error checking yourself. StartTrans and CompleteTrans will automatically handle errors, performing a COMMIT or ROLLBACK as appropriate. You can force a ROLLBACK with the FailTrans() function when necessary.

Of course you may need to know whether the transaction has failed or not, and ADOdb provides the HasFailedTrans() function to do this, which returns true if there was an error (or you specifically called FailTrans()), or false if not.

$sql1 = "UPDATE employees SET balance=balance-10 WHERE id=15";
$sql2 = "UPDATE employees SET balance=balance+10 WHERE id=22";
$db->StartTrans();
$db->Execute($sql);
$db->Execute($sql2);
$db->CompleteTrans();
if ($db->HasFailedTrans()) {
	// Something went wrong
}

Note that your database needs to support transactions for these functions to work correctly. (Most databases do, and MySQL InnoDB tables do, but MySQL MyISAM tables do not, for example)

I hope I've whetted your appetite to explore in more detail this database library. There are quite a number of interesting functions that may be useful to automatically generate HTML from database tables, as well as functions to examine your data in more depth, and of course alternative ways to query results. The full manual can be found here. Good luck!