doude4201 2016-07-26 09:01
浏览 65
已采纳

PHP中的嵌套JSON输出

I'm building an API for an iOS app and trying to get the mySQL data into a JSON string for processing. The desired output would require top level order details like customer name and address and then a sub array of products ordered.

There are quite a few fields in both tables i need and it i'm hoping to have all fields. I've built a script to do this, but the output is reporting as malformed in a JSON validator.

Here's my code:

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

The current output is like this:

enter image description here

Here's the RAW output, by request:

[{"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...

I'm just stumped with this one. Or am I going to need to itemise each field in the dataset for this to output a clean JSON?

  • 写回答

1条回答 默认 最新

  • doulang9953 2016-07-26 09:26
    关注

    It's not that clear from the question, but I think you need something like this:

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

    If this is right, you need to refactor your code a little bit. First of all, you have to put the products array inside the $row_array array, which should be $row, not contain it. Since $row seems to be an object with public properties, you can just cast $row to array and assign it to $row_array:

    $row_array = (array) $row;
    

    As you can see, no need for a $row_array that wraps the $row, your $row_array should be your $row.

    In the end, avoid using array_push() when you have to push only one element at the end of the array:

    $json_response[] = $row_array;
    

    Is faster than array_push().

    The final piece of code you need is just this:

    $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);
    
    本回答被题主选为最佳回答 , 对您是否有帮助呢?
    评论

报告相同问题?

悬赏问题

  • ¥15 ubuntu安装gdal后java读取tif文件报错
  • ¥15 请问lammps怎么做两种金属连接的原子浓度分布图
  • ¥15 求jacquard数据集
  • ¥15 w10部分软件不能联网
  • ¥15 关于安装hbase的问题(操作系统-windows)
  • ¥15 novnc连接pve虚拟机报错安全协议不支持262
  • ¥15 设备精度0.03给多少公差能达到CPK1.33
  • ¥15 qt+ffmpeg报错non-existing PPS 0 referenced
  • ¥15 FOC simulink
  • ¥50 MacOS 使用虚拟机安装k8s