PHP Group PDO or JSON results by 3 fields and output

I’m trying to group my results in an array by the 3 fields delivery_date, van_no and drop_no, this is what i have so far:

// Gets all orders and sorts by delivery date, van and drop number
    $getorders = $conn->prepare("SELECT * FROM orders INNER JOIN customers ON orders.customer_id = customers.customer_id INNER JOIN addresses on orders.address_id = addresses.address_id ORDER BY delivery_date, van_no, drop_no");
    $getorders->execute();

    $result = $getorders->fetchAll(PDO::FETCH_ASSOC);    

    var_dump($result);

which gives me an output like:

array (size=6)
  0 => 
    array (size=8)
      'orders_id' => string '6' (length=1)
      'customer_id' => string '1' (length=1)
      'address_id' => string '1' (length=1)
      'van_no' => string '1' (length=1)
      'drop_no' => string '0' (length=1)
      'delivery_date' => string '2020-08-25' (length=10)
      'customer_name' => string 'One' (length=3)
      'address_postcode' => string 'b1' (length=2)
  1 => 
    array (size=8)
      'orders_id' => string '1' (length=1)
      'customer_id' => string '1' (length=1)
      'address_id' => string '1' (length=1)
      'van_no' => string '1' (length=1)
      'drop_no' => string '1' (length=1)
      'delivery_date' => string '2020-08-25' (length=10)
      'customer_name' => string 'One' (length=3)
      'address_postcode' => string 'b1' (length=2)
  2 => 
    array (size=8)
      'orders_id' => string '3' (length=1)
      'customer_id' => string '2' (length=1)
      'address_id' => string '2' (length=1)
      'van_no' => string '1' (length=1)
      'drop_no' => string '2' (length=1)
      'delivery_date' => string '2020-08-25' (length=10)
      'customer_name' => string 'Two' (length=3)
      'address_postcode' => string 'b2' (length=2)

if i change this to PDO::FETCH_GROUP it just seems to double every field for each array and if i use a GROUP BY in the query it just gives me a single result for each. Ive tried:

 foreach($data->delivery_date as $values)
 {
      echo $values->van_no . "n";
 }

but just get Notice: Trying to get property ‘delivery_date’ of non-object, Ive tried encode to JSON but get stuck and the same place trying to group.

Basically im completely lost now and everything i google around grouping PDO arrays or JSON arrays ends up not doing anything or throwing an error like above.

EDIT:

Id like the output to look like this at least :

[
    "delivery_date": "2020-08-25"
    {
        "van_no": "1"
            {
                "orders_id": "6",
                "customer_id": "1",
                "address_id": "1",
                "van_no": "1",
                "drop_no": "0",
                "delivery_date": "2020-08-25",
                "customer_name": "One",
                "address_postcode": "b1"
            },
            {
                "orders_id": "1",
                "customer_id": "1",
                "address_id": "1",
                "van_no": "1",
                "drop_no": "1",
                "delivery_date": "2020-08-25",
                "customer_name": "One",
                "address_postcode": "b1"
            },
            {
                "orders_id": "3",
                "customer_id": "2",
                "address_id": "2",
                "van_no": "1",
                "drop_no": "2",
                "delivery_date": "2020-08-25",
                "customer_name": "Two",
                "address_postcode": "b2"
            }

    }

Answer

The output…

"delivery_date": "2020-08-25"
    {

isn’t achievable, so this code will create the output is something which has a list of deliveries and vans…

$output = [];
while( $result = $getorders->fetch(PDO::FETCH_ASSOC))  {
    if ( !isset($output[$result['delivery_date']]) )    {
        $output[$result['delivery_date']]['date'] = $result['delivery_date'];
    }
    if ( !isset($output[$result['delivery_date']][$result['van_no']]) ) {
        $output[$result['delivery_date']][$result['van_no']]['van'] 
            = $result['van_no'];
    }
    $output[$result['delivery_date']]
            [$result['van_no']][] = $result;
}
echo json_encode(array_values($output), JSON_PRETTY_PRINT);

which will give you something like…

[
    {
        "date": "2020-08-25",
        "1": {
            "van": 1,
            "0": {
                "delivery_date": "2020-08-25",
                "van_no": 1,

Advertisement

Answer