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

user_14713594 0
Viewed 935

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.

mysql php json
2 Answers

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.

user_26502090

You can do it either through php or through mysql

Using mysql

SELECT
    users.user_id,
    users.name,
    GROUP_CONCAT(subjects.subject) sub
FROM
    `users`
INNER JOIN subjects ON users.user_id = subjects.user_id
GROUP BY
    subjects.user_id

Result will be like

enter image description here

user_35779460
Related