php - Group nested array by order number

Question

I have a mysql query, like:

$result_orders = $database->query(
  "SELECT order.order_number, order.mail, order.type, 
    order.state, order_item.title, product_variation.sku, address.city, address.address_line
  FROM  order, order_item,product_variation, address
  WHERE order.order_number = order_item.order_id
    AND order_item.title = product_variation.title
    AND profile.id = address.p_id)"
  
)->fetchAll();

which returns stdClass objects, and I could convert it to array using this code:

$array = [];
$i     = 0;

foreach ($result_orders as $row_order) {
    $array[$i] = [
        'order_number' => $row_order->order_number,
        'mail'         => $row_order->mail,
        'order_info'   => [
            'type'    => $row_order->type,
            'state'   => $row_order->state,
            'address' => [
                'city'         => $row_order->city,
                'address_line' => $row_order->address_line,
            ],
        ],
        'order_items' => [
            'sku'   => $row_order->sku,
            'title' => $row_order->title,
        ],
    ];
    ++$i;
}

However, there are many items, so ordrer info and adress are reapeated for every item, so I want to group similar order numbers so the output is something like:

[
  {
    "order_number": "00000",
    "mail": "example",
    "order_info": {
      "type": "example",
      "state": "example"
      "address": {
        "city": "example",
        "address_line": "example"
      }
    },
    "order_items": {
      "sku": "00001",
      "title": "example"
    },
  {
    "sku": "00002",
    "title": "example"
  }
  }
]

I have tried to groupby in the query however it returns an error. Any help? Please.

Answer 1

In your example I cannot see how an order may have multiple items. However, I can assume that in Order object you may have order_items array. Let's see what we can do with the assumed structure to give you an idea of the approach. You can iterate over order items and add array elements accordingly.

<?php

$array = [];
$i     = 0;

foreach ($result_orders as $row_order) {
    $array[$i] = [
        'order_number' => $row_order->order_number,
        'mail'         => $row_order->mail,
        'order_info'   => [
            'type'    => $row_order->type,
            'state'   => $row_order->state,
            'address' => [
                'city'         => $row_order->city,
                'address_line' => $row_order->address_line,
            ],
        ],
        'order_items' => [],
    ];

    foreach ($row_order->order_items as $order_item) {
        $array[$i]['order_items'][] = [
            'sku'   => $order_item->sku,
            'title' => $order_item->title,
        ];
    }

    ++$i;
}