Codeigniter JOIN multiple tables

Question

I'm having a little trouble in retrieving data in multiple tables using codeigniter.

This is the code i'm using to retrieve data in my model which is working well.

function retrieve_experience($alumni_id)
{
$this->db->select('*');
$this->db->from('experience');
$this->db->where('alumni_id',$alumni_id);
$query = $this->db->get();
return $query;  
}

function retrieve_education($alumni_id)
{
$this->db->select('*');
$this->db->from('education');
$this->db->where('alumni_id',$alumni_id);
$query = $this->db->get();
return $query;
}

Now i tried using a simplified code but fails to display the data. here is the code in my model

function retrieve_all_data($alumni_id)
{
$this->db->select('*');
$this->db->from('experience');
$this->db->join('education','education.alumni_id=experience.alumni_id');
$this->db->where('experience.alumni_id',$alumni_id);
$query=$this->db->get();
return $query->result_array();
}

In my controller, i used this code to retrieving data in my model

function display()
{
$alumni_id = $this->session->userdata('alumni_id');
$data['all_data'] = $this->Alumni_model->retrieve_all_data($alumni_id);
$data['main_content'] = 'alumni_home';
$this->load->view('includes/template', $data);
}

and for the display i used this code

foreach($all_data as $results)
{
/** data from experience table **/
$results['company_name']; 
$results['company_address'];
/** data from education table **/
$results['school_name'];
$results['field_of_study'];
}

I cant display anything at all. Please help

You have any SQL error? Try enable codeigniter profiler to see what query you actual running. — pr0metheus
– pr0metheus, Commented Oct 10, 2015 at 10:28
On view echo $results['company_name']; or <?php echo $results['company_name'];?> — user4419336
– user4419336, Commented Oct 10, 2015 at 10:41
@wolfgang1983 yes i used echo, still no output.. forgot to put echo on my question. my bad — Chris
– Chris, Commented Oct 10, 2015 at 11:20

jlocker · Accepted Answer · 2015-10-10 13:33:50Z

1

Try the below code,

I believe you'll want something like this:

function retrieve_all_data($alumni_id)
{
  $this->db->select("e.*,edu.*");
  $this->db->from("experience e");
  $this->db->join("education edu", "edu.alumni_id = e.alumni_id",'left');
  $this->db->where('e.alumni_id',$alumni_id);
  $this->db->group_by('e.exp_id');
  $query = $this->db->get();
  return $query->result_array();
}

edited Oct 10, 2015 at 13:33

answered Oct 10, 2015 at 12:07

jlocker

1,4981 gold badge12 silver badges23 bronze badges

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

24 Comments

Chris Over a year ago

it only displayed 1 result instead of 3 when i added $this->db->group_by('e.alumni_id');

jlocker Over a year ago

@Chris how do you actually want it??

Chris Over a year ago

imgur.com/a/8aDta i want to display all the data in EXPERIENCE table WHERE alumni_id = $alumni_id. in my experience table, there are 3 entries in alumni_id=14 in the 1st image, it displayed 3 data, (using my old function function retrieve_experience($alumni_id) { $this->db->select('*'); $this->db->from('experience'); $this->db->where('alumni_id',$alumni_id); $query = $this->db->get(); return $query; } 2nd image is the code im using and the output is the 3rd image

jlocker Over a year ago

@Chris try my code above..I have used left join...i can't see that in your code.

Chris Over a year ago

yes i added 'left' on the join. still same result. sorry forgot to update the screenshot image

|

Aman Alam · Accepted Answer · 2015-10-10 10:32:35Z

1

Hope below mentioned function should return data that you expected.

function retrieve_all_data($alumni_id)
{
  $this->db->select('*');
  $this->db->from('experience ex');
  $this->db->join('education ed','ed.alumni_id=ex.alumni_id');
  $this->db->where('ex.alumni_id',$alumni_id);
  $query=$this->db->get();
  return $query->result_array();
}

answered Oct 10, 2015 at 10:32

Aman Alam

2469 silver badges18 bronze badges

3 Comments

Chris Over a year ago

still no data displayed.

Aman Alam Over a year ago

Please make a try catch block to see the error. function retrieve_all_data($alumni_id) { $this->db->select('*'); $this->db->from('experience ex'); $this->db->join('education ed','ed.alumni_id=ex.alumni_id'); $this->db->where('ex.alumni_id',$alumni_id); try{ $query=$this->db->get(); return $query->result_array(); }catch(Exception $e){ echo $e->getMessage(); die(); } }

Chris Over a year ago

my bad. it retrieved and displayed data. but the problem now is that the data displayed was looped 3times and i dont know why.

Collectives™ on Stack Overflow

Codeigniter JOIN multiple tables

2 Answers 2

24 Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

24 Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related