Reading Access Databases with PHP and PECL

      9 Comments on Reading Access Databases with PHP and PECL

A Question of Access

You’ve already seen, in previous articles, just how easy it is to read and write Microsoft Excel spreadsheets with PHP and PEAR. But spreadsheets are just one way of representing structured data – the other, of course, is to use a database. Well, guess what? If your database happens to be Microsoft Access, PHP has you covered there as well!

In this article, I’ll introduce you to PHP’s MDBTools extension, which provides an API to programmatically read data from Microsoft Access database files. If your project involves working with such files, extracting database records either for calculations or for conversion to other formats, you’ll find this extension invaluable. Come on in, and see how it works!

Laying The Foundation

Microsoft Access support in PHP comes through PECL’s ext/mdbtools extension, which is maintained by Hartmut Holzgraefe. This extension is itself a wrapper around the MDBTools package (http://mdbtools.sourceforge.net/), which provides a set of tools to read Access database schema and records. This MDBTools package also provides a simple ODBC-compliant driver that can be used to read data from Access databases using standard SELECT queries.

To use PECL’s ext/mdbtools extension, first download, install and compile the MDBTools package (v0.6pre1) for your platform – you’ll find detailed instructions on the MDBTools Web site. Once this is done, proceed to install the PECL MDBTools extension using the pecl command. Note that at the current time, a Windows version of ext/mdbtools is not available; the following steps assume a *NIX system.

The PECL installer will now download the source code, compile it and install it to the appropriate location on your system.

Alternatively, manually download the source code archive (v1.0.0 at this time) from http://pecl.php.net/package/mdbtools and compile it into a loadable PHP module with phpize:

This procedure should create a loadable PHP module named mdbtools.so in your PHP extension directory. You should now enable the extension in the php.ini configuration file, restart your Web server, and check that the extension is enabled with a quick call to phpinfo():

If you’d like to also use the MDBTools ODBC driver, which provides an alternative way of reading Access database files (examples are included in this article), you’ll need to perform two additional tasks:

  • Ensure that your PHP build includes unixODBC support. On UNIX systems, this is accomplished by downloading and installing the unixODBC tools and libraries from http://www.unixodbc.org (if you don’t already have them), and then compiling PHP with the –with-unixodbc configuration directive. By default, the unixODBC libraries will be installed to /usr/local/.
  • When compiling the MDBTools package, ensure that you include the –enable-sql and –with-unixodbc arguments to include ODBC support and compile the MDBTools ODBC driver. Note that you might encounter compilation errors during this process; to correct these, change the MDBTools source code as suggested in the post at http://article.gmane.org/gmane.comp.db.mdb-tools.devel/1090, and things should compile normally.

Turning The Tables

Now that you have all the pieces installed, let’s see them in action. Here’s a simple PHP script that uses ext/mdbtools to read the example Northwind database supplied with Microsoft Access and display the tables in it:

To use an Access database with ext/mdbtools, the first step is always to call the mdb_open() function with the full path to the database file; this creates a database handle that can be used for all future operations. Next, the mdb_tables() function is used to retrieve a list of all the tables in the database, as an enumerated array. This array is then processed with a foreach loop, and the table names are displayed as list items. At the end of the script, the mdb_close() function is used to destroy the database handle and free up unused memory.

Here’s what the output might look like:

To work with a specific table, use the mdb_table_open() function to create a handle for the table, and then use that handle for all table-related operations. Here’s an example, which enhances the previous example to also display the number of records in each table:

In this listing, the mdb_table_open() function is used to create a table handle. The resulting handle is then used for table-related functions such as mdb_num_rows(), which returns the number of records in the specified table. Once done, the mdb_table_close() function is used to destroy the table handle.

Here’s what the revised output might look like:

Field of View

It’s also possible to obtain detailed information on the structure of an Access table using ext/mdbtools. This is accomplished with the mdb_table_fields() function, which accepts a table handle and returns an array containing field information. Each element of the array represents a field of the table, and contains information on the field name, size, type, scale and precision. Here’s an example, which illustrates:

Notice the mdb_type_name() function in this listing. This function translates the raw field type into an English-language type description, such that it is easier to read. Here’s an example of the output:

This information can be used to enhance the table display script shown previously. Here’s how:

And here’s what the output looks like:

Delving Into The Data

Now that you know how to retrieve basic table information, let’s move on to actually reading table contents. It’s worthwhile mentioning at this point that the MDBTools package only allows reading existing records from an Access database; writing new records or modifying existing ones is not currently supported.

Here’s an example of reading and printing all the records in Northwind’s ‘Categories’ table:

Similar to the mysql_fetch_row() function is the mdb_fetch_row() function, which accepts a table handle and fetches a single record from the specified table as an array. Fields of that record can then be accessed using standard array notation.

The mdb_fetch_row() function returns the next available record on each invocation. This makes mdb_fetch_row() very suitable for use in a while loop keyed on the number of records in the table – information that is easily obtained via the mdb_num_rows() method discussed previously.

You can also use the mdb_fetch_assoc() function to represent each record as an associative array of field-value pairs. Here’s a revision of the previous example, which demonstrates:

It should be noted that the mdb_fetch_row() and mdb_fetch_assoc() functions include a known bug which may result in the field values getting corrupted. This bug is under investigation by the developer and should be corrected in a future release.

Hooking Up

An alternative way of accessing Access databases under PHP, is to use unixODBC and the MDBTools unixODBC driver. This requires the unixODBC package and unixODBC support in your PHP build; instructions for doing these tasks are available at the beginning of this article. Once you’ve added this support, it’s a two-step process to configure an ODBC DSN for the Access database file:

1. Data sources are listed in the odbc.ini file, located by default in /usr/local/etc/odbc.ini. Pop open this file and add the following lines to it using your favorite text editor (change the path to the database file as needed):

2. ODBC drivers are listed in the odbcinst.ini file, located by default in /usr/local/etc/odbcinst.ini. Place an entry for the MDBTools unixODBC driver in this file, by adding the following block to it (change the path to the driver file if needed):

With both the driver and DSN configured, you can proceed to access (pun intended) the database file using PHP’s standard ODBC functions and SQL. Here’s an example:

And here’s what the output looks like:

SQLite My Fire!

The MDBTools unixODBC driver is a very sparse driver, with support for only a limited subset of SELECT queries. This can be a bit of a dampener if you were looking forward to adding or modifying data to the Access database using PHP. Don’t lose heart, though – you can still do some pretty nifty things, including export data from an Access database into some other DBMS, such as SQLite or MySQL (and modify it there).

Here’s an example of one such application, which queries an Access database using unixODBC and MDBTools, and writes the resulting record set to an SQLite database:

Nothing very fancy here: the script opens up connections to both Access and SQLite database files, executes a SELECT query to retrieve all the records from the Access table with odbc_exec() and writes each record from the result set to the SQLite table using sqlite_query(). And now, when you look in the SQLite database, you’ll see all the data that was transferred:

With PHP’s built-in SQlite support, it’s a snap to now write PHP code to interact with and modify this data!

As these examples illustrate, the MDBTools extension makes it quite easy to read Microsoft Access databases in PHP, without actually requiring the use of any proprietary components. You probably wouldn’t have thought this was possible…but it is, and it works surprisingly well too!

I hope you enjoyed this article, and that it will save you some time the next time you sit down to integrate data from Microsoft Access with your PHP application. Happy coding!

This article copyright Melonfire, 2008. All rights reserved.