Join Query Using Codeigniter

By tutspointer Last updated Oct 19, 2020

In This tutorial will help you to learn join query using php codeigniter. Table joins are essential part in application development while displaying the records from multiple tables. So lets have a closer look.

Tools and Technologies

To execute Php codeigniter Application I have used below technologies.

Php Codeigniter 1.7.3
XAMPP / Php 5.2.3
Mysql 5.0.4

trn_employee table

Create two tables trn_employee and trn_address table with the help of below mysql scripts and few records in both the tables, but make sure that there should be join association between tables with common field.

CREATE TABLE  `tutspointer`.`trn_employee` (
  `employee_id` bigint(20) NOT NULL auto_increment,
  `first_name` varchar(50) collate latin1_general_ci default NULL,
  `last_name` varchar(50) collate latin1_general_ci default NULL,
  `email` varchar(30) collate latin1_general_ci NOT NULL,
  PRIMARY KEY  (`employee_id`)
);

CREATE TABLE `tutspointer`.`trn_employee` (

`employee_id` bigint(20) NOT NULL auto_increment,

`first_name` varchar(50) collate latin1_general_ci default NULL,

`last_name` varchar(50) collate latin1_general_ci default NULL,

`email` varchar(30) collate latin1_general_ci NOT NULL,

PRIMARY KEY (`employee_id`)

);

trn_address table

Address table contains employee address.

CREATE TABLE  `tutspointer`.`trn_address` (
  `address_id` int(10) unsigned NOT NULL auto_increment,
  `address_line` varchar(200) collate latin1_general_ci NOT NULL,
  `city` varchar(100) collate latin1_general_ci NOT NULL,
  `employee_id` int(10) unsigned NOT NULL,
  PRIMARY KEY  (`address_id`)
);

CREATE TABLE `tutspointer`.`trn_address` (

`address_id` int(10) unsigned NOT NULL auto_increment,

`address_line` varchar(200) collate latin1_general_ci NOT NULL,

`city` varchar(100) collate latin1_general_ci NOT NULL,

`employee_id` int(10) unsigned NOT NULL,

PRIMARY KEY (`address_id`)

);

Codeigniter Database Configuration

As you know that Php Codeigniter is popular for minimum configuration (almost Zero configuration) framework. To connect to database you need to provide username, password and database name configuration details in database.php file.

The config file is located at "application/config/database.php". Like in my case my local mysql database setting is as below.

$db['default']['hostname'] = "localhost";
$db['default']['username'] = "root";
$db['default']['password'] = "";
$db['default']['database'] = "tutspointer";

$db['default']['hostname'] = "localhost";

$db['default']['username'] = "root";

$db['default']['password'] = "";

$db['default']['database'] = "tutspointer";

Codeigniter Base Url Configuration

$config['base_url'] = "http://localhost/codeigniterexamples";

1	$config['base_url'] = "http://localhost/codeigniterexamples";

Home.php

Lets creates the Controller class called Home.php under folder name application/controllers along with default method name index().

<?php
class Home extends Controller {
 
 function Home(){
  parent::Controller();
  $this->load->model('HomeModel');
 
 }
 
 
 public function index(){
 
  $query = $this->HomeModel->getEmployees();
  $data['EMPLOYEES'] = null;
  if($query){
   $data['EMPLOYEES'] =  $query;
  }
 
  $this->load->view('index.php', $data);
 }
}

<?php

class Home extends Controller {

function Home(){

parent::Controller();

$this->load->model('HomeModel');

}

public function index(){

$query = $this->HomeModel->getEmployees();

$data['EMPLOYEES'] = null;

if($query){

$data['EMPLOYEES'] = $query;

}

$this->load->view('index.php', $data);

}

Join Syntax

$this-&gt;db-&gt;join('trn_address', 'trn_address.employee_id = trn_employee.employee_id');

1	$this->db->join('trn_address', 'trn_address.employee_id = trn_employee.employee_id');

You can specify the JOIN type also , use the third optional parameter of the function. like left, right, outer, inner, left outer, and right outer etc

$this-&gt;db-&gt;join('trn_address', 'trn_address.employee_id = trn_employee.employee_id', 'left');

1	$this->db->join('trn_address', 'trn_address.employee_id = trn_employee.employee_id', 'left');

HomeModel.php

Lets create the Model class which will query the database tables and get the records. Navigate to the folder application/models of your codeigniter application and create a new php file called HomeModel.php

Define the getEmployees() method, which reads the records from trn_employee and trn_address table.

<?php
class HomeModel extends Model {
 
 function HomeModel(){
  parent::Model();
 }
 
 function getEmployees(){
  $this->db->select("trn_employee.EMPLOYEE_ID,trn_employee.FIRST_NAME,trn_employee.LAST_NAME,trn_employee.EMAIL,trn_address.ADDRESS_LINE,trn_address.CITY");
  $this->db->from('trn_employee');
  $this->db->join('trn_address', 'trn_address.employee_id = trn_employee.employee_id');
  $query = $this->db->get();
  return $query->result();
 }
}
?>

<?php

class HomeModel extends Model {

function HomeModel(){

parent::Model();

}

function getEmployees(){

$this->db->select("trn_employee.EMPLOYEE_ID,trn_employee.FIRST_NAME,trn_employee.LAST_NAME,trn_employee.EMAIL,trn_address.ADDRESS_LINE,trn_address.CITY");

$this->db->from('trn_employee');

$this->db->join('trn_address', 'trn_address.employee_id = trn_employee.employee_id');

$query = $this->db->get();

return $query->result();

}

index.php

Lets create index.php file under folder name application/views. Here we are going to display the employee and address information.

<!DOCTYPE html>
<html lang="en">
 <head>
  <title>codeigniterexamplesDisplay Records From Database Using Codeigniter</title>
  <link href="<?= base_url();?>css/bootstrap.css" rel="stylesheet">
 </head>
 <body>
  <div class="row">
   <div style="width:600px;margin:50px;">
    <h4>Join Table Example Using Codeigniter</h4>
    <table class="table table-striped table-bordered">
     <tr><td><strong>Employee Id</strong></td><td><strong>First Name</strong></td><td><strong>Last Name</strong></td><td><strong>Email</strong></td><td><strong>Address</strong></td><td><strong>City</strong></td></tr> 
     <?php foreach($EMPLOYEES as $employee){?>
     <tr><td><?=$employee->EMPLOYEE_ID;?></td><td><?=$employee->FIRST_NAME;?></td><td><?=$employee->LAST_NAME;?></td><td><?=$employee->EMAIL;?></td><td><?=$employee->ADDRESS_LINE;?></td><td><?=$employee->CITY;?></td></tr>     
        <?php }?>  
    </table>
   </div> 
  </div> 
 </body>
</html>

<!DOCTYPE html>

<head>

<title>codeigniterexamplesDisplay Records From Database Using Codeigniter</title>

</head>

<body>

<h4>Join Table Example Using Codeigniter</h4>

<tr><td><strong>Employee Id</strong></td><td><strong>First Name</strong></td><td><strong>Last Name</strong></td><td><strong>Email</strong></td><td><strong>Address</strong></td><td><strong>City</strong></td></tr>

<?php foreach($EMPLOYEES as $employee){?>

<tr><td><?=$employee->EMPLOYEE_ID;?></td><td><?=$employee->FIRST_NAME;?></td><td><?=$employee->LAST_NAME;?></td><td><?=$employee->EMAIL;?></td><td><?=$employee->ADDRESS_LINE;?></td><td><?=$employee->CITY;?></td></tr>

<?php }?>

</table>

</div>

</body>

</html>

Run

Enter the url http://localhost/codeigniterexamples in your browser, It will display the employee details