Bilal Farooq Ahmad Bilal Farooq Ahmad - 3 years ago 123
JSON Question

Create JSON in PHP using database

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.

Answer Source

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);
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download