i'm trying to create a multilevel list. I have two tables 'State' and 'City'. The city table has a foreign key 'state_id' which is the primary key of 'State' table. I want to show each state and under each state there is multiple cities. But when i run my code only the last stored state in the db and the cities under it show up. I want all the states in the Db and the cities corresponds to it to appear.
Part of my controller:
function index(){
$result = $this->db->count_all('state');
$id=1;
while ($id<=$result){
$data ['state'] = $this->state_model->stateid($id);
$data['city']=$this->state_model->statec($id);
$id++;
}
$this->load->view('state_view',$data);
}
The model:
function stateid($id = 0){
$this->db->where('id',$id);
$sql = $this->db->get('state');
return $sql->result();
}
function statec($id = 0){
$this->db->where('state_id',$id);
$sql = $this->db->get('city');
return $sql->result();
}
The view:
<?php foreach($state as $row):?>
<h4><?php echo $row->statename;?></h4>
<?php foreach($city as $row):?>
<?php echo $row->cityname; ?></br></br></br>
<?php endforeach;?></br></br>
<?php endforeach;?></br></br>