CODEWITHSUNDEEP
phpmysqlcodeigniter
JoeKincognito
JoeKincognito

Reputation: 371

CodeIgniter SELECT with 2 JOINS - how to get the correct id

The Problem Summary: I am doing a join from 3 tables using CodeIgniter's Active Record Class. All 3 tables have 'ID' as their primary key. How do I get the correct ID to output? I tried to figure out how to do an alias in the select statement but couldn't get it to work.

The Code:

(Controller)

public function index()
{
$data['call_reports'] = $this->call_reports_model->get_call_reports();
$data['title'] = 'All Call Reports';
$this->load->view('call_reports/templates/header', $data);
$this->load->view('call_reports/index', $data);
$this->load->view('call_reports/templates/footer');
}

(Model)

public function get_call_reports()
{
$this->db->select('*');
$this->db->from('call_reports');
$this->db->join('employees', 'employees.ID=call_reports.employee_ID');
$this->db->join('customers', 'customers.ID=call_reports.customer_ID');
$query = $this->db->get();
return $query->result_array();
}

(index)

<?php foreach($call_reports as $call_reports_item): ?>
<p><?php echo $call_reports_item['name'] ?></p> <!—Result: name from employee table -->
<p><?php echo $call_reports_item['CUSNM'] ?></p> <!—Result: name from customer table -->
<?php echo $call_reports_item['ID'] ?> ***<!—RESULTS IN EMPLOYEE ID -->***
<?php endforeach ?>

The Problem Line of Code:

<?php echo $call_reports_item['ID'] ?> ***<!—RESULTS IN EMPLOYEE ID -->***

I want this to be the call report ID not the employee ID. How do I fix?

Thanks in advance for your help.

Upvotes: 1

Views: 197

Answers (2)

Jakub
Jakub

Reputation: 20475

This is just standard SQL:

$this->db->from('call_reports cr');

Then do selects of variables you want:

$this->db->select('cr.id, ...');

and you would reference the value as cr.id in your result. For each table just add an alias after it (keep it simple, like cr, e, or c (customers).

Enjoy

Edit
Based on your response, you could also use method chaining to reduce using $this->db-> for each line. The catch is it makes your code less readable, but for simple calls it is just fine: 

$this->db->select('cr.ID,cr.call_date,cr.objective,cr.comments,c.CBRCD,e.name,c.‌​CUSNM');
$this->db->from('call_reports cr')->join('employees e', 'e.ID=cr.employee_ID')->join('customers c', 'c.ID=cr.customer_ID');

I've left it on 2 lines (could do it on 1), but you get the idea.

Upvotes: 1

raj
raj

Reputation: 817

in your select try: 

$this->db->select('employees.*, customers.*, call_reports.*');

and then use:

$call_reports_item['call_reports.ID']

Upvotes: 0

Related Questions