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.

You are looking for for loop or some kind of array_reduce. Basically you loop elements and gather subjects based on user_id — Justinas
– Justinas, Commented Jun 9, 2021 at 6:12
@Justinas may you please explain it with full query example. — Zia Ur Rahman
– Zia Ur Rahman, Commented Jun 9, 2021 at 6:15
You should look for group concat and group by queries in MySql — Abhishek Sharma
– Abhishek Sharma, Commented Jun 9, 2021 at 6:17
@AbhishekSharma if you don't mind please may you help me in writing a query for this issue. — Zia Ur Rahman
– Zia Ur Rahman, Commented Jun 9, 2021 at 6:19

Abhishek Sharma · Accepted Answer · 2021-06-09 06:24:02Z

2

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.

answered Jun 9, 2021 at 6:24

Abhishek Sharma

3,3501 gold badge23 silver badges39 bronze badges

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

3 Comments

Zia Ur Rahman Over a year ago

but when i json encode it , it gives me a list of subs as a string with commas but i want a nested json ,list as i described above.

Abhishek Sharma Over a year ago

I have already mentioned in the answer to use explode function to convert that string in subject to array. Read the answer carefully :)

Abhishek Sharma Over a year ago

for example, you can do explode(',', $result[i]['subject']); where $result is the result of query and i is the index of result. This will convert subject to an array. i.e. "Math, Chem, Bio" => ["Math", "Chem", "Bio"]

Indra Kumar S · Accepted Answer · 2021-06-09 06:29:57Z

0

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

answered Jun 9, 2021 at 6:29

Indra Kumar S

2,9552 gold badges19 silver badges29 bronze badges

3 Comments

Zia Ur Rahman Over a year ago

but after encoding it to json its converting list of sub data into a string. and I want to get a nested json list

Zia Ur Rahman Over a year ago

how can i do this.? please help in this regard too

Indra Kumar S Over a year ago

@ZiaUrRahman share your code block that fetches data from db and json_encodes

Collectives™ on Stack Overflow

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

2 Answers 2

3 Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related