Abin Jacob Abin Jacob - 3 months ago 16
MySQL Question

Codeigniter, How to display select query result in json format?

I have two tables
table1: dr_country

enter image description here

table2: dr_city

enter image description here

I have to fetch data from both tables. My required output should look like in format:

[{"id":"1","country_name":"Australia","country_code":"61","iso_code":"AUS","city":
[{"id":"1","city_name":"sydney"},{"id":"2","city_name":"melbourne"},{"id":"3","city_name":"perth"},{"id":"4","city_name":"brisbane"}]},
{"id":"2","country_name":"Bangladesh","country_code":"880","iso_code":"BGD","city":
[{"id":"5","city_name":"dhaka"},{"id":"6","city_name":"chittagong"}]}]


I model method looks like:

public function countryAction()
{
$this->db->select("*");
$this->db->from('dr_country');
$this->db->join('dr_city', 'dr_country.id = dr_city.country_id','left');

$result = $this->db->get()->result_array();
if($result)
{
print_r(json_encode($result));

}
else
{
$detail = array(
'status'=>'unsucess',
);

echo json_encode($detail);
}
}


It produce the output like:

[{"id":"1","country_name":"Australia","country_code":"61","iso_code":"AUS","city_name":"sydney","country_id":"1"},{"id":"2","country_name":"Australia","country_code":"61","iso_code":"AUS","city_name":"melbourne","country_id":"1"},{"id":"3","country_name":"Australia","country_code":"61","iso_code":"AUS","city_name":"perth","country_id":"1"},{"id":"4","country_name":"Australia","country_code":"61","iso_code":"AUS","city_name":"brisbane","country_id":"1"},{"id":"5","country_name":"Bangladesh","country_code":"880","iso_code":"BGD","city_name":"dhaka","country_id":"2"},{"id":"6","country_name":"Bangladesh","country_code":"880","iso_code":"BGD","city_name":"chittagong","country_id":"2"}]


So for getting required json format what all changes should I make in the model function, I am new to this, Thanking in advance.

Answer

Try this in your model

$countries = $this->db->get('dr_country')->result();
foreach ($countries as $key => $country) {
    $country->city = $this->db->get_where('dr_city', array('country_id' => $country->id))->result();
}
print_r(json_encode($countries));
Comments