How display json records from database sorted by date and group by date

Question

i need to display records from my database mysql grouped by date to JSON in PHP. i have table :

--- transaction ---
- id
- date (Y-m-d H:i:s)
- user
- price
- product_id
- quantity
-------------------

And i wish display to JSON like this

{
  "status": {
    "code": 200,
    "message": "Success"
  },
  "data": [
    {
      "date": "1 June 2020",
      "detail": [
        {
          "id": 1,
          "date_trx": "01-06-2020 15:22:54",
          "user": "User A",
          "price": 500,
          "product_id": "Product A",
          "quantity": 2
        },
        {
          "id": 1,
          "date_trx": "01-06-2020 17:22:54",
          "user": "User B",
          "price": 500,
          "product_id": "Product H",
          "quantity": 2
        }
      ]
    },
    {
      "date": "2 June 2020",
      "detail": [
        {
          "id": 1,
          "date_trx": "02-06-2020 15:22:54",
          "user": "User A",
          "price": 500,
          "product_id": "Product A",
          "quantity": 2
        }
      ]
    }
  ]
}

i have to tried some code from google or stackoverflow, but not like my wish

** EDIT **

this is my code have tried

$search = $call->query("SELECT * FROM transaction WHERE ORDER BY date DESC");
            if($search->num_rows > 0){
                $date = null;
                $array = [];
                
                while($record = $search->fetch_assoc()) {
                    $currentDate = date('Y-m-d', strtotime($record['tgl']));
                    if ($currentDate != $date) {
                        // echo "<h1>$currentDate</h1>";
                        $tgl = $currentDate;
                    }
                    $date = $currentDate;
                    // echo $record['rid']."<br>";

                    if(is_array($array) && $array['date'] != $tgl){
                        $arr = array(
                            'date' => $tgl,
                            'detail' => array(
                                'id' => $record['rid'],
                                'date_trx' => $record['tgl'],
                                'user' => $record['user'],
                                'price' => $record['price'],
                                'product_id' => $record['product'],
                                'quantity' => $record['quantity']
                            )
                        );
                        array_push($array, $arr);
                    }
                }
                $output = ['result' => true, 'data' => $array];
            }

And this is respon from my code :

{
    "result": true,
    "data": [
        {
            "date": "2020-07-05",
            "data": {
                "id": "9924243",
                "date_trx": "2020-07-05 07:03:58",
                "user": "User A",
                ...
            }
        },
        {
            "date": "2020-06-01",
            "data": {
                "id": "9924243",
                "date_trx": "2020-06-01 07:03:58",
                "user": "User A",
                ...
            }
        },
        {
            "date": "2020-06-01",
            "data": {
                "id": "9924243",
                "date_trx": "2020-06-01 07:03:58",
                "user": "User A",
                ...
            }
        }
    ]
}

The respon from my code is not like my wishes, maybe you all can help me for wishes response

Answer 1

Iterate over results, add to your result a basic structure with 'date' and 'detail' under the date key if it doesnt exist and just add every row to correct 'detail'.

<?php

$db = new mysqli("localhost", "zz", "zz", "zz");

$query = "SELECT * FROM transaction ORDER BY date ASC";
$result = $db->query($query);

$data = [];
while($row = $result->fetch_assoc()) {
  $date = new DateTime($row['tgl']);

  if (!isset($data[$date->format('Y-m-d')])) {
    $data[$date->format('Y-m-d')] = [
      'date' => $date->format('j F Y'),
      'detail' => [],
    ];
  }

  $data[$date->format('Y-m-d')]['detail'][] = [
    'id' => $row['rid'],
    'date_trx' => $date->format('d-m-Y H:i:s'),
    'user' => $row['user'],
    'price' => $row['price'],
    'product_id' => $row['product'],
    'quantity' => $row['quantity'],
  ];
}

$data = array_values($data);
var_dump(json_encode($data, JSON_PRETTY_PRINT));

Result:

string(892) "[
    {
        "date": "1 June 2020",
        "detail": [
            {
                "id": "1",
                "date_trx": "01-06-2020 15:22:54",
                "user": "User A",
                "price": "500",
                "product_id": "Product A",
                "quantity": "2"
            },
            {
                "id": "2",
                "date_trx": "01-06-2020 17:22:54",
                "user": "User B",
                "price": "500",
                "product_id": "Product H",
                "quantity": "2"
            }
        ]
    },
    {
        "date": "2 June 2020",
        "detail": [
            {
                "id": "3",
                "date_trx": "02-06-2020 15:22:54",
                "user": "User A",
                "price": "500",
                "product_id": "Product A",
                "quantity": "2"
            }
        ]
    }
]"