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 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.