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:

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.

Answer 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);