Join Table Example Using Codeigniter

Join Query Using Codeigniter

By Yashwant Chavan, Views 47616, Date 05-Oct-2014

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.

tags codeigniter codeigniter-table-joins

Popular Codeigniter Tutorials

Tools and Technologies

To execute Php codeigniter Application I have used below technologies.

  1. Php Codeigniter 1.7.3
  2. XAMPP / Php 5.2.3
  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  `technicalkeeda`.`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  `technicalkeeda`.`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`)
);

I am using Codeigniter version # 1.7.3 and Eclipse to develop this sample application. Your codeigniter project looks like below once you setup the application in eclipse.

Display records using codeigniter eclipse setup

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'] = "technicalkeeda";

Codeigniter Base Url Configuration

$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);
 }
}
?>

Join Syntax

$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->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();
 }
}
?>

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>

Run

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

codeigniter join query example
Yashwant

Yashwant

Hi there! I am founder of technicalkeeda.com and programming enthusiast. My skills includes Java,J2EE, Spring Framework, Nodejs, PHP and lot more. If you have any idea that you would want me to develop? Lets connect: yashwantchavan[at][gmail.com]