Join Query Using Codeigniter
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.
1 2 3 4 5 6 7 |
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.
1 2 3 4 5 6 7 |
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.
1 2 3 4 |
$db['default']['hostname'] = "localhost"; $db['default']['username'] = "root"; $db['default']['password'] = ""; $db['default']['database'] = "tutspointer"; |
Codeigniter Base Url Configuration
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().
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 |
<?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
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
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.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
<?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.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 |
<!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