json array for one to many relations

Question

I want to create a json string from two table's queries in one to many relations. I have done like this

$query = "SELECT id,name FROM sample1 ORDER BY id ASC" ;                
$result = mysql_query($query) or die(mysql_error());
$parent = array() ;

while($row = mysql_fetch_array($result))
{   
    $parent[]= array("id"=>$row['id'],"name"=>$row['name']);
    $query1 = "SELECT id,cid,cmessage FROM sample2 WHERE id = '$row[id]' ORDER BY cid ASC" ;            
    $result1 = mysql_query($query1) or die(mysql_error());

    while($row1 = mysql_fetch_array($result1))
    {
        $parent[] = array("id"=>$row1['id'],"cid"=>$row1['cid'],"comment"=>$row1['cmessage']);

    }  
} echo  json_encode($parent);

it shows an output like this [{"id":"1","name":"Arathy"},{"id":"1","cid":"11","comment":"hai"},{"id":"1","cid":"111","comment":"exe"},{"id":"2","name":"Dhanya"},{"id":"2","cid":"22","comment":"yes"}]

But I want the format like as shown below ,

[
{
    "id": "1",
    "name": "Arathy",
    "details": [
        {
            "id": "1",
            "cid": "11",
            "comment": "hai"
        },
        {
            "id": "11",
            "cid": "111",
            "comment": "exe"
        }
    ]
},
{
    "id": "2",
    "name": "Dhanya",
    "details": [
        {
            "id": "2",
            "cid": "22",
            "comment": "yes"
        }
    ]
} ]

Please help to correct

score 1 · Accepted Answer · edited May 25 '19 at 12:02

It will be something like,

while($row = mysql_fetch_array($result))
{   
    $parent[$row['id']]= array("id"=>$row['id'],"name"=>$row['name']);
    $query1 = "SELECT id,cid,cmessage FROM sample2 WHERE id = '$row[id]' ORDER BY cid ASC" ;            
    $result1 = mysql_query($query1) or die(mysql_error());

    while($row1 = mysql_fetch_array($result1))
    {
        $parent[$row['id']]["details"][] = array("id"=>$row1['id'],"cid"=>$row1['cid'],"comment"=>$row1['cmessage']);

    }  
}
echo json_encode($parent);

Warning: Please, don't use mysql_* functions in new code. They are no longer maintained and are officially deprecated. See the red box? Learn about prepared statements instead, and use PDO or MySQLi - this article will help you decide which. If you choose PDO, here is a good tutorial.

json array for one to many relations

1 Answers1