使用PHP查询MDB文件，并返回JSON

Question

我有一个Microsoft数据库，我正在尝试使用PHP查询表，并输出有效的JSON。对于MSSQL数据库，我有一个等价的代码，我是否试图让我的代码做同样的事情，但只用于Access数据库。

以下是MSSQL代码

$myServer = "server";
$myDB = "db";
$conn = sqlsrv_connect ($myServer, array('Database'=>$myDB));

$sql = "SELECT *
        FROM db.dbo.table";

$data = sqlsrv_query ($conn, $sql);

$result = array();   

do {
    while ($row = sqlsrv_fetch_array ($data, SQLSRV_FETCH_ASSOC)) {
        $result[] = $row;   
    }
} while (sqlsrv_next_result($data));

$json = json_encode ($result);

sqlsrv_free_stmt ($data);
sqlsrv_close ($conn); 

下面是我为MDB文件所做的尝试

$dbName = "/filename.mdb";

if (!file_exists($dbName)) {
    die("Could not find database file.");
}

$db = odbc_connect("Driver={Microsoft Access Driver (*.mdb)};Dbq=$dbName", $user, $password);

$sql = "SELECT *
        FROM cemetery";

$data = $db->query($sql); // I'm getting an error here
$result = array();   

// Not sure what do do for this part...
do {
    while ($row = fetch($data, SQLSRV_FETCH_ASSOC)) {
        $result[] = $row;   
    }
} while (sqlsrv_next_result($data));

$json = json_encode ($result);

我在某种程度上遵循这一点，尝试连接到数据库：http://phpmaster.com/using-an-access-database-with-php/

目前，这给了我一个500内部服务器错误。我期望这样的字符串保存在变量$json中

[
    {
        "col1":"col value",
        "col2":"col value",
        "col3":"col value",
    },
    {
        "col1":"col value",
        "col2":"col value",
        "col3":"col value",
    },
    {
        etc...
    }
]

有人能帮我移植我上面的MSSQL代码吗?这样我就可以在MDB数据库中使用它了吗？谢谢你的帮助！

编辑:我一个接一个地注释掉这些行，它将我抛到行$data = $db->query($sql);上的500个错误。我查看了错误日志，得到了错误Call to a member function query() on a non-object。我已经在我的extension=php_pdo_odbc.dll文件中删除了行php.ini。有人知道问题出在哪里吗？

Answer 1

我终于想出来了。

<?php
// Location of database. For some reason I could only get it to work in
// the same location as the site. It's probably an easy fix though
$dbName = "dbName.mdb";
$tName = "table";

// Throws an error if the database cannot be found
if (!file_exists($dbName)) {
    die("Could not find database file.");
}

// Connects to the database
// Assumes there is no username or password
$conn = odbc_connect("Driver={Microsoft Access Driver (*.mdb)};Dbq=$dbName", '', '');

// This is the query
// You have to have each column you select in the format tableName.[ColumnName]
$sql = "SELECT $tName.[ColumnOne], $tName.[ColumnTwo], etc...
        FROM $dbName.$tName";

// Runs the query above in the table
$rs = odbc_exec($conn, $sql);

// This message is displayed if the query has an error in it 
if (!$rs) {
    exit("There is an error in the SQL!");
}

$data = array();
$i = 0;

// Grabs all the rows, saves it in $data
while( $row = odbc_fetch_array($rs) ) {
    $data[$i] = $row;
    $i++;
} 

odbc_close($conn); // Closes the connection
$json = json_encode($data); // Generates the JSON, saves it in a variable
?>

Answer 2

您只需要一个循环，fetchAll是您可迭代的朋友：

while ($row = $data->fetchAll(SQLSRV_FETCH_ASSOC)) {
    $result[] = $row;   
}

Answer 3

odbc_connect不返回对象，而是返回资源。请参见(http://php.net/manual/en/function.odbc-connect.php)，因此您需要这样做。

 $db = odbc_connect("Driver={Microsoft Access Driver (*.mdb)};Dbq=$dbName", $user, $password);
 $oexec = obdc_exec($db,$sql);
  $result[] = odbc_fetch_array($oexec);

然后你可以迭代结果。

另见：

http://www.php.net/manual/en/function.odbc-fetch-array.php http://www.php.net/manual/en/function.odbc-exec.php