Create JSON in PHP using database

Question

I have this query that I have run to gather data from three tables. I want to create JSON from this data gathered. I have written the following script to create it. Please guide me if there is any quick or smart workaround to do this.

This is the snapshot of my database result that I need to create JSON from:

Data gathered from 3 tables using joins

Script that I'm using is this.

    $jsonarray = array();

    while ($i<count($result))
   {
        $channel_cat = Array("Channel Category" => Array("name" => $result[$i]['chanct_name']));

        $id = $result[$i]['chanct_id'];
    while($id == $result[$i]['chanct_id'])
        {
            $content[] = Array( "Channel Name" => $result[$i]['con_name'], "image" => $result[$i]['con_image']); 
            $i++;
        }

        $jsonarray[][] = Array($channel_cat, $content);
        unset($content);

   }
     echo json_encode($jsonarray);

This gives me result pasted below.

[[[{"Channel Category":{"name":"Movie"}},
[{"Channel Name":"Channel1","image":"Thanks.jpg"},
{"Channel Name":"Channel2","image":"Thanks.jpg"},
{"Channel Name":"Channel4","image":"amazon-logo-b_tpng.png"},
{"Channel Name":"High","image":"Thanks.jpg"}]]]
,[[{"Channel Category":{"name":"Documentary"}},
[{"Channel Name":"Channel7","image":"amazon-logo-b_tpng.png"}]]]]

However I am looking for result below.

{
"channelsCategories":
    [
        {
            "name":"Movie",
            "image": "MoviePoster",
            "contents":
                [
                    {
                        "name":"Channel 1",
                        "image":"Thanks.jpg",
                    },
                    {
                        "name":"Channel 2",
                        "image":"Thanks.jpg",
                    },
                    {
                        "name":"Channel 4",
                        "image":"amazon-logo...",
                    },
                    {
                        "name":"High",
                        "image":"Thanks.jpg",
                    }
            ]

        },
        {
            "name":"Documentary",
            "image": "MoviePoster",
            "contents":
                [
                    {
                        "name":"Channel 7",
                        "image":"amazon.....",
                    }
            ]

        }


    ]
}

Any help or guidance will be really helpful.

pisamce · Accepted Answer · 2015-05-31 16:29:49Z

1

Try this, it will give you the result you are expecting:

$typeArr = array();
foreach($result as $a){
    $typeArr[$a['chanct_name']][] = array(
        'name'=>$a['con_name'],
        'image'=>$a['con_image']
    );
}

$jsonarray = array();
foreach($typeArr as $type=>$contents){
    $jsonarray['channelsCategories'][] = array(
        'name'=>$type,
        'contents'=>$contents
    );
}

echo json_encode($jsonarray);

edited May 31, 2015 at 16:29

answered May 31, 2015 at 16:18

pisamce

5332 silver badges6 bronze badges

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

1 Comment

Bilal Ahmad Over a year ago

Thank you @pisamce This, really gives the desired output.

Collectives™ on Stack Overflow

Create JSON in PHP using database

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related