获取 MySQL 数据并通过 PHP 输出为嵌套 JSON

Question

我正在尝试以第三方服务可以使用的格式从我的数据库中导出数据。将数据从 MySQL DB 转换为 JSON 非常简单。我目前正在使用以下内容进行测试，尽管它不是我的完整数据集。

$result = $dblink->query("SELECT * FROM Contracts LIMIT 3");

  $dbdata = array();

  while ( $row = $result->fetch_assoc())  {

    $rowarray['id'] = $row['ContractID'];

    $rowarray['title'] = $row['Service_Name'];

    $rowarray['currency'] = 'GBP';

    $rowarray['amountAppliedFor'] = $row['Sum_Requested'];

    $rowarray['amountAwarded'] = $row['Sum_Approved'];

    $rowarray['awardDate'] = $row['Decision_Date'];

    $rowarray['recipientOrganization'] = $row['Provider'];

    $dbdata[]=$rowarray;

  }

  echo json_encode($dbdata);

这很容易输出：

[ 

   { 

      "id":"147",

      "title":"Whatever",

      "currency":"GBP",

      "amountAppliedFor":"5000.00",

      "amountAwarded":"5000.00",

      "awardDate":"2019-07-14",

      "recipientOrganization":"32"

   },

   { 

      "id":"146",

      "title":"Test",

      "currency":"GBP",

      "amountAppliedFor":"1000.00",

      "amountAwarded":"0.00",

      "awardDate":"2019-07-04",

      "recipientOrganization":"32"

   },

   { 

      "id":"144",

      "title":"Test",

      "currency":"GBP",

      "amountAppliedFor":"15000.00",

      "amountAwarded":"15000.00",

      "awardDate":"0000-00-00",

      "recipientOrganization":"32"

   }

]

很容易。

但我试图创建的模式看起来像：

{

  "grants" : [ {

    "id" : "",

    "recipientOrganization" : [ {

      "id" : "",

      "companyNumber" : "",

      "charityNumber" : "",

      "name" : "",

    } ],

    "fundingOrganization" : [ {

      "id" : "",

      "name" : ""

    } ],

    "title" : "",

    "currency" : "",

    "amountAwarded" : "" }]

}

我不清楚如何使用对象等来构造数据。目前我只是输出平面 JSON。我是不是打算使用类似的东西：

json_encode($dbdata, array(...)); ?

Answer 1

看起来你的结构应该是  while ( $row = $result->fetch_assoc())  {    $rowarray['id'] = $row['ContractID'];    $rowarray['recipientOrganization'] = array(         "id" => $row['id'],         "companyNumber" => $row['companyNumber'],         "charityNumber" => $row['charityNumber'],         "name" => $row['name']    );    $rowarray['fundingOrganization'] = array(         "id" => $row['id'],         "name" => $row['name']    );    $rowarray['title'] = $row['Service_Name'];    $rowarray['currency'] = 'GBP';    //$rowarray['amountAppliedFor'] = $row['Sum_Requested'];    $rowarray['amountAwarded'] = $row['Sum_Approved'];    //$rowarray['awardDate'] = $row['Decision_Date'];    $dbdata['grants'][] = $rowarray;  }我喜欢做的另一个技巧是将 json 放入 phpsandbox 并将其转换为数组以查看结构。