Login

PHP Application Development With ADODB (part 2)

In this concluding article, find out about ADODB’s advanced
functions, with examples that demonstrate how ADODB can be used to
optimize multiple-run queries, commit and roll back transactions,
improve performance by caching query results, and automatically write
HTML (or
text) files.In the first part of this article, I introduced you to the ADODB database
abstraction library, and showed you a little of how it works. I demonstrated how
using it in your PHP application development could substantially reduce the time
spent on code rewrites if your RDBMS decided to change shape, and also gave you
a crash course in the basic functions built into the
library.

Fortunately, that isn’t all she wrote. ADODB comes with a whole
bunch of bells and whistles, which allow you to do some fairly nifty new things
in your PHP scripts. Over the next few pages, I’ll be showing you some of them –
so flip the page, and let’s get started!{mospagebreak title=Rapid Execution} In
the event that you need to execute a particular query multiple times with
different values – for example, a series of INSERT statements – the ADODB class
comes with two methods that can save you a huge amount of time and also reduce
overhead. Consider the following example, which demonstrates:

The Prepare() function, which takes an SQL query as
parameter, readies a query for execution, but does not execute it (kinda like
the priest that walks down the last mile with you to the electric chair).
Instead, prepare() returns a handle to the prepared query, which is stored and
then passed to the Execute() method, which actually executes the query (bzzzt!).

Note the two placeholders used in the query string passed to Prepare() –
these placeholders are replaced by actual values each time Execute() runs on the
prepared statement. The second argument to Execute() is a PHP array containing
the values to be substituted in the query string.

It should be noted that
using Prepare() can provide performance benefits when you have a single query to
be executed a large number of times with different values. However, this benefit
is only available to you if your database system supports prepared queries
(MySQL does not at this time, although Interbase and Oracle do); in all other
cases, only simulated functionality is available and Prepare() becomes
equivalent to a simple Execute(), with no inherent performance
gain.{mospagebreak title=A Fear Of Commitment} If your database system supports
transactions (MySQL doesn’t, but quite a few others do), you’ll be pleased to
hear that ADODB allows you to transparently use this feature in your scripts.

The first step here is to turn off auto-committal of data to
the database, via the BeginTrans() method; this method also marks the beginning
of a transaction block, one which can be ended by either CommitTrans() or
RollbackTrans(). Once auto-commit has been turned off, you can go ahead and
execute as many queries as you like, secure in the knowledge that no changes
have (yet) been made to the database.

Every call to Execute() within the
transaction block returns either a true or false value, depending on whether or
not the query was successful. These values can be tracked, and used to determine
whether or not the entire transaction should be committed. Once you’re sure that
all is well, you can save your data to the database via a call to the
CommitTrans() method. In the event that you realize you made a mistake, you can
rewind gracefully with the RollbackTrans() function. {mospagebreak title=Cache Cow} One of the coolest things about ADODB has to be its support for cached
queries. Why? Because caching your queries can result in a fairly significant
performance improvement, especially if you’re executing the same tired old
SELECT every time.

In order to illustrate the difference, let’s take a
look at how this normally works:

This should be familiar to you by now – it’s a very basic SQL
SELECT operation with ADODB. If this was your personal Web site, and you were
getting 5000 hits a minute, you’d be running the query above 30,000 times an
hour. As you might imagine, this will have your database server scurrying around
like a hamster on cocaine – not to mention affecting the performance of your Web
site.

ADODB offers a better option – caching the results of the first
SELECT query, and using this cached resultset in each subsequent run of the
query. This reduces the load on the database server, and can also provide you
with an incremental performance benefit.

The first argument to CacheExecute() is the number of seconds
to cache the query results; the second is, obviously, the query string itself.
The remainder of the script remains unchanged – a cached resultset is processed
in exactly the same manner as a non-cached one.

You can also use the
CacheFlush() method to flush all queries from the cache.{mospagebreak title=What’s On The Menu?} ADODB also comes with a couple of methods designed
specifically for common Web development tasks. One of the most useful is the
GetMenu() method, which retrieves and iterates over a resultset, and uses it to
automatically build a form drop-down list containing the database records. This
comes in very handy for dynamically-generated forms, when the items in the
various form listboxes have to be dynamically built from a
database.

The GetMenu() method takes a number of arguments, which can
be used to control the behaviour of the generated list box. The first argument
is the name for the list (“library”, in this case); the second is the default
value for the list; the third lets you specify whether the first item in the
list should be empty; and the fourth lets you control whether or not the list
allows multiple selection.

As you can see, the contents of the list box are built from
the resultset returned by the query; the first column of the resultset becomes
the label for each list item, while the second is the corresponding value.

The GetMenu() method can simplify the task of developing a Web form
substantially, significantly reducing the amount of code you have to write –
consider using it the next time you need to build a list box from the records in
a database.{mospagebreak title=A Rose By Any Other Name…} ADODB also allows
you to export a resultset into a variety of different formats – comma-separated
text, tab-separated text, or even an HTML table. These functions are not part of
the ADODB class per se; rather, they are packaged as ancillary functions in a
separate file, which needs to include()-d in your scripts. The following example
demonstrates:

A
number of other interesting conversion functions are also shipped with ADODB –
take a look at the documentation for more information.{mospagebreak title=The Final Countdown} And that’s about it for the moment. Over the course of this
two-part article, I introduced you to the ADODB database abstraction class and
demonstrated how it could be used in your Web development efforts. I showed you
the fundamentals – executing queries, iterating over resultsets, obtaining table
and row information – and then moved on to more advanced material, illustrating
how ADODB could be used to optimize multiple-run queries, commit and roll back
transactions, improve performance by caching query results, and automatically
write HTML (or text) files.

That isn’t all, though – ADODB comes with a
wealth of features, and is constantly being improved by its author, and the PHP
community at large. For more information on what it can do, and how you can use
it in your own PHP projects, take a look at the following links:

Note: All examples in this article
have been tested on Linux/i586 with PHP 4.2.0, Apache 1.3.12 and ADODB 2.2.0.
Examples are illustrative only, and are not meant for a production environment.
Melonfire provides no warranties or support for the source code described in
this article. YMMV!