CODEWITHSUNDEEP
phpmysqlsqlcodeigniter
user3675089
user3675089

Reputation: 47

MySQL join shows multiple records

I am using CodeIgniter and in my sql join query it gets duplicate values. For an example if tbl_employee_contact has two contact numbers it shows same record twice with different contact number for each. How can I display only one record?

Database image

Image example

this is my model

function get_records(){
    $this->db->select(array(
         'tbl_employee_registration.emp_id',
         'tbl_employee_registration.emp_fname',
         'tbl_employee_registration.emp_email',
         'tbl_employee_registration.emp_status',
         'tbl_employee_contact.emp_contact',    
    ));
    $this->db->from('tbl_employee_registration');
    $this->db->join('tbl_employee_contact','tbl_employee_contact.emp_id=tbl_employee_registration.emp_id');

    $query = $this->db->get();
    return $query->result();
}

this is my controller

function manage_operators(){
    $data = array();
    if($query = $this->mod_employee->get_records())
    {
        $data['records'] = $query;
    }
    $this->load->view('admin/admin_manage_operators',$data);
}

Upvotes: 2

Views: 1353

Answers (2)

podijobs
podijobs

Reputation: 86

You can use group_by

 $this->db->group_by("your table");

finally your model will look like this

 function get_records(){
        $this->db->select(array(
            'tbl_employee_registration.emp_id',
            'tbl_employee_registration.emp_fname',
            'tbl_employee_registration.emp_email',
            'tbl_employee_registration.emp_status',
            'tbl_employee_contact.emp_contact',    
        ));
        $this->db->from('tbl_employee_registration');
        $this->db->join('tbl_employee_contact','tbl_employee_contact.emp_id=tbl_employee_registration.emp_id');
        $this->db->group_by("tbl_employee_registration.emp_id");

        $query = $this->db->get();
        return $query->result();
    }

Upvotes: 1

GGio
GGio

Reputation: 7643

You need what's called GROUP BY or add DISTINCT to your select query.

$this->db->select(array(
    'DISTINCT  tbl_employee_registration.emp_id',
    'DISTINCT  tbl_employee_registration.emp_fname',
    'DISTINCT  tbl_employee_registration.emp_email',
    'DISTINCT  tbl_employee_registration.emp_status',
    'DISTINCT  tbl_employee_contact.emp_contact',    
));

Or you can select all data but in your loop just add to array with unique IDs like

$arr[$ID][] = $record

Upvotes: 1

Related Questions