create custom json array from two tables

Question

I have following table in mysql. 1)User

userid    username    college
1         robert      sarauniversity
2         albert      oxford

2)UserDetails
userid     subjectid
1          1
1          2
2          3
1          4
2          1

3)subjectdetails
subjectid  subjectname          subjectteacher
1          basic science        pro. vengaskar
2          advance mathematics  pro. richard
3          history              pro. michale
4          geography            pro. renuka

I need following data in json

  [  
  {
   id:1
   username:robert      
   subjects:[
   {
       subjectname:pro. geography            ,
       subject teacher:algebra

   },
  {
       subjectname:pro. vengaskar,
       subject teacher:pro. renuka
  }
  ]
  },
  {
   id:2
   username:albert      
   subjects:[
   {
       subjectname:history                          ,
       subject teacher: pro. michale

   },
  {
       subjectname:basic science,
       subject teacher: pro. vengaskar

  }
  ]
  }
  }

I have developed json rest api several times but this time it is json array with object which have json array. I ma confused how to iterate content and get final desired json. I am not pasting php code as nothing going to help me. so sad it took my two days but still at beginning...

What is your problem?!!

Ma'moon Al-Akash
– Ma'moon Al-Akash

2013-12-09 12:02:52 +00:00
Commented Dec 9, 2013 at 12:02 — Ma'moon Al-Akash
– Ma'moon Al-Akash, Commented Dec 9, 2013 at 12:02
What is id:1 in your JSON Format ?

Noor
– Noor

2013-12-09 12:04:07 +00:00
Commented Dec 9, 2013 at 12:04 — Noor
– Noor, Commented Dec 9, 2013 at 12:04
Obviously this is the user id

Manitoba
– Manitoba

2013-12-09 12:07:29 +00:00
Commented Dec 9, 2013 at 12:07 — Manitoba
– Manitoba, Commented Dec 9, 2013 at 12:07

Noor · Accepted Answer · 2013-12-09 12:59:53Z

1

With a query like this get the result

SELECT A.userid,A.username, C.subjectname,C.subjectteacher FROM `A` AS users INNER JOIN `UserDetails` AS B on A.userid = b.userid INNER JOIN `subjectdetails` AS C on C.subjectid = B.subjectid

And then manipulate result in the below format in your loop

$arr = array (
        array (
                "id" => "1",
                "username" => "Robert",
                "subjects" => array (
                        array (
                                "subjectname" => "pro. geography",
                                "subject teacher" => "algebra" 
                        ) 
                ) 
        ) 
);

echo json_encode ( $arr );

OutPut

[
    {
        "id": "1",
        "username": "Robert",
        "subjects": [
            {
                "subjectname": "pro. geography",
                "subject teacher": "algebra"
            }
        ]
    }
]

edited Dec 9, 2013 at 12:59

answered Dec 9, 2013 at 12:34

Noor

1,4019 silver badges28 bronze badges

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

3 Comments

mayur bhagat Over a year ago

Hi Noor,its dynamic from db field so need php loop structure for that.I do not think it will be solved within one loop.I am hang up with loop.

mayur bhagat Over a year ago

actually I have simplified my real requirement.real code is tough to understand.

mayur bhagat Over a year ago

real code have business logic like audit table,checklist realted to audit and so on.So I have simplified in terms of easy example else real sql query is too large.

Damodaran · Accepted Answer · 2013-12-10 10:14:52Z

Try this

SELECT DISTINCT @pv := usr.userid, CONCAT(  "[", CONVERT( GROUP_CONCAT(DISTINCT  "{\"id\":", usr.userid,  ", \"subjects\":", (
SELECT CONCAT(  "[", CONVERT( GROUP_CONCAT(  "{\"id\":", subjectid,  ", \"subjectname\":\"", subjectname,  "\"}" ) 
USING utf8 ) ,  "]" ) 
FROM subjectdetails
WHERE subjectid
IN (
SELECT subjectid
FROM UserDetails
WHERE userid = @pv)
),  ", \"username\":\"", usr.username,  "\"}" ) 
USING utf8 ) ,  "]" ) AS jsn
FROM User usr
JOIN UserDetails det ON det.userid = usr.userid
LEFT OUTER JOIN subjectdetails sub ON sub.subjectid = det.subjectid

Output:

ID  JSN
1   [{"id":1, "subjects":[{"id":1, "subjectname":"basic science"},{"id":2, "subjectname":"advance mathematics"},{"id":4, "subjectname":"geography"}], "username":"robert"},{"id":2, "subjects":[{"id":1, "subjectname":"basic science"},{"id":2, "subjectname":"advance mathematics"},{"id":4, "subjectname":"geography"}], "username":"albert"}]

The JSN column having the structure

[
    {
        "id": 1,
        "subjects": [
            {
                "id": 1,
                "subjectname": "basic science"
            },
            {
                "id": 2,
                "subjectname": "advance mathematics"
            },
            {
                "id": 4,
                "subjectname": "geography"
            }
        ],
        "username": "robert"
    },
    {
        "id": 2,
        "subjects": [
            {
                "id": 1,
                "subjectname": "basic science"
            },
            {
                "id": 2,
                "subjectname": "advance mathematics"
            },
            {
                "id": 4,
                "subjectname": "geography"
            }
        ],
        "username": "albert"
    }
]

SQLFIDDLE

Note: The JSON structure in your question is not a valid one. Use jsonlint to generate valid json.

Collectives™ on Stack Overflow

create custom json array from two tables

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related