使用PHP查询MDB文件并返回JSON [英] Using PHP to query a MDB file, and return JSON

问题描述

我有一个Microsoft Access数据库，并且尝试使用PHP查询表并输出有效的JSON.我有一个等效的MSSQL数据库代码，我是否想使我的代码做同样的事情，但仅适用于Access数据库.

I have a Microsoft Access Database, and I am trying to query the table using PHP, and output valid JSON. I have an equivalent code for a MSSQL database, am I am trying to make my code do the same thing, but just for the Access database.

这是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文件

Here is what I tried for the MDB file

$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/

I kind of followed this to try to connect to the database: http://phpmaster.com/using-an-access-database-with-php/

当前，这给了我500内部服务器错误.我希望将这样的字符串保存在变量$json

Currently this is giving me a 500 Internal Server Error. I'm expecting a string such as this to be saved in the variable $json

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

有人可以帮我移植上面的MSSQL代码，以便将其与MDB数据库一起使用吗?感谢您的帮助！

Can someone help me port the MSSQL code I have above so I can use it with an MDB database? Thanks for the help!

我正在逐行注释掉行，这在行$data = $db->query($sql);处抛出500错误.我查看了错误日志，发现错误Call to a member function query() on a non-object.我的php.ini文件中已经没有注释行extension=php_pdo_odbc.dll.任何人都知道可能是什么问题吗?

I'm commenting out the lines one by one, and it throws me the 500 error at the line $data = $db->query($sql);. I looked in the error log, and I'm getting the error Call to a member function query() on a non-object. I already have the line extension=php_pdo_odbc.dll uncommented in my php.ini file. Anyone know what the problem could be?

推荐答案

我终于知道了.

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

这篇关于使用PHP查询MDB文件并返回JSON的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！