Get nested JSON data from one to many relationships in MYSQL using PHP

Question

I have two tables as follows
user table

user_id	name
1	zia
2	john
3	raza

subject table

data_id	user_id	subject
1	1	Math
2	1	Chem
3	1	Bio
4	2	Math
5	2	Phy
6	3	Eng

when I am querying data i am getting results like this:

[
{
    "user_id": "1",
    "name": "zia",
    "subject" : "Math"
   
},
{
    "user_id": "1",
    "name": "zia",
    "subject" : "Chem"
},
{
    "user_id": "1",
    "name": "zia",
    "subject" : "Bio"
},
{
    "user_id": "2",
    "name": "john",
    "subject" : "Math"
},
{
    "user_id": "2",
    "name": "john",
    "subject" : "Phy"
},
{
    "user_id": "3",
    "name": "Raza",
    "subject" : "Eng"
}

]

My query is as follows

SELECT users.user_id , users.name , subjects.subject FROM users,subjects;
Where users.id = subjects.id;

the reuluts shown above are in such a way that we have a separate data row for every subject of one student/user.

but actually i want to get data in follwoing way:

[
{
    "user_id": "1",
    "name": "zia",
    "subject": [
        "Math",
        "Chem",
        "Bio"
    ]
},
{
    "user_id": "2",
    "name": "john",
    "subject": [
        "Math",
        "Phy"
    ]
},
{
    "user_id": "3",
    "name": "Raza",
    "subject": "Eng"
}

]

Here as you see that we have data in such a way that every student has all of its subjects in one row only or in nested json formate

PLease help me in solving this question Previously marked as solved but not solved.

Answer 1

You should try using group_concat in MySql to merge subjects in a single column and group by the user_id.

Something like

SELECT users.user_id , users.name , GROUP_CONCAT ( subjects.subject ) as "subjects" FROM users,subjects;
Where users.id = subjects.id group by users.user_id;

Expected output should be something like

[{
    "user_id": "1",
    "name": "zia",
    "subject": "Math, Chem, Bio",
},
{
    "user_id": "2",
    "name": "john",
    "subject": "Math , Phy"
},
{
    "user_id": "3",
    "name": "Raza",
    "subject": "Eng"
}]

You can then explode the subjects using explode(",",$str)

This is just an example, try to play around it. For reference you can look into this.