Get data From database according to id in view : Codeigniter

Question

i am newbie to codeigniter my city table structure is like

id,name,state_id

Now in view i get all data of city but i wants to also print state name.but in city table i have only state id.so how can i get state name in view

This is My controller.

public function city(){
        $query = "SELECT * FROM ".CITYTABLE." ORDER BY name ASC";
        $citydata = $this->masterModel->_get_data("",$query);
        $this->create_template('manage-city', 'admin',compact('citydata'));
}

And my view is like

<tbody>
  <?php
    $i=1;
    foreach ($citydata as $value) { 
      if($value->status == 1){
        $status = "<span class='label label-success'>Active<span>";
      }else{
        $status = "<span class='label label-warning'>Deactive<span>";
      }
  ?>
  <tr>
    <th scope="row"><?= $i++ ?></th>
    <td><?= ucfirst($value->name) ?></td>
    <td><?= $value->state_id ?></td>//HEREEEEEEEEEEE IS I WANT TO SHOW NAME OF STATE/*****/
    <td><?= $status ?></td>
    <td>
      <?php echo anchor("master/edit_city/{$value->id}","<i class='fa fa-pencil'></i> Edit",['class'=>"btn btn-link"]) ?>
      <span class="rmv-city" data-state="<?=$value->id?>"><i class="fa fa-trash"></i> Delete</span>
    </td>
  </tr>
  <?php } ?>
</tbody>

Here is my Schema

Answer 1

Use Following Query

SELECT m_city.id,m_city.name,m_state.name AS state_name
FROM m_city
JOIN m_state ON m_city.state_id=m_state.id