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

Solution:

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.

Answer

Solution:

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

Source