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

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.

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

Here is the MSSQL code

这是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);

Here is what I tried for the MDB file

这是我为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);

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

我有点跟着这个尝试连接到数据库:http://phpmaster.com/using-an-access-database-with-php/

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

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

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

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

有人可以帮助我移植我上面的MSSQL代码,这样我就可以在MDB数据库中使用它吗?谢谢您的帮助!

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

编辑:我正在逐行评论这些行,并在$ data = $ db-> query($ sql);行中抛出500错误。我查看了错误日志,我收到错误调用非对象上的成员函数query()。我已经在我的php.ini文件中取消注释了行extension = php_pdo_odbc.dll。谁知道问题可能是什么?

4 个解决方案

#1

You only need 1 loop, fetchAll is your iterable friend:

你只需要1个循环,fetchAll是你的可迭代朋友:

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

#2

odbc_connect doesn't return an object, it returns a resource. see (http://php.net/manual/en/function.odbc-connect.php) so you would need to do something like this.

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

and then you can iterate over results..

然后你可以迭代结果..

#3

I finally figured it out.

我终于弄明白了。

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

#4

I use this code to get results from an ODBC query into a JSON array:

我使用此代码将ODBC查询的结果转换为JSON数组:

$response = null;
$conn = null;

try {
  $odbc_name = 'myODBC'; //<-ODBC connectyion name as is in the Windows "Data Sources (ODBC) administrator"

  $sql_query = "SELECT * FROM table;";

  $conn = odbc_connect($odbc_name, 'user', 'pass');
  $result = odbc_exec($conn, $sql_query);

  //this will show all results:
  //echo odbc_result_all($result);

  //this will fetch row by row and allows to change column name, format, etc:     
  while( $row = odbc_fetch_array($result) ) { 
     $json['cod_sistema'] = $row['cod_sistema'];
     $json['sistema'] = $row['sistema'];
     $json['cod_subsistema'] = $row['cod_subsistema'];
     $json['sub_sistema'] = $row['sub_sistema'];
     $json['cod_funcion'] = $row['cod_funcion'];
     $json['funcion'] = $row['funcion'];
     $json['func_desc_abrev'] = $row['desc_abreviada'];
     $json['cod_tipo_funcion'] = $row['cod_tipo_funcion'];

     $response[] = array('funcionalidad' => $json);
  }

  odbc_free_result($result); //<- Release used resources

} catch (Exception $e) {
   $response = array('resultado' => 'err', 'detalle' => $e->getMessage());
   echo 'ERROR: ',  $e->getMessage(), "\n";
}
odbc_close($conn);
return $response;

And finnally encoding the response in JSON format:

并以JSON格式对响应进行最终编码:

echo json_encode($response);

#1