Join Query Using Codeigniter

Question

I want to join two tables in codeigniter. I have two tables called designation and staff. I have created to select staff title using dropdown, but this shows data in data table using foreign key value. Now I want show the title in designation table instead of foreign key value. Please any one can help me?

Controller

 public function index()
{  

    $data['designation'] = $this->staff_model->get_designation();

    $this->load->view('admin_include/header');
    $this->load->view('admin_pages/staff_list',$data);
}

    public function fillgrid(){
        $this->staff_model->fillgrid();
    }

Model

class Staff_model extends CI_Model {

//get designation table to populate the designation dropdown
function get_designation()     
{ 
    $this->db->select('designation_id');
    $this->db->select('title');
    $this->db->from('designation');
    $query = $this->db->get();
    $result = $query->result();

    $designation_id = array('-SELECT-');
    $title = array('-SELECT-');

    for ($i = 0; $i < count($result); $i++)
    {
        array_push($designation_id, $result[$i]->designation_id);
        array_push($title, $result[$i]->title);
    }
    return $designation_result = array_combine($designation_id, $title);
}


public function fillgrid(){
    $this->db->order_by("id", "desc"); 
    $data = $this->db->get('staff');

    foreach ($data->result() as $row){
        $edit = base_url().'index.php/staff_controller/edit/';
        $delete = base_url().'index.php/staff_controller/delete/';
        echo "<tr>
                    <td>$row->first_name</td>
                    <td>$row->last_name</td> 
                    <td>$row->designation_id</td>
                    <td>$row->email</td>
                    <td>$row->address</td>
                    <td>$row->contact_no</td>
                    <td>$row->work_experience</td>    
                    <td>$row->qualifications</td>    
                    <td>$row->created</td>
                    <td><a href='$edit' data-id='$row->id' class='btnedit' title='edit'><i class='glyphicon glyphicon-pencil' title='edit'></i></a>&nbsp;&nbsp;&nbsp;&nbsp;<a href='$delete' data-id='$row->id' class='btndelete' title='delete'><i class='glyphicon glyphicon-remove'></i></a></td>    
                </tr>";

    }
    exit;
}

View

 <form class="form-inline" role="form" id="frmadd" action="<?php echo base_url() ?>index.php/staff_controller/create" method="POST">
            <div class="form-group">
                <label class="sr-only" for="first_name">First name</label>
                <input type="text" name="first_name" class="form-control" id="first_name" placeholder="First Name">
            </div>
             <div class="form-group">
                <label class="sr-only" for="last_name">Last name</label>
                <input type="text" name="last_name" class="form-control" id="first_name" placeholder="Last Name">
            </div>


             <div class="form-group">
                <label class="sr-only" for="title">Title</label>
                <?php
            $attributes = 'class = "form-control" id = "designation"';
            echo form_dropdown('designation',$designation, set_value('designation'), $attributes);?>
            </div>


            <div class="form-group">
                <div class="input-group">
                    <div class="input-group-addon">@</div>
                    <input class="form-control" name="email" type="email" placeholder="Enter email">
                </div>
             <div class="form-group">
                <label class="sr-only" for="address">Address</label>
                <input type="text" name="address" class="form-control" id="address" placeholder="address">
            </div>
            <div class="form-group">
                <label class="sr-only" for="contact_no">Contact</label>
                <input type="text" class="form-control" name="contact_no" id="contact_no" placeholder="contact number">
            </div>
            <div class="form-group">
                <label class="sr-only" for="work_experience">Work Experience</label>
                <input type="text" class="form-control" name="work_experience" id="work_experience" placeholder="Work Experience">
            </div>
            <div class="form-group">
                <label class="sr-only" for="qualifications">Qualifications</label>
                <input type="text" class="form-control" name="qualifications" id="qualifications" placeholder="Qualifications">
            </div>
            <div class="form-group">
                <label class="sr-only" for="nic">NIC</label>
                <input type="text" class="form-control" name="nic" id="nic" placeholder="NIC">
            </div>
            <div class="form-group">
                <input type="submit" class="btn btn-success" id="btn btn-success" value="submit">
            </div>
        </form>
    </div>

    <table class="table">
        <thead><tr><th>First Name</th><th>Last Name</th><th>Title</th><th>Email</th><th>Address</th><th>Contact</th><th>Work Experience</th><th>Qualifications</th><th>created</th><th>Action</th></tr></thead>
        <tbody id="fillgrid">

        </tbody>
        <tfoot></tfoot>
    </table>

Answer 1

You can join the tables like this:

$data = $this->db->select('*')->from('staff')
  ->join('designation', 'staff.designation_id = designation.designation_id');
  ->order_by("id", "desc")->get();
$result = $data->result(); // every result now has 'title'

Also I would strongly recommend to remove the "echo html" thing from your model and just return an array. You can do whatever you like inside the view. Take a look at this: PHP simple foreach loop with HTML