Developerscentral Developerscentral - 4 months ago 20
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
, `states`.`name` AS `stateName`
, `cities`.`cityName`
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
$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"


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.