在codeigniter中加入表

I have two tables: Company & Users.

I have a form in which i insert Company name and others details. in that same form i have a sub form Sales info and Tech info.

The data i insert in sales and tech info gets stored into users tables.and their id are stored into company table in two fields called sales_id and tech_id.

Now for a view i want to fetch company name its sales person and its tech person,How to do it?

The Code in model:

        public function get_company()
    {

        $this->db->select('*');
        $this->db->join('users','users.id = company.sales_id','users.id = company.tech_id');
        $this->db->from('company');
        $query = $this->db->get();
        $result = $query->result(); 
        return $result; 

    }

IN The View:

<?php if(count($companys)): foreach($companys as $company): ?>  

  <td><?php echo $company->first_name; ?></td>

how to differentiate which is sales and which is tech person?

the Controller:

public function add_company($id = NULL)
    {

        $this->data['company'] = $this->company_m->get_new();
        $this->data['user'] = $this->user_m->get_new();
        $rules = $this->company_m->rules_admin;

        $this->form_validation->set_rules($rules);


        if ($this->form_validation->run() == TRUE)
        {



            /*Inserting  Sales Person Information*/

            $data['first_name'] = $this->input->post('first_name_s');

            $data['last_name'] = $this->input->post('last_name_s');

            $data['email'] = $this->input->post('email_s');

            $data['user_type'] ="sales";

            $this->user_m->save($data,$id);

            $sales_id = $this->db->insert_id();



            /*Inserting  Tech Person Information*/
            $data_tech =$this->user_m->array_from_post(array('first_name','last_name','email'));


            $this->user_m->save($data_tech,$id);

            $tech_id = $this->db->insert_id();



            /*Insert Company Information*/

            $data = $this->company_m->array_from_post(array('org_name','dba','addr1','addr2','city','state','country','pin','sales_id','tech_id','tax_number','comment','url'));

            $data['sales_id']= $sales_id;

            $data['tech_id']= $tech_id;

            $org_id = $this->company_m->save($data, $id); 

            redirect('admin/company');


        }
                                            // Load the view
        $this->data['subview'] = 'admin/company/add';
        $this->load->view('admin/_layout_main', $this->data);

    }

I hope you understood my issue.

The Company table

The Users table

the View code:

<table class="table table-striped ">
            <thead>
                <tr class="warning">


                    <th>Organization Name</th>
                    <th>Sales Person</th>
                    <th>Tech Person</th>
                    <th>Tax No</th>


                    <th>Edit</th>
                    <th>Delete</th>


                </tr>
            </thead>
            <tbody>
    <?php if(count($companys)): foreach($companys as $company): ?>  
               <tr class="active">

        <td  contenteditable="true" onClick="edit_company(this)" onBlur="save_company(this,'org_name','<?php echo $company->id; ?>')" ><?php echo $company->org_name; ?></td>
        <td  contenteditable="true" onClick="edit_company(this)" onBlur="save_company(this,'sales_id','<?php echo $company->id; ?>')" ><?php echo $company->first_name; ?></td>
        <td  contenteditable="true" onClick="edit_company(this)" onBlur="save_company(this,'tech_id','<?php echo $company->id; ?>')" ><?php echo $company->first_name; ?></td>
        <td  contenteditable="true" onClick="edit_company(this)" onBlur="save_company(this,'tax_number','<?php echo $company->id; ?>')" ><?php echo $company->tax_number; ?></td>


                <td><?php echo btn_edit('admin/company/edit/' . $company->id); ?></td>
                <td><?php echo btn_delete('admin/company/delete/' . $company->id); ?></td>
            </tr>
    <?php endforeach; ?>
    <?php else: ?>
            <tr>
                <td colspan="3">We could not find any users.</td>
            </tr>
    <?php endif; ?> 
            </tbody>
        </table>

In model Use

  public function get_company()
    {

        $this->db->select('*');
        $this->db->from('company');
        $query = $this->db->get();
        $result = $query->result(); 
        return $result; 

    }

For view :-

 <?php if(count($companys)): foreach($companys as $company):
 $tech_person = $this->db->get_where("users",array("id"=>$company->tech_id))->row();
        $sales_person = $this->db->get_where("users",array("id"=>$company->sales_id))->row();
 ?>  
           <tr class="active">

    <td  contenteditable="true" onClick="edit_company(this)" onBlur="save_company(this,'org_name','<?php echo $company->id; ?>')" ><?php echo $company->org_name; ?></td>
    <td  contenteditable="true" onClick="edit_company(this)" onBlur="save_company(this,'sales_id','<?php echo $company->id; ?>')" ><?php echo $tech_person->first_name; ?></td>
    <td  contenteditable="true" onClick="edit_company(this)" onBlur="save_company(this,'tech_id','<?php echo $company->id; ?>')" ><?php echo $sales_person->first_name; ?></td>
    <td  contenteditable="true" onClick="edit_company(this)" onBlur="save_company(this,'tax_number','<?php echo $company->id; ?>')" ><?php echo $company->tax_number; ?></td>


            <td><?php echo btn_edit('admin/company/edit/' . $company->id); ?></td>
            <td><?php echo btn_delete('admin/company/delete/' . $company->id); ?></td>
        </tr>
<?php endforeach; ?>
<?php else: ?>
        <tr>
            <td colspan="3">We could not find any users.</td>
        </tr>
<?php endif; ?> 
        </tbody>

In this method you even do not need join to get tech and sales person. Edited According to your view

At first move the code your have in the controller to a model. If you use a MVC framework try and use it for what it's built, it will help keep everything clean.

So now in the model you should have the following:

function getCompany(){
    $query = $this->db->select('users.first_name, company.org_name')
    ->from('company')
    ->join('users','users.id = company.sales_id','users.id = company.tech_id')
    ->get()
    ->result_array(); 
    return $query; 
}

In your view you would do the following:

<?php if(count($companys)): foreach($companys as $company): ?>  
<tr>
    <td><?php echo $company['first_name']; ?></td>
    <td><?php echo $company['org_name']; ?></td>
</tr>
<?php endforeach; endif;?>