Group By Getting JSON format in php

Question

Hello I am getting the problem how to group by json format in php.

Here is my code.

$sql = "SELECT * FROM event as e 
        JOIN course as c ON e.id = c.event_id
        JOIN hold as h ON c.id = h.course_id
        GROUP By h.id";

$query = mysqli_query($con, $sql);

$data = [];
while( $row = mysqli_fetch_assoc($query) ) {

    $eInfo = new stdClass();
    $eInfo->e_info = [];

    $event = new stdClass();

    $event->e_code = $row['e_code'];
    $event->c_info = [];

    $course = new stdClass();
    $course->c_code = $row['course_code'];
    $course->h_info = [];

    $hold = new stdClass();

    $hold->h_code = $row['hold_code'];

    $course->h_info[] = $hold;

    $event->c_info[] = $course;

    $eInfo->e_info[] = $event;

    $data[] = $eInfo;

}

$json = json_encode($data, JSON_PRETTY_PRINT);
return $json

the result is:

[
{
    "e_info": [
        {
            "e_code": "e001",
            "c_info": [
                {
                    "c_code": "C001",
                    "h_info": [
                        {
                            "h_code": "h001"
                        }
                    ]
                }
            ]
        }
    ]
},
{
    "e_info": [
        {
            "e_code": "e001",
            "c_info": [
                {
                    "c_code": "C001",
                    "h_info": [
                        {
                            "h_code": "h002"
                        }
                    ]
                }
            ]
        }
    ]
},
{
    "e_info": [
        {
            "e_code": "e001",
            "c_info": [
                {
                    "c_code": "C002",
                    "h_info": [
                        {
                            "h_code": "h001"
                        }
                    ]
                }
            ]
        }
    ]
},
{
    "e_info": [
        {
            "e_code": "e001",
            "c_info": [
                {
                    "c_code": "C002",
                    "h_info": [
                        {
                            "h_code": "h002"
                        }
                    ]
                }
            ]
        }
    ]
}
]

e_code and course_code are duplicate and I want to group these. Is it possible to get the json array like this with php as json format ?

[
{
    "e_info": [
        {
            "e_code": "e001",
            "c_info": [
                {
                    "c_code": "c001",
                    "h_info": [
                        {
                            "h_code": "h001"
                        },
                        {
                            "h_code": "h002"
                        }
                    ]
                },
                {
                    "c_code": "c002",
                    "h_info": [
                        {
                            "h_code": "h001"
                        },
                        {
                            "h_code": "h002"
                        }
                    ]
                }
            ]
        }
    ]
}
]

Help me please. thank you.

Can you share some part of your code? – Rtra Jun 01 '17 at 22:18 — Rtra, Jun 01 '17 at 22:18
I edit the question plz review it. – Min Naing Jun 02 '17 at 07:31 — Min Naing, Jun 02 '17 at 07:31

score 0 · Answer 1 · answered Jun 01 '17 at 22:13

0

You must try GROUP BY in your SELECT statement like this

"SELECT * FROM tablename GROUP BY e_code, c_code";

answered Jun 01 '17 at 22:13

Rtra

514
12
25

I only want to group by json format? – Min Naing Jun 01 '17 at 22:21
when group by data is duplicate – Min Naing Jun 01 '17 at 22:23
Yes this is because your column `h_code` contains different value and not included in GROUP BY – Rtra Jun 01 '17 at 22:26
e_code contains many c_code and c_code contains many h_code – Min Naing Jun 01 '17 at 22:38

Group By Getting JSON format in php

1 Answers1