Rajan February 2016

Join Tables in 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 Info        

Answers


killstreet February 2016

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


Praveen Kumar February 2016

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. Edite

Post Status

Asked in February 2016
Viewed 1,441 times
Voted 13
Answered 2 times

Search




Leave an answer