Dismiss
Announcing Stack Overflow Documentation

We started with Q&A. Technical documentation is next, and we need your help.

Whether you're a beginner or an experienced developer, you can contribute.

Sign up and start helping → Learn more about Documentation →

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"
  }
]
share|improve this question
    
What are the options you need on your drop down select? – Mohamed Nizar 17 hours ago
    
That drop-down part is working fine with the desired JSON data. But I am not able to build/echo out that nested JSON formated data anyhow. I need help to format the data like this { 'India': { 'Andhra Pradesh': ['Vijayawada', 'Guntur', 'Nellore', 'Kadapa'], 'Madhya Pradesh': ['Hyderabad', 'Warangal', 'Karimnagar'], },.... drop-down is not my concern, formatting the json data is my main issue. – Developerscentral 15 hours ago
    
try to use angular-filter ,it will allows you to filter as you desired on front end you can easy access it in the controller as well.github.com/a8m/angular-filter – Mohamed Nizar 14 hours ago
    
Sorry to say, but I think I am unable to make you understand my problem. I have no problem with AngularJs and filtering at all. I just want to echo that JSON data with that specific nested format mentioned at the beginning of my post. Try understand my question first . – Developerscentral 13 hours ago
    
3 dimensional array is encoded to achieve the JSON data. So you loop through the result and format the result set Like, array('India'=>array('Andhra Pradesh'=>array(Vijayawada', 'Guntur', 'Nellore', 'Kadapa'))) then do json_encode – Ashu Jha 13 hours ago
up vote 1 down vote accepted

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.

share|improve this answer

This is modification I have done in my model and now its working perfectly ! Thank you everyone...

 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`);");

        $data = $query->result_array();

        $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 $result;
    }
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.