Developerscentral Developerscentral - 3 months ago 16
JSON Question

Nested JSON data using MySQL and PHP for AngularJs | ng-options

Could anyone please tell me how to create the following nested JSON data using MySQL and PHP in codeigniter.

I want my data in the given format.

$data = {
'India': {
'Andhra Pradesh': ['Vijayawada', 'Guntur', 'Nellore', 'Kadapa'],
'Madhya Pradesh': ['Hyderabad', 'Warangal', 'Karimnagar'],
},
'USA': {
'San Francisco': ['SOMA', 'Richmond', 'Sunset'],
'Los Angeles': ['Burbank', 'Hollywood']
},
'Australia': {
'New South Wales': ['Sydney', 'Orange', 'Broken Hill'],
'Victoria': ['Benalla', 'Melbourne']
}
};


My Model code is below:

public function getAll()
{
$query = $this->db->query("SELECT
`country`.`countryName`
, `states`.`name` AS `stateName`
, `cities`.`cityName`
FROM
`tablename`.`states`
INNER JOIN `tablename`.`country`
ON (`states`.`country_ID` = `country`.`ID`)
INNER JOIN `tablename`.`cities`
ON (`cities`.`state_id` = `states`.`ID`);");

return $query->result();

}


And Controller code is given below :

public function getAllData()
{
//get All details
$this->load->model('Shiksha_model','locations');
$data = $this->locations->getAll();
echo json_encode($data);
}


So I am gating the ouput as given below :

[
{
"countryName": "INDIA",
"stateName": "West-Bengal",
"cityName": "Kolkata"
},
{
"countryName": "INDIA",
"stateName": "Bihar",
"cityName": "Purnia"
}
]

Answer

Just iterate through and build what you need:

$result = [];
foreach ($data as $row) {
  if (!isset($result[$row['countryName']]))
    $result[$row['countryName']] = [];
  if (!isset($result[$row['countryName']][$row['stateName']]))
    $result[$row['countryName']][$row['stateName']] = [];
  $result[$row['countryName']][$row['stateName']][] = $row['cityName'];
}
return json_encode($result);

You also can achieve same result with same client-side code.