Introduction to ADODB Class Library in PHP (ii)

ADO Caching a Query

Last month, we had a quick look at how to do Select, insert, and update in ADODB. If you are a novice on ADODB, I suggest that you read the last month's article first. ADOdb has a lot more advanced features, and this month, we're going to look at some of them together.

Databases are often the culprit for inefficient applications. Minimizing queries to the database is one way to improve execution efficiency. This can usually be done by caching an entire page of content (there are many ways to do this.) For example, Pear->cache), or if you need to make a dynamic page and just want the query instructions to be cached, you can use ADODB to simply cache the query instructions. Before your view improves your application's poor performance through caching, I suggest you try to optimize your query instructions first. Sometimes, simple indexes can change everything-there are too many so-called professional solutions that use bad indexes. In this article, you can find many examples of this. Now let's look at how ADODB enables you to cache query results for a database. In this example, ADODB saves the results of our last query in the/var/tmp/adodb_cache cache file and retains it for 10 minutes.

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 ($sql); Executes, and caches the results for seconds
if (! $rs) {
Print $db->errormsg (); Displays the error message if no results could is returned
}
else {
while (! $rs->eof) {
Print $rs->fields[0]. ' '. $rs->fields[1]. ' <BR> ';
Fields[0] is surname, Fields[1] are age
$rs->movenext (); Moves to the next row
}//End While
}//End Else


The Cacheexecute () function has two parameters: the first parameter is the time that the cached file will be retained, timed in seconds, and the second parameter is the SQL declaration. The first parameter is optional (some developers may think it should be the second parameter), and if you don't have a time limit, the default is 3,600 seconds, or 1 hours. Cached files are named Adodb_*.cache and you can safely remove them from the file system. You should periodically clear outdated cache files (with Unix "crontab", or Windows "Scheduled Tasks"). Translator Note: The calendar does not know if there is no translation error, I do not have UNIX. ）。 Note that to use the caching method, you need to set the PHP parameter magic_quotes_runtime to OFF (note: In php.ini, set the value to 0). You can modify its value at run time as needed:
Set_magic_quotes_runtime (0);
Just put the above code before you call the database instructions. You can clear the cache at any time by calling Cacheflush (). With security in mind, ADODB also recommends that PHP's parameter register_globals be set to 0 (the default value in the latest version of PHP).


More information on query results

Developers often have questions about the results they want to get, and the most common question is: How many records? ADODB can easily answer this question through RecordCount (). RowCount () is synonymous function.

$sql = "Select surname, age from Employees";
$rs = & $db->cacheexecute ($sql); Executes, and caches the results for seconds
Print $rs->recordcount (). "Rows returned]"; Display number of rows returned


Perhaps the second question arises on the demand list: What is the number of returned fields? Don't worry, ADODB has FieldCount ().

$sql = "Select surname, age from Employees";
$rs = & $db->cacheexecute ($sql); Executes, and caches the results for seconds
Print $rs->fieldcount (). "Columns returned]"; Display number of rows returned



Limit results

Last time we talked about how to make your application simpler and easier to migrate by using a database library function. In moving from MySQL to Informix, I went through a painful porting process. Everything is attributed to the ANSII standard LIMIT clause (for example, the following directives are allowed in MySQL: SELECT name from employee LIMIT 15), which is a very useful feature that is not supported in Informix. (in Informix, the same functionality should be written in: SELECT-name from employee in Informix.) It seems to be a wake-up call for you to stop using non-standard SQL directives in your queries and to seriously learn standard SQL. Fortunately, ADODB has a way of dealing with limit: Selectlimit ().

$sql = "Select surname, age from Employees";
$rs = & $db->selectlimit ($sql, 10, 100); Select rows, starting at row 100
if (! $rs) {
Print $db->errormsg (); Displays the error message if no results could is returned
}
else {
while (! $rs->eof) {
Print $rs->fields[0]. ' '. $rs->fields[1]. ' <BR> ';
Fields[0] is surname, Fields[1] are age
$rs->movenext (); Moves to the next row
}//End While
}//End Else


Selectlimit () takes the SQL declaration as the first argument, the second is the number of columns returned, and the last argument is the offset (the first row returned). Note that this is the reverse of the parameter order of the limit clause in the MySQL directive. Selectlimit () is useful for displaying search results in parts of the page, with the previous and Next buttons to view all the results.

More than once, I see the code will come from the database of all the query results, are using PHP for filtering processing-overkill! You can use the Cacheselectlimit () function to store this type of result.


Processing transactions

Processing transactions is an important feature of many applications. (For starters, dealing with a transaction is a process that you can submit to a dozen query operations, all of which are successful or partially unsuccessful, and need to be considered.)
The classic example is Bank processing. The money is transferred from your account and then transferred to someone else's account. As long as any one of them fails, the entire process must be considered a failure. Otherwise, the money was set aside, not into the other's account, or the money was not drawn, the other side of the account without a single extra tip. )

Processing transactions can be tricky to manage control at the code level. Constant error checking is used to determine whether to commit (all items of a transaction are correct, execute correctly, end a transaction) or rollback (there are errors in the transaction, all changes need to revert to the original condition). ADOdb There are some useful functions that make processing operations work correctly. The following example adds 10 to the balance of one employee and a minus 10 on the other, and the whole process has two queries as a complete transaction.

$sql 1 = "UPDATE employees SET balance=balance-10 WHERE id=15";
$sql 2 = "UPDATE employees SET balance=balance+10 WHERE id=22";
$db->starttrans ();
$db->execute ($sql);
$db->execute ($sql 2);
$db->completetrans ();



When you browse the old code, you may see an older way to do it. When using the BeginTrans (), CommitTrans (), and RollbackTrans () functions, you need to handle the error yourself. Starttrans () and Completetrans () will automatically handle errors and commit or rollback appropriately. When needed, you can enforce rollback by Failtrans ().

Of course you may need to know whether the deal is a failure or a success. ADODB provides Hasfailedtrans () to implement this feature. If an error occurs (or you can refer to this condition as Failtrans ()), return True, and if successful, return false.

$sql 1 = "UPDATE employees SET balance=balance-10 WHERE id=15";
$sql 2 = "UPDATE employees SET balance=balance+10 WHERE id=22";
$db->starttrans ();
$db->execute ($sql);
$db->execute ($sql 2);
$db->completetrans ();
if ($db->hasfailedtrans ()) {
Something went wrong
}


It is worth noting that your database needs to support these transaction functions. (most databases are supported, however, MySQL InnoDB table support, can be MySQL MyISAM table is not supported.) )

I hope that all I have done will give you a great interest in database library functions. There are also a number of interesting functions that can be used to automatically generate HTML from database tables, and there are different ways to achieve the same result. You can find the complete manual here.
Good luck!
2003.07.31

Original address: http://www.databasejournal.com/features/php/article.php/2234861