Using an Access Database with PHP

A previous client of mine had an Access database of products they sell, which they had been using offline. But increasingly this working arrangement had proven to be limited. There had been too many instances when they needed to use the database but weren’t near the computer where they kept their database. Wouldn’t it have been better for them to able to maintain their database where ever they were? Of course, but how? Simple – put it online. In addition, having their product database online opens the door to using it as the basis of a website for their business. There’s another advantage to having an online database, but which is a significant – multi-user access. That is: storing the database on a server means that any authorized person be able to use the company’s product database using nothing more than a browser. And all as a result of moving an existing Access database file from an offline to an online location. To make the transition to online databases easier, the existing Access database can be kept unchanged and simply uploaded to a suitable Windows host. (Linux hosting is possible too, but that’s slightly more complicated.) The hosting is not enough in itself, though, because you also need to build an interface allowing them to read and write to the database. Building an interface may sound daunting, but it needn’t be. In effect, what you’ll be doing is replacing the forms listed under the Forms tab in Access. In this article I’ll focus on the essential elements of PHP you’ll need to use an existing Access database online. One other item that’s standard with a Windows installation is the availability of ODBC drivers. These are essential for the use of Access from PHP.

Front-end and Back-end

First of all, let’s clarify how the client’s Access database would be opened. Normally they’d use Microsoft Access on their PC, perhaps by double-clicking the icon for the database file. With the database online, however, they will be opening the database in a browser. How can a browser be used to open, view, and edit an Access database? Well, think of the MS Access application as a user interface. As a UI, it has been designed to be used within Windows to read and write an Access database file. Remember, an Access database file is completely separate from the MS Access application software. Thus it is possible to build an alternative UI to read and write to an Access database file. This one just happens to be run using a web browser. Before getting into the nuts and bolts of the UI, it is important to keep in mind that the data and the user interface are distinct aspects of a system. Let’s take a moment to reinforce a key aspect of using a browser to access a database hosted on a server. When using MS Access on a PC to use an Access database (that is, offline), the application software is the front-end and the database file (.mdb) is the back-end. Similarly, when going online, the browser is the front-end and the database file is the back-end. In other words, the relationship hasn’t changed: it is still a front-end/back-end relationship. The front-end connects to the back-end, and the back-end stores the data.

The Database

In the example I’ve chosen for this article, we’ll use a very simple database that consists of three tables: a product table, a category table, and a linking product-category table. The Microsoft Access database contains all the data and table definitions that are relevant to this article. As per best practice, the data is separated into distinct tables. For example, the list of products is stored in the product table, and the list of categories is stored in the category table. The Access database is self-contained, with no links to external tables. Many products can be of any given category, and any given product can belong to more than one category. For example, if the database purpose is to store details of silver antiques, a pair of 19th-Century silver cuff-links can be categorised as both 19th-Century and Gifts for Men. This relationship between the data is shown diagrammatically using Access’s own Relationships option.

That is, there is a many-to-many relationship between products and categories. Hence the need for the link table.

Connection to Database

Before reading or writing a database, a connection must be made to it. Windows hosts have a similar folder hierarchy to a Windows PC, so the location of the files on a Windows server will likely have a path of the form C:inetpubvhostsexample.comhttpdocs. You should be able to extract the value of the path to the root folder of your host using the PHP superglobal $_SERVER["DOCUMENT_ROOT"]. The code needed to connect to the Access database then would be similar to:

<?php
$dbName = $_SERVER["DOCUMENT_ROOT"] . "products\products.mdb";
if (!file_exists($dbName)) {
    die("Could not find database file.");
}
$db = new PDO("odbc:DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=$dbName; Uid=; Pwd=;");

A successful connection will allow SQL commands to be executed from PHP to read or write the database. If, however, you get the error message “PDOException Could not find driver” then it’s likely that the PDO ODBC driver is not installed. Use the phpinfo() function to check your installation for references to PDO. If an entry for PDO ODBC is not present, you will need to ensure your installation includes the PDO extension and ODBC drivers. To do so on Windows, uncomment the line extension=php_pdo_odbc.dll in php.ini, restart Apache, and then try to connect to the database again. With the driver installed, the output from phpinfo()

should include information like this:

PDO_ODBC in phpinfo() output

SQL Commands

The four basic commands used from PHP are the same as those used within MS Access, namely SELECT, UPDATE, INSERT, and DELETE. Thus, the following SQL examples should be easy to follow. Unless, that is, you normally use Access’s Design View, in which you may need an SQL refresher course.

SELECT row(s)

Suppose you need to get the price for a specific product. This is readily achieved using the id of the product to select a single field from the product table.

<?php
$sql  = "SELECT price FROM product";
$sql .= " WHERE id = " . $productId;

$result = $db->query($sql);
$row = $result->fetch();

$productPrice = $row["price"];

After choosing a category from a pull-down list, you can use the category id to query the link table to obtain a list of products that have been assigned to that category.

<?php
$sql  = "SELECT p.name, p.description, p.price";
$sql .= "  FROM product p, product_category pc";
$sql .= " WHERE p.id  = pc.productId";
$sql .= "  AND pc.category_id = " . $categoryId;
$sql .= " ORDER BY name";

$result = $db->query($sql);
while ($row = $result->fetch()) {
    $productName        = $row["name"];
    $productDescription = $row["description"];
    $productPrice       = $row["price"];
}

UPDATE row

Using details provided by an HTML form, you can update a product’s details.

<?php
$sql  = "UPDATE product";
$sql .= "   SET description = " . $db->quote($strDescription) . ",";
$sql .= "       price       =  " . $strPrice . ",";
$sql .= "       sale_status = " . $db->quote($strDescription);
$sql .= " WHERE id = " . $productId;

$db->query($sql);

A troublesome character to be wary of is the apostrophe. The apostrophe can’t be stored in an Access table simply as an apostrophe, rather it has to be escaped by another apostrophe. This ensures that Access knows it is to be stored as an apostrophe and not as the apostrophe that delimits the string. Fortunately, PHP’s PDO library contains a method that prepares strings for storing in a database, quote().

INSERT row

You can add a new product to the product table, using details entered in an HTML form.

<?php
$sql  = "INSERT INTO product";
$sql .= "       (name, description, price, sale_status) ";
$sql .= "VALUES (" . $db->quote($strName) . ", " . $db->quote($strDescription) . ", " . $strPrice . ", " . $db->quote($strStatus) . ")";

$db->query($sql);

DELETE row

If a product has been entered by mistake, or is no longer needed in the database, you can delete it from the product table.

<?php
$sql  = "DELETE";
$sql .= "  FROM product";
$sql .= " WHERE id = " . $productId;

$db->query($sql);

It could be argued that deleting a product is unlikely as it’s better to have a suitable status to indicate a product has been sold or has been archived, etc. Thus, the details would be retained for future reference.

Summary

The above is the bare bones of what is needed to use an Access database from PHP. I hope it shows how little PHP is needed for the essential nitty-gritty of reading and writing an Access database, and how easy it is to understand these basic elements of database interaction using PHP. Reviewing the above SQL, it’s clear there were only three PDO ODBC commands required to allow PHP to access Access, namely, $db = new PDO(), $db->query(), $db->query(), and $db->quote(). Of course, this is the bare minimum. More complicated databases, and more complicated websites, will require more SQL than shown in this article, but you have now the basics to put your own – or your client’s – Access database online using PHP. Comments on this article are closed. Have a question about PHP? Why not ask it on our forums?

Frequently Asked Questions (FAQs) about Using an Access Database with PHP

What are the prerequisites for connecting an Access database with PHP?

To connect an Access database with PHP, you need to have a server with PHP installed and an Access database. You also need to have the appropriate drivers installed on your server to facilitate the connection. The PHP extension php_pdo_odbc.dll must be enabled in your PHP configuration file (php.ini). This extension provides a data object for Microsoft Access databases.

How can I enable the PHP extension for Access databases?

To enable the PHP extension for Access databases, you need to edit your PHP configuration file (php.ini). Locate the line that reads “;extension=php_pdo_odbc.dll” and remove the semicolon at the beginning of the line. Save the file and restart your server for the changes to take effect.

How do I connect to an Access database using PHP?

To connect to an Access database using PHP, you need to use the PDO (PHP Data Objects) extension. This extension provides a consistent interface for accessing databases in PHP. Here is a basic example of how to establish a connection:

<?php
$db = new PDO("odbc:Driver={Microsoft Access Driver (*.mdb, *.accdb)};Dbq=C:\\path\\to\\your\\database.accdb;Uid=Admin");
?>

How can I handle errors when connecting to an Access database with PHP?

When connecting to an Access database with PHP, it’s important to handle potential errors that may occur. You can do this by using the try-catch block in PHP. Here is an example:

<?php
try {
$db = new PDO("odbc:Driver={Microsoft Access Driver (*.mdb, *.accdb)};Dbq=C:\\path\\to\\your\\database.accdb;Uid=Admin");
} catch (PDOException $e) {
echo 'Connection failed: ' . $e->getMessage();
}
?>

How can I execute SQL queries on an Access database using PHP?

To execute SQL queries on an Access database using PHP, you can use the query() or exec() methods of the PDO object. Here is an example of how to execute a SELECT query:

<?php
$result = $db->query('SELECT * FROM table_name');
foreach ($result as $row) {
print_r($row);
}
?>

How can I insert data into an Access database using PHP?

To insert data into an Access database using PHP, you can use the exec() method of the PDO object. Here is an example:

<?php
$db->exec("INSERT INTO table_name (column1, column2) VALUES ('value1', 'value2')");
?>

How can I update data in an Access database using PHP?

To update data in an Access database using PHP, you can use the exec() method of the PDO object. Here is an example:

<?php
$db->exec("UPDATE table_name SET column1 = 'new_value' WHERE condition");
?>

How can I delete data from an Access database using PHP?

To delete data from an Access database using PHP, you can use the exec() method of the PDO object. Here is an example:

<?php
$db->exec("DELETE FROM table_name WHERE condition");
?>

How can I close the connection to an Access database using PHP?

To close the connection to an Access database using PHP, you can simply set the PDO object to null. Here is an example:

<?php
$db = null;
?>

Can I use prepared statements with an Access database in PHP?

Yes, you can use prepared statements with an Access database in PHP. Prepared statements can help prevent SQL injection attacks by automatically escaping special characters. Here is an example of how to use a prepared statement:

<?php
$stmt = $db->prepare("INSERT INTO table_name (column1, column2) VALUES (?, ?)");
$stmt->execute(['value1', 'value2']);
?>