Writing a MySQL query to obtain an array group in PHP and output as 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.

What MySQL version? Because it's possible to generate JSON on the MySQL server also with native JSON functions.. For a more general answer you can use GROUP_CONCAT in combination with CONCAT and GROUP BY to also generate JSON on every MySQL version. — Raymond Nijland
– Raymond Nijland, Commented Aug 13, 2018 at 16:35
'grouping' has a particular meaning in sql, which doesn't quite apply here. This is just a simple array manipulation. In consequence, the version of MySQL is utterly irrelevant. — Strawberry
– Strawberry, Commented Aug 13, 2018 at 16:36
@RaymondNijland - This is irrelevant as I am trying to manipulate this thru array looping in PHP — Jojo
– Jojo, Commented Aug 13, 2018 at 16:37
@MohamedElMrabet - Yes, it has nothing to do as the output of the JSON is not grouped the way I want it — Jojo
– Jojo, Commented Aug 13, 2018 at 16:37

Yogesh Salvi · Accepted Answer · 2018-08-13 16:37:06Z

3

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

answered Aug 13, 2018 at 16:37

Yogesh Salvi

1,2222 gold badges11 silver badges19 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Mohamed El Mrabet · Accepted Answer · 2018-08-13 16:41:12Z

1

Try This

foreach ($query as $allData) {

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

It will be group all the results and give you an array with every $allData->col2

answered Aug 13, 2018 at 16:41

Mohamed El Mrabet

6338 silver badges16 bronze badges

Collectives™ on Stack Overflow

Writing a MySQL query to obtain an array group in PHP and output as JSON

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related