# php access数据库_在PHP中使用Access数据库

php access数据库

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.

Microsoft Access数据库包含与本文相关的所有数据和表定义。 按照最佳实践，数据被分为不同的表。 例如，产品列表存储在product表中，类别列表存储在category表中。 Access数据库是独立的，没有指向外部表的链接。

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: 在读取或写入数据库之前，必须先建立连接。 Windows主机的文件夹层次结构与Windows PC相似，因此Windows服务器上文件的位置可能具有C:inetpubvhostsexample.comhttpdocs形式的路径。 您应该能够使用PHP超全局性$_SERVER["DOCUMENT_ROOT"]提取主机根目录的路径值。 然后，连接到Access数据库所需的代码将类似于：

<?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. 成功的连接将允许从PHP执行SQL命令以读取或写入数据库。 但是，如果收到错误消息“ PDOException无法找到驱动程序”，则可能未安装PDO ODBC驱动程序。 使用phpinfo()函数检查您的安装中是否有对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. 如果不存在用于PDO ODBC的条目，则需要确保您的安装包括PDO扩展名和ODBC驱动程序。 要在Windows上执行此操作，请取消注释php.ini extension=php_pdo_odbc.dll行，重新启动Apache，然后尝试再次连接到数据库。 With the driver installed, the output from phpinfo() should include information like this: 安装驱动程序后， phpinfo()的输出应包含以下信息： ## SQL命令 (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. PHP中使用的四个基本命令与MS Access中使用的四个命令相同，即SELECTUPDATEINSERTDELETE 。 因此，以下SQL示例应易于理解。 除非是，除非您通常使用Access的设计视图，否则您可能需要其中SQL复习课程。 ### SELECT行 (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. 假设您需要获取特定产品的价格。 使用产品的ID从product表中选择单个字段即可轻松实现。 <?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. 从下拉列表中选择类别后，可以使用类别ID查询链接表，以获得已分配给该类别的产品的列表。 <?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行 (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 . ",";

php access数据库

04-12 46
04-07 5003
01-22 167
05-22 56
02-02 3889
09-18
10-31 1072
07-18 658
01-03 4032

• 非常没帮助
• 没帮助
• 一般
• 有帮助
• 非常有帮助