An introduction to the ADOdb class library for PHP

Tuesday Jun 17th 2003 by Ian Gilfillan
Share:

A common beginner (and not so beginner) mistake is to develop an application without any consideration of the future. All too often, applications remain tied to the wrong database, inflexible and not performing optimally. That's where using a well-designed database class library can make all the difference. This month Ian Gilfillan gets you up and running with a brief introduction to ADOdb, with more detail to follow in subsequent articles.

Why use a database class library?

A common beginner (and not so beginner) mistake is to develop an application without any consideration of the future. It's not impossible that one day the database you use today may be inappropriate, and you need to rewrite your application to use another database. But the PHP functions don't make your life easy when you do this - there are different functions for each DBMS - mysql_connect(), mssql_select_db(), for example. Going through all of your code and changing the functions, as well as in many cases changing the query syntax, is not a job to undertake lightly. So all too often, applications remain tied to the wrong database, inflexible and not performing optimally. That's where using a well-designed database class library can make all the difference. These allow you to change to another DBMS with minimal effort - using the same functions no matter what choice you make, with only a different connection parameter determining which DBMS to use. By removing the connection string to a single location, there is just one place to make the change. In ADOdb, for example, simply replace $db = NewADOConnection('access') with $db = NewADOConnection('mysql'), and you have an upgrade. There are a number of other class libraries out there. I've worked with many of them, but this article will focus on just one I particularly happen to like - ADOdb. This month is a brief and basic introduction to getting you up and running, while next month we'll delve a little deeper.

ADOdb currently supports MySQL, PostgreSQL, Interbase, Firebird, Informix, Oracle, MS SQL 7, Foxpro, Access, ADO, Sybase, FrontBase, DB2 and generic ODBC. If your database isn't in there, you can probably still use ADOdb generically, but spread the word, and I'm sure you won't have to wait long for it to be added.

Installing ADOdb

Installing ADOdb is extremely easy.

  • First, be sure you are running PHP version 4.0.4 or later. If you're not, I seriously suggest you upgrade!
  • Download the .zip or .tgz file from SourceForge
  • Unpack the file in the directory of your choice. This should not be in the webtree! Although the include files in ADOdb have been given the extension .inc.php, which means that poorly configured web servers that allow the serving of .inc files will not display the contents as plain text, it's still not a good idea to place libraries in the web tree. To unpack a tar file, change to the directory you want to install it in, and run tar -zxvf adodb350.tgz. This creates a directory, adodb, and numerous sub-directories.

Testing your installation

Connecting is equally easy. You can test your installation by placing the following three lines in a piece of code, replacing the variables with the appropriate values in your setup.

include("$adodb_path/adodb.inc.php"); // includes the adodb library
$db = NewADOConnection("$database_type"); // A new connection
$db->Connect("$host", "$user", "$password", "$database_name");

Now you have one successful database connection object, $db. You could also use ADONewConnection instead of NewADOConnection - the two are alternative names for the same function. The connection variables are those specific to your installation, and the database type is dependent on your DBMS. It can be one of the following:

  • access (Microsoft Access/Jet)
  • ado (Generic ADO, the base for all the other ADO drivers)
  • ado_access (Microsoft Access/Jet using ADO)
  • ado_mssql (Microsoft SQL Server using ADO)
  • db2 (DB2)
  • vfp (Microsoft Visual FoxPro)
  • fbsql (FrontBase)
  • ibase (Interbase 6 or before)
  • firebird (Firebird)
  • informix72 (Informix databases before Informix 7.3)
  • informix (Informix)
  • maxsql (MySQL with transaction support)
  • mssql (Microsoft SQL Server 7)
  • mssqlpo (Portable mssql driver)
  • mysql (MySQL without transaction support)
  • mysqlt (MySQL with transaction support, identical to maxmysql)
  • oci8 (Oracle 8/9)
  • oci805 (Oracle 8.0.5)
  • oci8po (Oracle 8/9 portable driver)
  • odbc (Generic ODBC, the base for all the other ODBC drivers)
  • odbc_mssql (MSSQL via ODBC)
  • odbc_oracle (Oracle via ODBC)
  • oracle (Oracle 7)
  • postgres (PostgreSQL)
  • postgres64 (PostgreSQL 6.4)
  • postgres7 (PostgreSQL 7, currently identical to postgres )
  • sqlanywhere (Sybase SQL Anywhere)
  • sybase (Sybase)

If you're having trouble connecting, the obvious place to look is in the path or connection values, but please make sure you can connect normally using those values before you blame ADOdb (obvious I know, but we spend most of our time fixing the obvious, so lets get it out of the way now). If you managed to connect, you are now ready to use the library in your applications.

Connecting to the database in your scripts

Before you rush off and add the above few lines to every script that needs them, take a step back. The point of the library is to make your applications flexible, and portable. Hardcoding the connection paramaters, or the path to the adodb library, is a general beginner mistake that immediately undermines part of the point of using a library like this. If your password changes, you would need to make changes to each of those scripts. Also, your password information is in the webtree, which is less secure in case of server hiccups. Rather, place the password information in a separate include file, perhaps in the same location as the rest of the adobdb installation. The same applies to the path value. If you had to rollout your application on a different server, you cannot guarantee the directory structure would be the same, so it pays to store that in only one location too. I suggest getting your webserver to automatically include a file containing this basic information each time a PHP script is called.

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

The database name would of course be different in many of your scripts, so that can stay hardcoded. You may also want to use persistent connections, instead of creating a new connection each time (this speeds up many web applications, but its effectiveness will differ with each database/application/load combination). To do this, replace Connect with PConnect. The file db_values.inc.php contains something like:

<?
$database_type="mysql";
$host = "localhost"; // the database server 
   and web server are on the same machine
$user = "ian"
$password = "let_me_in"
?>

The value for $adodb_path can be automatically included in a prepend file. You can set your version of PHP to automatically prepend a file in the php.ini configuration file, for example as follows:

; Automatically add files before or after any PHP document.
auto_prepend_file = /usr/local/build/apache/www/tool_lib/defaults.inc
auto_append_file =

The file defaults.inc would contain the value for $adbdb_path, for example:

<?
$adodb_path = "/usr/local/build/apache/www/tool_lib/";
?>

There are other ways of doing it, but I find this way relatively painless when migrating.

Selecting from a database

As with any well-developed library, and with the native PHP functions themselves, there are a number of ways to access results, depending on what you plan to do with them. Here's one way:

$sql = "SELECT surname, age FROM employees";
$rs = &$db->Execute($sql);
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

In this example, $rs->fields is an array containing the values returned. The array index is numeric by default, but you can also make it an associative array, by specifying the mode before you Execute the query, as follows:

$sql = "SELECT surname, age FROM employees";
$db->SetFetchMode(ADODB_FETCH_ASSOC); // Return associative array
$rs = &$db->Execute($sql);
if (!$rs) {
  print $db->ErrorMsg(); // Displays the error message if no results could be returned
}
else {
  while (!$rs->EOF) {
    print $rs->fields['surname']." ".$rs->fields['age']."<BR>";
    $rs->MoveNext();  //  Moves to the next row
  }  // end while
} // end else

An alternative way to browse results is to return each row as on object. ADOdb has a function called FetchNextObject(), which does this, as well as automatically move to the next row.

$sql = "SELECT surname, age FROM employees";
$db->SetFetchMode(ADODB_FETCH_ASSOC); // Return associative array
$rs = &$db->Execute($sql);
if (!$rs) {
  print $db->ErrorMsg(); // Displays the error message if no results could be returned
}

// loop through results
while ($row = $rs->FetchNextObject()) {
	// The field names need to be uppercase
	print $row->SURNAME." ".$row->AGE."<BR>";
}

Inserting and updating records

The basic INSERT is quick and easy, with the syntax identical to when you SELECT:

$sql = "INSERT INTO employees (surname, age) values ('Clegg','43')";
if (!($db->Execute($sql))) {
	print 'Error inserting: '.$db->ErrorMsg().'<BR>';
}

The real advantage of the library is that it allows you to insert records into different databases with the same syntax, where this would normally be impossible. There are two common situations where this occurs. Firstly, quoting. All quotes need to be escaped so as not to cause a syntax error, but some databases insert a single quote, others two single quotes. So instead of using a PHP function such as addslashes(), as many of you may be used to, you can rather use ADOdb's qstr(), which takes a string, and returns it correctly formated for the DBMS you're using.

Secondly, dates. Many databases accept differing, incompatible formats for their date type. ADOdb has a function called DBDate(), which accepts a date in either Unix timestamp, or ISO (Y-m-d) format, and converts it to whatever format is appropriate for your specific database. Below is an example showing these two functions in action:

$employee_surname = $db->qstr("d'Angelo");
$arrival_time = $db->DBDate(time());
// The above two functions also add the enclosing quotes, so, $arrival_time, not '$arrival_time'
$sql = "INSERT INTO employee_arrival (arrival_time,surname) values ($arrival_time,$employee_surname)";
if (!($db->Execute($sql))) {
	print 'Error inserting: '.$db->ErrorMsg().'<BR>';
}

You update in exactly the same way, for example:

$sql = "UPDATE employees SET age='44' WHERE id='121')";
if (!($db->Execute($sql))) {
	print 'Error updating: '.$db->ErrorMsg().'<BR>';
}

This has just been the bare bones - next time we'll look at some of the more advanced features ADOdb provides. If I've whetted your appetite and you can't wait, I suggest you take a look at PHP Everywhere site, home of ADOdb, which contains much helpful information.

» See All Articles by Columnist Ian Gilfillan

Share:
Home
Mobile Site | Full Site
Copyright 2017 © QuinStreet Inc. All Rights Reserved