时间戳

首页 > 解决方案 > 将 oci_fetch_assoc 数据转换为 json 时出现问题

php - 将 oci_fetch_assoc 数据转换为 json 时出现问题

问题描述

我正在尝试从 Oracle 表中获取数据并转换为 JSON 对象，以便可以在 Jquery Datatable 上读取它。这是我的 PHP 代码 -

    <?php

   $db = "(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=hostname)(PORT=1521)(SEND_BUF_SIZE=)(RECV_BUF_SIZE=))(LOAD_BALANCE=yes))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=servicename)))" ;

    if($c = OCILogon("test", "test", $db))
    {

        $rowfetch = oci_parse($c, "select * from table" );
        oci_execute($rowfetch);
    while($row=oci_fetch_assoc($rowfetch))
            {
                $arr['data'][]= $row;
                $senddata= json_encode($arr, JSON_UNESCAPED_SLASHES|JSON_UNESCAPED_UNICODE);
                echo $senddata;
}
}
    else
    {
        $err = OCIError();
        echo "Connection failed." . $err[text];
    }


OCILogoff($c);
?>

我收到这样的 JSON 响应 -

{"data":[{"SCHEMA":"TEST","TABLE_NAME":"TEST_T","CUSTOMFIELD10":"CREATING","CUSTOMFIELD5":"NIK","CUSTOMFIELD9":"NIK","LAST_UPD_TIME" :"27-JUN-18"}]}{"data":[{"SCHEMA":"TEST","TABLE_NAME":"TEST_T","CUSTOMFIELD10":"CREATING","CUSTOMFIELD5":"NIK", "CUSTOMFIELD9":"NIK","LAST_UPD_TIME":"27-JUN-18"},{"SCHEMA":"TEST","TABLE_NAME":"ART_CM3_T","CUSTOMFIELD10":"平均 DWP","CUSTOMFIELD5" :null,"CUSTOMFIELD9":"BILLING","LAST_UPD_TIME":"05-FEB-19"}]}{"data":[{"SCHEMA":"TEST","TABLE_NAME":"TEST_T","CUSTOMFIELD10":"CREATING","CUSTOMFIELD5":"NIK","CUSTOMFIELD9":"NIK","LAST_UPD_TIME":"27-JUN-18"},{" SCHEMA":"TEST","TABLE_NAME":"ART_CM3_T","CUSTOMFIELD10":"平均 DWP","CUSTOMFIELD5":null,"CUSTOMFIELD9":"BILLING","LAST_UPD_TIME":"05-FEB-19"} ,{"SCHEMA":"TEST","TABLE_NAME":"ART_T","CUSTOMFIELD10":"CREATING","CUSTOMFIELD5":"NIK","CUSTOMFIELD9":"NIK","LAST_UPD_TIME":"09-FEB -19"}]}{"数据":[{"SCHEMA":"TEST","TABLE_NAME":"TEST_T","CUSTOMFIELD10":"CREATING","CUSTOMFIELD5":"NIK","CUSTOMFIELD9":"NIK","LAST_UPD_TIME":"27-JUN-18"},{"SCHEMA":"TEST","TABLE_NAME":" ART_CM3_T","CUSTOMFIELD10":"Average DWP","CUSTOMFIELD5":null,"CUSTOMFIELD9":"BILLING","LAST_UPD_TIME":"05-FEB-19"},{"SCHEMA":"TEST","TABLE_NAME ":"ART_T","CUSTOMFIELD10":"CREATING","CUSTOMFIELD5":"NIK","CUSTOMFIELD9":"NIK","LAST_UPD_TIME":"09-FEB-19"},{"SCHEMA":"TEST ","TABLE_NAME":"ART_CUST","CUSTOMFIELD10":"CREATING","CUSTOMFIELD5":"NIK","CUSTOMFIELD9":"NIK","LAST_UPD_TIME":"09-FEB-19"}]}

但这不是 Datatable 可以读取和接受的正确格式。对于 Jquery 数据表，JSON 响应应该是这样的 -

{“数据”：[[“Tiger Nixon”，“系统架构师”，“爱丁堡”，“5421”，“2011/04/25”，“320,800 美元”]，[“Garrett Winters”，“会计”，“东京”、“8422”、“2011/07/25”、“170,750 美元”]、[“赫罗德·钱德勒”、“销售助理”、“旧金山”、“9608”、“2012 年 8 月 6 日”、“137,500 美元” ]、[“Rhona Davidson”、“集成专家”、“东京”、“6200”、“2010/10/14”、“$327,900”]、[“科琳·赫斯特”、“Javascript 开发人员”、“旧金山”、“2360”、“2009 年 9 月 15 日”、“205,500 美元”]、[“海莉·肯尼迪”、“高级营销设计师”、“伦敦”、“ 3597”、“2012/12/18”、“$313,500”]、[“Tatyana Fitzpatrick”、“区域总监”、“伦敦”、“1965”、“2010/03/17”、“$385,750”]]}]，[“Haley Kennedy”，“高级营销设计师”，“伦敦”，“3597”，“2012/12/18”，“$313,500”]，[“Tatyana Fitzpatrick”，“区域总监”，“伦敦”， “1965 年”、“2010 年 3 月 17 日”、“385,750 美元”] ] }]，[“Haley Kennedy”，“高级营销设计师”，“伦敦”，“3597”，“2012/12/18”，“$313,500”]，[“Tatyana Fitzpatrick”，“区域总监”，“伦敦”， “1965 年”、“2010 年 3 月 17 日”、“385,750 美元”] ] }750"]]}750"]]}

我在这里做错了什么？如何将此 JSON 格式更改为所需的 Datatable 格式？

标签： phpjson

解决方案

在附加返回记录后的 while 循环中，它被回显。

除了返回错误的格式外，还会返回重复的记录。

您可以在对它们进行编码后输出单个行，并输出它们所包含的编码 JSON 数组。

echo '{"data": [';

$row = oci_fetch_row($rowfetch);
while ($row) {
    $senddata = json_encode($row, JSON_UNESCAPED_SLASHES|JSON_UNESCAPED_UNICODE);

    echo $senddata;

    $row = oci_fetch_row($rowfetch);

    if ($row === FALSE) break;

    echo ',';
}

echo ']}';
// Remember to free the statement and close the connection.

您还可以构建编码记录数组并完全输出。

$records = [];

oci_fetch_all($rowfetch, &$records, null, null, OCI_FETCHSTATEMENT_BY_ROW + OCI_NUM);

$resp = ["data" => $records];

echo json_encode($resp, JSON_UNESCAPED_SLASHES|JSON_UNESCAPED_UNICODE);
// Remember to free the statement and close the connection.

推荐阅读