PHP连接Access

文章目录

1. 1. PHP连接Access数据库
   1. 1.1. 环境
   2. 1.2. PHP需要的扩展
   3. 1.3. SQL命令
      1. 1.3.1. 连接Access
      2. 1.3.2. SELECT
      3. 1.3.3. UPDATE
      4. 1.3.4. INSERT
      5. 1.3.5. DELETE

PHP连接Access数据库

工作中需要中控的联网考勤打卡机将考勤数据同步到公司的OA系统中，但中控的打卡机只能通过网络将数据发送到其自己的考勤系统中，而其考勤系统用的数据库是Microsoft Access，所以才有了这个需求。

环境

• 必须是Windows系统
• LAMP或LNMP必须是32位版的，否则报错连接不上

PHP需要的扩展

通过运行phpinfo()查看，应该包含以下扩展，否则修改php.ini文件

SQL命令

连接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=;");

SELECT

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

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

$productPrice = $row["price"];

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

<?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);

INSERT

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

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

$db->query($sql);