JOIN the same table to a parent table twice with CodeIgniter query builder [duplicate]

Question

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 do I do that?

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 can I differentiate which is the sales and which is the 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);

}

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>

If I am correct you want the sales name, tech name and company name ? — killstreet
– killstreet, Commented Feb 8, 2016 at 9:10
if(isset($company->sales_id) && $company->sales_id != '' && $company->sales_id != 'NULL') { echo "sales person"; } if(isset($company->tech_id) && $company->tech_id != '' && $company->tech_id != 'NULL') { echo "tech person"; } — msvairam
– msvairam, Commented Feb 8, 2016 at 9:12

Praveen Kumar · Accepted Answer · 2016-02-08 09:36:40Z

1

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

edited Feb 8, 2016 at 9:36

answered Feb 8, 2016 at 9:30

Praveen Kumar

2,3981 gold badge14 silver badges21 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

mickmackusa May 1 at 9:55

Oh my dear heavens, NO! Do not query your database from the view layer and do not make iterated trips to the database. Get everything you need in one trip to the database, then send that payload to the view for presentation.

mickmackusa · Accepted Answer · 2025-10-24 07:51:02Z

To include the first_name of the company.sales_id and company.tech_id individually, you will need to JOIN the users table to the company table twice. To prevent column name collisions in the SELECT clause, give unique column aliases to the accessed foreign columns.

public function get_company()
{
    return $this->db
        ->select('company.*, sales.first_name sales_first_name, tech.first_name tech_first_name')
        ->join('users sales', 'sales.id = company.sales_id')
        ->join('users tech', 'tech.id = company.tech_id')
        ->get('company')
        ->result(); 
}

The adjustments to the view are simple and do not involve querying the database because everything you already need is in the payload passed from the model to the controller then to the view.

<td ...,'sales_id',... ><?php echo $company->sales_first_name; ?></td>
<td ...,'tech_id',... ><?php echo $company->tech_first_name; ?></td>

killstreet · Accepted Answer · 2016-02-08 09:18:27Z

-1

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;?>

answered Feb 8, 2016 at 9:18

killstreet

1,3402 gold badges16 silver badges39 bronze badges

7 Comments

killstreet Over a year ago

Oh okay, got confused because in the question it said controller

Rajan Over a year ago

okay sorry but how to show my tech person? it still returns same for both sales person and tech person

killstreet Over a year ago

There is no table that shows what the tech table looks like, update the question with the tech table and I will update the awnser

Rajan Over a year ago

i just have two tables one is company and other is users, when i add a company i add sales person and tech person for that company, and its stored in users table, the first record inserted would be sales and second would be tech and then will get the in of both inserted record and save it in company table

Rajan Over a year ago

i am adding the add method in question so u can undertstand

|

Collectives™ on Stack Overflow

JOIN the same table to a parent table twice with CodeIgniter query builder [duplicate]

3 Answers 3

1 Comment

Comments

7 Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

7 Comments

Linked

Related