编写MySQL查询以在PHP中获取数组并输出为JSON

Question

I have the following MySQL Table:

+----+------+-------+--------+-------+-------+
| id | col1 | col2  | col3   | col4  | col5  |
+----+------+-------+--------+-------+-------+
| 1  | a    | Cat A | Joe    | data1 | data2 |
+----+------+-------+--------+-------+-------+
| 2  | a    | Cat A | Carl   | data3 | data4 |
+----+------+-------+--------+-------+-------+
| 3  | b    | Cat B | Mario  | data5 | daa6  |
+----+------+-------+--------+-------+-------+
| 4  | c    | Cat C | Philip | data7 | data8 |
+----+------+-------+--------+-------+-------+

I am building a REST Endpoint and want to output final Result in the following JSON format:

{
  "json_data": {
    "a": [
      {
        "Cat A": [{
            col3:Joe,
            Col4: data1,
            col5: data2
        },{
            col3:Carl,
            Col4: data3,
            col5: data4
        }
    ],
    "b": [
      {
        "Cat B": [{
            col3:Mario,
            Col4: data5,
            col5: data6
        }]
      }
    ],
    "c": [
      {
        "Cat C": [{
            col3:Philip,
            Col4: data7,
            col5: data8
        }]
      }
    ]
  }
}

As you can see from the table and the final JSON output, I want to first group the results by col1 then based on col1 I will group by col2 and eventually show the remaining data.

I am using PHP to create this with the following code:

MYSQL Query: $query = $wpdb->get_results('SELECT * FROM table ORDER BY col2');

PHP Loops & Array:

foreach ($query as $allData) {

        $arrayData2[$allData->col2] = array(
            'col3' => $allData->col3,
            'col4' => $allData->col4,
            'col5' => $allData->col5
        );
    }


    foreach ($query as $col2) {
        $arrayData1[$col2->col2] = array(
            $arrayData2
        );
    }

    foreach ($query as $col1) {
        $array1[$col1->col1] = array(
            $arrayData1,
        );
    }

    return array(
        'json_data' => $array1,
    );

The result is not as expected, can someone please guide me to the error I am doing wrong in order to achieve my expected result.

Answer 1

You can achieve your desire result with simple one for loop:

$resultArray = [];
foreach ($query as $row) {
    $resultArray[$row->col1][$row->col2][] = ['col3'=>$row->col3, 'col4'=>$row->col4, 'col5'=>$row->col5];
}

echo json_encode($resultArray);