php - PHP 中的嵌套 JSON 输出

Question

我正在为 iOS 应用程序构建 API，并尝试将 mySQL 数据转换为 JSON 字符串进行处理。所需的输出需要顶级订单详细信息，例如客户名称和地址，然后是订购的产品子数组。

我需要的两个表中有相当多的字段，我希望拥有所有字段。我已经构建了一个脚本来执行此操作，但输出在 JSON 验证器中报告为格式错误。

这是我的代码:

$orders = $db->get_results("SELECT ords.serial as ID, cust.title, cust.name, cust.surname, cust.address, cust.address2, cust.town, cust.county, cust.postcode, cust.phone, cust.height, cust.weight, cust.houseType, cust.parking, cust.access, ords.furnitureRem, ords.furnitureRemDetails, ords.comments FROM orders as ords JOIN customers as cust ON ords.customerid = cust.serial JOIN order_detail as odeet ON ords.serial = odeet.orderid JOIN `user` as us ON us.id = ords.user_id  WHERE us.id='".$_REQUEST['userID']."' GROUP BY ords.serial ORDER BY cust.serial DESC"); 

$json_response = array(); //Create an array
foreach ( $orders as $row )
{
    $row_array = array();
    $row_array[] = $row;        
    $ord_id = $row->ID;  

    $orders2 = $db->get_results("SELECT * FROM order_detail as ord
    JOIN products as prod ON ord.productid = prod.id
    WHERE ord.orderid = ".$ord_id);
    foreach ( $orders2 as $vorder2 ) {
    {
        $row_array['products'][] = $vorder2;
    }
    array_push($json_response, $row_array); //push the values in the array
}
echo json_encode($json_response);
}

当前的输出是这样的:

根据要求，这是 RAW 输出:

[{"0":{"ID":"756","title":"Mr","name”:”John”,”surname”:”Smith”,”address”:”Address Line 1”,”address2”:”Address Line 2”,”town”:”Town","county”:”County”,”postcode”:”PO57 8DE”,”phone":"0777777777777”,”height":"6ft","weight":"14st","houseType":"Flat","parking":"none","access":"No problems","furnitureRem":"0","furnitureRemDetails":null,"comments":null},"products":[{"orderid":"756","productid":"2","price":"6500","status":"1","id":"2","type":"Chair","style":"Surrey","action":"Single Motor 2 Way Tilt in Space","weightCap":"35st","height":"14\"","width":"24\"","internalWidth":null,"length":null,"internalLength":null,"depth":"16\"","fabric":"Leather","fabricCode":"LR43","backStyle":"Waterfall","cushionFabric":"Leather","cushionFabricCode":"LR43","backHeight":"32\"","armHeight":"7\"","seatingOptions":"Memory Foam","armOption":"Scrolled","backupBattery":"2x Backup","headRoll":"1","headCover":"1","seatCover":"0","armCover":"0","armCap":"1","pocket":"Left","loop":"Left","antimacassar":"0","freedom":"1","heatMassage":"0","castorsGlides":"Castors","footPlate":"0","seatDepthAdj":"0","doorFrame":null,"additionalCover":"0","scViscoform":"0","scPommelViscoform":"0","scLiquiform":"0","scPommelLiquiform":"0","scCelliform":"0","scAirform":"0","scDynaform":"0","bsWaterfall":"0","bsComfortLateral":"0","bsProfileWaterfall":"0","bsProfileComfortLateral":"0","bsSupportLateral":"0","bsProfileSupportLateral":"0","hsLargeProfileHeadrest":"0","hsSmallHeadPillow":"0","hsSmallProfileHeadPillow":"0","hsMidlineHeadrest":"0","woodColour":"0","frameColour":null,"baseColour":null,"headFootBoard":null,"mattressType":null,"productHeightLowest":null,"productHeightHighest":null,"liftingPoles":null,"grabRails":null,"readingLight":"0","existingBedHeight":null,"legHeight":null,"drawerOptions":null,"hoistCutouts":"0","cotSides":null,"liftingPole":"0","sideRetention":"0","linked":"0","rrp":null,"stock":"0"}]}][{"0":{"ID":"756","title":"Mr","name":"John","surname":"Smith","address":"Address Line 1","address2":"Address Line 2","town":"Town","county":"County","postcode":"PO57 8DE","phone":"0777777777777","height":"6ft","weight":"14st","houseType":"Flat","parking":"none","access":"No problems","furnitureRem":"0","furnitureRemDetails":null,"comments":null},"products":[{"orderid":"756","productid":"2","price":"6500","status":"1","id":"2","type":"Chair","style":"Surrey","action":"Single Motor 2 Way Tilt in Space","weightCap":"35st","height":"14\"","width":"24\"","internalWidth":null,"length":null,"internalLength":null,"depth":"16\"","fabric":"Leather","fabricCode":"LR43","backStyle":"Waterfall","cushionFabric":"Leather","cushionFabricCode":"LR43","backHeight":"32\"","armHeight":"7\"","seatingOptions":"Memory Foam","armOption":"Scrolled","backupBattery":"2x Backup","headRoll":"1","headCover":"1","seatCover":"0","armCover":"0","armCap":"1","pocket":"Left","loop":"Left","antimacassar":"0","freedom":"1","heatMassage":"0","castorsGlides":"Castors","footPlate":"0","seatDepthAdj":"0","doorFrame":null,"additionalCover":"0","scViscoform":"0","scPommelViscoform":"0","scLiquiform":"0","scPommelLiquiform":"0","scCelliform":"0","scAirform":"0","scDynaform":"0","bsWaterfall":"0","bsComfortLateral":"0","bsProfileWaterfall":"0","bsProfileComfortLateral":"0","bsSupportLateral":"0","bsProfileSupportLateral":"0","hsLargeProfileHeadrest":"0","hsSmallHeadPillow":"0","hsSmallProfileHeadPillow":"0","hsMidlineHeadrest":"0","woodColour":"0","frameColour":null,"baseColour":null,"headFootBoard":null,"mattressType":null,"productHeightLowest":null,"productHeightHighest":null,"liftingPoles":null,"grabRails":null,"readingLight":"0","existingBedHeight":null,"legHeight":null,"drawerOptions":null,"hoistCutouts":"0","cotSides":null,"liftingPole":"0","sideRetention":"0","linked":"0","rrp":null,"stock":"0"}]},{"0":{"ID":"756",""title":"Mr","name":"John","sur...

我只是被这个问题难住了。或者我是否需要逐项列出数据集中的每个字段才能输出干净的 JSON？

Answer 1

问题并不清楚，但我认为你需要这样的东西:

[
  {
    "ID": 123,
    ...
    "products": [
      {
        "foo": "bar"
      },
      {
        "foo": "baz"
      }
    ]
  },
  {
    ...
  }
]

如果这是正确的，您需要稍微重构一下您的代码。首先，您必须将 products 数组放入 $row_array 数组中，该数组应该是 $row，而不是包含它。由于 $row 似乎是一个具有公共(public)属性的对象，因此您可以将 $row 转换为数组并将其分配给 $row_array:

$row_array = (array) $row;

如您所见，不需要包装 $row 的 $row_array，您的 $row_array 应该是您的 $行。

最后，当您必须仅将一个元素推送到数组末尾时，请避免使用 array_push():

$json_response[] = $row_array;

比 array_push() 更快。​​

您需要的最后一段代码就是这样:

$orders = $db->get_results(("SELECT ords.serial as ID, cust.title, cust.name, cust.surname, cust.address, cust.address2, cust.town, cust.county, cust.postcode, cust.phone, cust.height, cust.weight, cust.houseType, cust.parking, cust.access, ords.furnitureRem, ords.furnitureRemDetails, ords.comments FROM orders as ords JOIN customers as cust ON ords.customerid = cust.serial JOIN order_detail as odeet ON ords.serial = odeet.orderid JOIN `user` as us ON us.id = ords.user_id  WHERE us.id='".$_REQUEST['userID']."' GROUP BY ords.serial ORDER BY cust.serial DESC");

$json_response = array();
foreach ( $orders as $row ) {
    $row_array = (array) $row;
    $ord_id = $row->ID;

    $orders2 = $db->get_results("SELECT * FROM order_detail as ord
        JOIN products as prod ON ord.productid = prod.id
        WHERE ord.orderid = ".$ord_id);
    foreach ( $orders2 as $vorder2 ) {
        $row_array['products'][] = $vorder2;
    }
    $json_response[] = $row_array;
}
echo json_encode($json_response);