ADOdb is a powerful database abstraction library for PHP (as well as Python) designed to simplify some of the day to day database activities you may need in your PHP applications. I’ve used this library for about 3 years now, from site to site, just to simply the retrieval of data and cache the data locally for ease of use.

Throughout this small guide, the focus will be on getting the most out of the ADOdb library for accessing MySQL databases. One of the best parts of ADOdb in my opinion is the ability to switch between different databases without having to change your code. The last application I created with the ADOdb library was written locally on my development machine using a traditional LAMP setup, but upon completion was deployed to a Windows 2008 server running Microsoft’s SQL Server. I encountered very few issues in the transition, as swapping between database types was really not an issue, as long as it was coded it properly.

Here’s a quick list of what exactly will be covered in this “getting started” post.

Contents

• The Installation, Connection String and Config
• Performing a SELECT, looping through results
• Using the CACHE Mechanism to Speed up queries
• The EXECUTE Syntax for performing Updates/Inserts

The Installation, Connection String and Config

First things first, you are going to need to include the ADOdb library in your website’s server someplace. The easiest way to do this, is simply to create a folder in your project, call it ADODB, and download the library into that folder.

You will find the Download for ADOdb here: http://sourceforge.net/project/showfiles.php?group_id=42718

There are 3 different versions, each suited to which version/language you will be using. Personally, I am using the PHP5 version of the library. Simply download the archive and extract it into your project in a folder of your choosing. For this example, lets put it into your document root into the ADODB folder. If you are feeling savvy, feel free to also create a location on your web server for the library, and simply modify your include paths to search that location.

Next you are going to need a database to play around with… I went ahead and created a database on my development machine called adodb_test, and created a table called contacts with the following SQL commands. This will also insert 2 example contacts into the table with dummy information.

CREATE DATABASE `adodb_test`;

CREATE TABLE `adodb_test`.`contact` (
`id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY ,
`FirstName` VARCHAR( 50 ) NOT NULL ,
`LastName` VARCHAR( 50 ) NOT NULL ,
`Phone` VARCHAR( 15 ) NOT NULL ,
`Email` VARCHAR( 100 ) NOT NULL
) ENGINE = MYISAM ;

INSERT INTO `adodb_test`.`contacts` (
`id` ,
`FirstName` ,
`LastName` ,
`Phone` ,
`Email`
)
VALUES (
NULL , 'Joe', 'User', '555-555-5555', 'joe@joeuser.com'
), (
NULL , 'John', 'Doe', '555-555-5555', 'john.doe@nobody.com');

Now that you have the files downloaded and an example database setup, including it into your project is as simple as below.

$config['server'] = "localhost"; // The URL/IP for your database server
$config['dbtype'] = "mysql"; // The type of database we are using (mysql in this example)
$config['username'] = "username"; // Username to connect to the database
$config['password'] = "password"; // Password to connect to the database
$config['database'] = "adodb_test"; // The Name of the Database which to use

require_once("ADODB/adodb.inc.php");;
$db = NewADOConnection($config['dbtype']);
$db->debug = true;
$db->Connect($config['server'], $config['username'], $config['password'], $config['database']);

// Do your SQL work here

$db->close();

Now generally I have a config.php file stored someplace out of my SVN repository, making it unique to each deployment of the application I create. The $config variables are usually set within this file, but for this example, I am trying to keep it all in a single script for ease of understanding.

The above code will establish a connection to your MySQL database or provide an error if it cannot. At the end, the $db->close() will terminate the database connection. The line of code “$db->debug = true” tells the library you are going to be in debug/development mode, and will output every single query that ADOdb executes, as well as provide errors on anything that might occur. It’s extremely useful for debugging your applications and seeing exactly what your code is trying to accomplish.

Performing a SELECT, looping through results

Well, now that we are connected, we might as well write a query for new table. Let’s say we want to write a command that will retrieve all of the contacts in our database. Well, there’s a nice little command that makes it easy to perform this action.

$res = $db->GetAll("select * from contacts");
foreach($res as $row) {
echo $row['FirstName']." ".$row['LastName']."
";
}

This code will loop through every row returned from the database, echo’ing the first and last name of each person on its own line. You can also append a WHERE clause into this statement to refine your results. There are a few other nice commands that are useful when selecting records as well.

// Returns a single row, and single column based on WHERE criteria (or first available)
$res = $db->GetOne("select LastName from contacts WHERE FirstName = 'John'");
echo $res; // Returns "Doe"
// Returns an Array containing a single Row based on WHERE criteria (or first available)
$res = $db->GetRow("select * from contacts");
var_dump($res); // Returns the row for "John Doe"

Pretty simple eh? This is one of the reasons I moved over to ADOdb a few years ago, was simply because I could write code more efficently that if I was just using straight PHP commands. Now that PHP has its PDO Layer its a lot easier to use with straight PHP, but I still prefer the ADOdb library, and hopefully through this article you will find out why.

Using the CACHE Mechanism to Speed up queries

A lot of applications repeat a lot of queries, over and over and over, and will return the same results during a period of time. Let’s say this example database of contacts is used for a user base of 10,000 users. Every time one of the users loads a page looking up an index of contacts, they will probably be getting the same results. In a normal application repeating and hammering our database server with all of these duplicate requests is counter productive, so what to do?

ADOdb provides a solution to your problem, it has built in record set caching, and its incredibly easy to use and build into your application. All of the above code does not use the cache system, but if you wanted it to, its as simple as changing the method you are calling from ADOdb.

$res = $db->CacheGetAll(60,"select * from contacts");
foreach($res as $row) {
echo $row['FirstName']." ".$row['LastName']."
";
}

// Returns a single row, and single column based on WHERE criteria (or first available)
$res = $db->CacheGetOne(60,"select LastName from contacts WHERE FirstName = 'John'");
echo $res; // Returns "Doe"
// Returns an Array containing a single Row based on WHERE criteria (or first available)
$res = $db->CacheGetRow(60,"select * from contacts");
var_dump($res); // Returns the row for "John Doe"

Notice the change, all of the methods called from the ADOdb Object are now prefixed with the word Cache. Also notice, before the query, we now have a number being passed into the call. The number I chose for this example was 60, meaning that the query will be cached on the server’s disk for 60 seconds. If one user hits this page, and loads that query, it will record the results to a temp file on the server, and feed the results back for anyone else from the temp file for 60 seconds.

So what exactly does this do? Well, now lets say you do a query for all users with the last name starting with the letter “S”, and there are 5,000 results. That query is probably pretty intense if your database is that large. So now imagine if 3,000 people use that same query in a hour, it would be extremely intensive on your database, and might cause some problems with the server. So by using the ADOdb caching mechanism, you could set it up so that the first person that loads the page, actually hits the database, and then every user after that wouldn’t be using the database, but a flat file in your temp folder. The performance boost for applications like this would be incredible.

Now the caveat to this is, by using cached results you won’t be able to see immediate results, unless you design your update process to flush the cache whenever a record is updated. It’s the same theory that the WP Super Cache plug-in for WordPress uses. Less Database Calls, more Flat Files, but the same functionality as a dynamic application.

The EXECUTE Syntax for performing Updates/Inserts

Updating and Inserting is pretty much just as easy as running queries against the database, very straight forward in how you can use the execute statement.

$db->Execute("INSERT INTO contacts VALUES (null,'Firstname','Lastname','phone','emailaddress')");

// or
$sql = "INSERT INTO contacts VALUES (null,'Firstname','Lastname','phone','emailaddress')";
$db->Execute($sql);

Essentially all the execute command is doing is running whatever SQL you are putting into the EXECUTE command, and will return either true or false based on whether it succeeds or not. You can use the EXECUTE command to fire stored procedures or any SQL command that you would like just to pass straight to the database.

More about ADOdb to Come!

In the next post about ADOdb I am going to cover using the ADOdb Active Records implementation as well as using XML to generate and update your table schema. There are some downfalls to using ADOdb’s Active Record implementation, but let me tell you, they are incredibly organized and a great tool for developing complex applications.