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.
shell# pecl install mdbtools
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:
shell# cd mdbtools-1.0.0 shell# phpize shell# ./configure shell# make shell# make install
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:
<html>
<head></head>
<body>
<?php
// initialize database handle
$mdb = mdb_open('NWIND.MDB');
if ($mdb === false) {
die('ERROR: Cannot initialize database handle');
}
// get table listing
$tables = mdb_tables($mdb) or die('ERROR: Cannot get table listing');
if (sizeof($tables) > 0) {
echo '<h2>Table listing</h2>';
foreach ($tables as $t) {
echo "$t <br/>\n";
}
}
// destroy database handle
mdb_close($mdb);
?>
</body>
</html>
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:
<html>
<head></head>
<body>
<?php
// initialize database handle
$mdb = mdb_open('NWIND.MDB');
if ($mdb === false) {
die('ERROR: Cannot initialize database handle');
}
// get table listing
$tables = mdb_tables($mdb) or die('ERROR: Cannot get table listing');
if (sizeof($tables) > 0) {
echo '<h2>Table listing</h2>';
foreach ($tables as $t) {
// get table handle
$tbl = mdb_table_open($mdb, $t) or die('ERROR: Cannot open table ' . $t);
// find and print number of records in table
$rows = mdb_num_rows($tbl);
echo "$t ($rows) <br/>\n";
// close table
mdb_table_close($tbl);
}
}
// destroy database handle
mdb_close($mdb);
?>
</body>
</html>
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:
<html>
<head></head>
<body>
<?php
// initialize database handle
$mdb = mdb_open('NWIND.MDB');
if ($mdb === false) {
die('ERROR: Cannot initialize database handle');
}
// get field data for 'Suppliers' table
$tbl = mdb_table_open($mdb, 'Suppliers') or die('ERROR: Cannot open table');
// get field information
$fields = mdb_table_fields($tbl);
foreach ($fields as $f) {
echo "Name: " . $f['name'] . "<br/>";
echo "Type: " . mdb_type_name($mdb, $f['type']) . "<br/>";
echo "Size: " . $f['size'] . "<br/>";
echo "Precision: " . $f['prec'] . "<br/>";
echo "Scale: " . $f['scale'] . "<p/>";
}
// close table
mdb_table_close($tbl);
// destroy database handle
mdb_close($mdb);
?>
</body>
</html>
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:
<html>
<head></head>
<body>
<?php
// initialize database handle
$mdb = mdb_open('NWIND.MDB');
if ($mdb === false) {
die('ERROR: Cannot initialize database handle');
}
// get table listing
$tables = mdb_tables($mdb) or die('ERROR: Cannot get table listing');
if (sizeof($tables) > 0) {
echo '<h2>Table listing</h2>';
foreach ($tables as $t) {
// get table handle
$tbl = mdb_table_open($mdb, $t) or die('ERROR: Cannot open table ' . $t);
// find and print number of records in table
$rows = mdb_num_rows($tbl);
echo "$t ($rows) <br/>\n";
// get field information
$fields = mdb_table_fields($tbl);
echo "<ol>\n";
foreach ($fields as $f) {
echo "<li>" . $f['name'] . ": " . mdb_type_name($mdb, $f['type']) . "</li>\n";
}
echo "</ol>\n";
// close table
mdb_table_close($tbl);
}
}
// destroy database handle
mdb_close($mdb);
?>
</body>
</html>
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:
<html>
<head></head>
<body>
<?php
// initialize database handle
$mdb = mdb_open('NWIND.MDB');
if ($mdb === false) {
die('ERROR: Cannot initialize database handle');
}
// open categories table
$tbl = mdb_table_open($mdb, 'Categories') or die('ERROR: Cannot open table ');
// read and print records
echo "<table>\n";
while ($row = mdb_fetch_row($tbl)) {
echo "<tr>\n";
foreach ($row as $v) {
echo "<td>$v</td>\n";
}
echo "</tr>\n";
}
echo "</table>\n";
// close table and database
mdb_table_close($tbl);
mdb_close($mdb);
?>
</body>
</html>
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:
<html>
<head></head>
<body>
<?php
// initialize database handle
$mdb = mdb_open('NWIND.MDB');
if ($mdb === false) {
die('ERROR: Cannot initialize database handle');
}
// open categories table
$tbl = mdb_table_open($mdb, 'Categories') or die('ERROR: Cannot open table ');
// read and print records
while ($row = mdb_fetch_assoc($tbl)) {
foreach ($row as $k => $v) {
echo "$k: $v <br/> \n";
}
echo "<br/>\n";
}
// close table and database
mdb_table_close($tbl);
mdb_close($mdb);
?>
</body>
</html>
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):
[Northwind] Description = Example Database Database = /user/local/apache/htdocs/NWIND.MDB Driver = MDBToolsODBC
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):
[MDBToolsODBC] Description = MDB Tools ODBC drivers Driver = /usr/local/lib/libmdbodbc FileUsage = 1
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:
<?php
// open connection
$db = odbc_connect('Northwind', null, null);
if (!$db) {
die('ERROR: Cannot connect!');
}
// execute query
$sql = "SELECT CompanyName, PostalCode, Country FROM Suppliers";
$result=odbc_exec($db, $sql) or die('ERROR: Cannot execute query!');
// iterate over result set and print records
while ($row = odbc_fetch_array($result)) {
echo $row['CompanyName'] . ": " . $row['PostalCode'] . ", " . $row['Country'] . '<br/>';
}
// close connection
odbc_close($db);
?>
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:
<?php
// open SQLite connection
$sqlite = sqlite_open('/tmp/new.db');
if (!$sqlite) {
die ("ERROR: Could not open SQLite database");
}
// open ODBC connection
$mdb = odbc_connect('Northwind', null, null);
if (!$mdb) {
die('ERROR: Could not connect to MDB database');
}
// execute SELECT query
$sql = "SELECT CompanyName, PostalCode, Country FROM Suppliers";
$result = odbc_exec($mdb, $sql) or die('ERROR: Could not execute SELECT query!');
// iterate over result set
// formulate INSERT queries to copy records to SQLite
while ($row = odbc_fetch_array($result)) {
// escape input data
$company_name = sqlite_escape_string($row['CompanyName']);
$postal_code = sqlite_escape_string($row['PostalCode']);
$country = sqlite_escape_string($row['Country']);
// execute INSERT query
$sql2 = "INSERT INTO suppliers_new (CompanyName, PostalCode, Country) VALUES ('$company_name', '$postal_code', '$country')";
if (sqlite_query($sqlite, $sql2) === false) {
echo "ERROR: Could not execute INSERT query. " . sqlite_error_string(sqlite_last_error($sqlite));
}
}
// close connections
odbc_close($mdb);
sqlite_close($sqlite);
?>
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:
sqlite> SELECT * FROM suppliers_new; Exotic Liquids |EC1 4SD|UK New Orleans Cajun Delights |70117 |USA Grandma Kelly's Homestead |48104 |USA Tokyo Traders |100 |Japan Cooperativa de Quesos 'Las Cabras'|33007 |Spain Mayumi's |545 |Japan Pavlova, Ltd. |3058 |Australia Specialty Biscuits, Ltd. |M14 GSD|UK ...
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.
December 5, 2008 at 3:59 am
Does PECL support UNC to access MDB file from network ?
April 16, 2009 at 1:19 pm
Amazing great going
<a href=’http://www.kiran.org.in’>Kiran Hota</a>
May 10, 2009 at 5:16 pm
this is very good indeed.
http://myfacefriends.com
June 3, 2009 at 4:40 am
I am running PHP 5.1.6 and I have mdbtools-0.6pre1 installed.
but then when I execute the "pecl install mdbtools" command, it returns an error saying I need at least PHP 4.0.0. The PHP Api version in the configuration file has a date of 20041225, wonder if this is right? See details below.
Any idea how to fix the problem? Any help would be greatly appreciated.
# pecl install mdbtools
downloading mdbtools-1.0.0.tgz …
Starting to download mdbtools-1.0.0.tgz (8,783 bytes)
…..done: 8,783 bytes
5 source files, building
running: phpize
Configuring for:
PHP Api Version: 20041225
Zend Module Api No: 20050922
Zend Extension Api No: 220051025
…
checking for mdbtools in default path… found in /usr/local
…
checking for mdb_init in -lmdb… yes
checking PHP version… configure: error: need at least PHP 4.0.0
ERROR: `/tmp/tmp74zkaB/mdbtools-1.0.0/configure –with-mdbtools’ failed
June 29, 2009 at 6:47 pm
I am trying to get data from an Access database and thought this tool would be the perfect choice for doing so. After trying to get it work for days I can’t seem to get any data out of any of my access tables. I am wondering if anybody else has run into this issue and what they did to solve this issue. It seems to me like access the tables is no problems as I can print out the data types for all the tables. Also could it be a field type that is not allowing this or something else? Is there a sample database and table you can download to test to make sure it’s not something with your tables? Any help would be great.
July 14, 2009 at 8:03 am
The underlying mdbtools library is out of date – five years old. The latest release is named mdbtools-0.6pre1.
This is not production quality. You should never rely on this.
If you can do, check the code for errors and give a new release to the community.
If no one can do this, just forget ist. In this case mdbtools is dead. It should be removed from the php stack.
ruediger [dot] schmidt [at] bam [dot] de
March 16, 2011 at 10:53 pm
I’m having the same problem as reported before using pecl-mdbtools – not getting data out of the tables using the tutorial code (although headers, number of records, etc. all work). Has anyone figured out what’s going on? I’ll try the unixODBC and mbtools MDB connector, but pecl-mdbtools would be simpler if I could make it work.
I don’t accept that the library should be expected to fail because it’s old. It’s a maintained FreeBSD port, it compiles without errors, and the MDB file I’m using is a 10-year old format itself. I’m sure the solution will be obvious in hindsight…..
Thanks,
Dale
April 13, 2011 at 4:35 pm
Fyi, MDBTools development has been moved from Sourceforge to Github (https://github.com/brianb/mdbtools), although the developers still use the Sourceforge project mailing list (and you can browse the mailing list archives).
However, the FreeBSD ports for pecl-mdbtools and mdbtools-0.5 have not been updated (probably because that was the last "official" release from the developers). I downloaded the head from Github and compiled (same instructions, although using gmake instead of make due to FreeBSD, and the –enable-sql option is no longer supported). I’m now using the mdbtools cli utilities with PHP as recommended by one of the developers and all is working great (see mail list archive).
Dale
May 26, 2011 at 2:55 pm
i am using Linux and mdbtools and the execution fine but still having segmentation fault all the time even the db size is large or not
Please Answer Me