获取 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 并将其转换为数组以查看结构。