How to create json encode by an object with several tables in php?

advertisements

I have two table names are channel and department.

$result = mysql_query("SELECT * FROM channels");
while($json = mysql_fetch_assoc($result)){
    $fetch = mysql_query("SELECT * FROM departments where channelid='".$json['id']."'");
    $json2 = array();
    while ($row = mysql_fetch_array($fetch)){
        $json2[] = array('departmentname' => $row["departmentname"],'departmentid' => $row["did"]
        );
    }
    $json['department'] = $json2;
    echo json_encode($json);
}

Ouput:

{
  "id": "1",
  "channelname": "con",
  "channelurl": "http:\/\/xxx.net\/YY\/",
  "department": [
    {
      "departmentname": "xxx Travel",
      "departmentid": "1"
    },
    {
      "departmentname": "xxxx Virtual Assist",
      "departmentid": "2"
    },
    "departmentname": "xxx  Premier",
    "departmentid": "3"
  },
  {
    "departmentname": "xxxx Events",
    "departmentid": "4"
  }
]
}{
"id": "2",
"channelname": "Slim",
"channelurl": "http:\/\/xxxxx.net\/slim\/",
"department": [
  {
    "departmentname": "Virtualvideo",
    "departmentid": "5"
  }
]
}

Expected Result:

[
  {
    "id": "1",
    "channelname": "xxxxx",
    "channelurl": "http://XXXXX.net/yyy/",
    "department": [
      {
        "departmentname": "XXX Travel",
        "departmentid": "1"
      },
      {
        "departmentname": "XXXX Virtual Assist",
        "departmentid": "2"
      },
      {
        "departmentname": "XXXX Premier",
        "departmentid": "3"
      },
      {
        "departmentname": "XXXX Events",
        "departmentid": "4"
      }
    ]
  },
  {
    "id": "2",
    "channelname": "Slim",
    "channelurl": "http://XXXXXX.net/slim/",
    "department": [
      {
        "departmentname": "Virtual video",
        "departmentid": "5"
      }
    ]
  }
]


try this:

$json_channel = array();
$json_final = array();
$result = mysql_query("SELECT * FROM channels");
while($json = mysql_fetch_assoc($result))
{
    $fetch = mysql_query("SELECT * FROM departments where channelid='".$json['id']."'");
    $json_dept = array();

    while ($row = mysql_fetch_array($fetch))
    {
        $json_dept[] = array('departmentname' => $row["departmentname"],'departmentid' => $row["did"]);
    }

    $json_channel = array('id' => $json["ch_id"], 'channelname' =>$json["channelname"], 'department' => $json_dept);    

    $json_final[] = $json_channel;
}
echo json_encode($json_final);