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

why have you defined a table? just write it in or use a class var. seems excessive. — Alex, Jul 19 '18 at 05:58
So i have to join in query ? ther is no other sollution for that ? — Holy Coder, Jul 19 '18 at 05:59
But how can i join in this query `"SELECT * FROM ".CITYTABLE." ORDER BY name ASC";` with statename — Holy Coder, Jul 19 '18 at 06:01
Sure i have posted it. in there i have two table named `m_state` And `m_city`. City table have `state_id` which relate to table `m_state`. — Holy Coder, Jul 19 '18 at 06:09

score 1 · Answer 1 · edited Jul 19 '18 at 06:54

If you have a state table like

Table Name: states 
Fields : state_id, state_name
//City tbl
Table Name: city
Fields: id,name,state_id

You can use join the two tables like,

public function city(){
   $query = "SELECT c.id, c.name, s.state_name FROM city c join states s
             on c.state_id = s.state_id ORDER BY c.name ASC";

  $citydata = $this->masterModel->_get_data("",$query);

  $this->create_template('manage-city', 'admin',compact('citydata'));
}

But, you have to follow the standard codeigniter query.

score 1 · Answer 2 · answered Jul 19 '18 at 06:13

Try with this code :

Join with m_state table with m_city like this : (better use query builder class to get the results)

public function city()
{
    $sql = "SELECT `m_city`.*, `m_state`.`name` as `state_name` 
            FROM `m_city` 
            JOIN `m_state` 
            ON `m_state`.`id` = `m_city`.`state_id` 
            ORDER BY `m_city`.`name` ASC";

    $citydata = $this->masterModel->_get_data("",$sql);
    $this->create_template('manage-city', 'admin',compact('citydata'));
}

For more : https://www.codeigniter.com/user_guide/database/query_builder.html

TarangP · Accepted Answer · 2018-07-19T06:22:51.847

0

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

edited Jul 19 '18 at 06:22

answered Jul 19 '18 at 06:15

TarangP

2,711
5
20
41

Get data From database according to id in view : Codeigniter

3 Answers3