How to Use Join Query In CodeIgniter Example

By | December 24, 2016

In this post we are going to see how to use Join Query in CodeIgniter. We have covered two examples one which joins two tables in codeigniter and another with three tables. In our previous tutorial we covered ajax registration in codeigniter. We generally use JOINS concept when we need to fetch records from multiple tables .

Lets check how to use join query in codeigniter

Database

CREATE DATABASE joins;

Tables

We have three tables Student, Subject and Marks.

Student Table


CREATE TABLE `student` (
`ID` INT(10),
`first_name` varchar(50)
);

Subject Table


CREATE TABLE `subject` (
`ID` INT(10),
`subject` varchar(50),
`SID` INT(10)
);

Marks Table


CREATE TABLE `marks` (
`MID` INT(10),
`marks` varchar(50),
`subject_id` INT(10)
);

Make sure you change base URL to $config[‘base_url’] = “http://localhost/codeigniter”;

Join Query Syntax in Codeigniter

$this->db->join('subject', 'student.ID = subject.ID');

Use third parameter to specify type of join it could be inner join, outer join, left or right.

Join Query In CodeIgniter Example

Student.php (Controller)

How to JOIN TWO tables in Codeigniter


$this->db->select("student.ID,student.first_name,subject.subject");
$this->db->from('student');
$this->db->join('subject', 'student.ID = subject.ID');
$query = $this->db->get();

$data['qry'] = $query;
$this->load->view("student_view",$data);

codeigniter join

How to JOIN Three tables in Codeigniter

 $this->db->select("student.ID,student.first_name,subject.subject,marks.marks");
 $this->db->from('student');
 $this->db->join('subject', 'student.ID = subject.ID');
 $this->db->join('marks', 'marks.MID = subject.ID');
 $query = $this->db->get(); 
 
 $data['qry'] = $query;
 $this->load->view("student_view",$data); 

Join Query In CodeIgniter

Complete Code


<?php
class Student extends CI_Controller {

function __construct()
{
parent::__construct();
$this->load->helper(array('form', 'url'));
}

//index function
function index()
{
//load file upload form
$this->fetch_data();
}
public function show_details()
{
//it fetches data from 3 tables
$this->fetch_more_data();

}
//How to JOIN TWO tables in Codeigniter
function fetch_data()
{

//FETCHING DATA FROM TWO TABLES STUDENT AND SUBJECT

$this->db->select("student.ID,student.first_name,subject.subject");
$this->db->from('student');
$this->db->join('subject', 'student.ID = subject.ID');
//specify third parameter inner to use inner join eg - $this->db->join('subject', 'student.ID = subject.ID','inner');
$query = $this->db->get();

$data['qry'] = $query;
$this->load->view("student_view",$data);

}
//How to JOIN three tables in Codeigniter
function fetch_more_data()
{

//FETCHING DATA FROM three TABLES STUDENT SUBJECT and marks

$this->db->select("student.ID,student.first_name,subject.subject,marks.marks");
$this->db->from('student');
$this->db->join('subject', 'student.ID = subject.ID');
$this->db->join('marks', 'marks.MID = subject.ID');
$query = $this->db->get();

$data['qry'] = $query;
$this->load->view("student_view",$data);

}

}
?>

student_view.php (View)


<!DOCTYPE html>
<html>
<head>
<title></title>

<link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/css/bootstrap.min.css">

<script src="https://ajax.googleapis.com/ajax/libs/jquery/3.1.1/jquery.min.js"></script>

<script src="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/js/bootstrap.min.js"></script>

<style>

#main{

position: absolute;
top:200px;
left:550px;
width: 220px;
padding: 10px;
border: 3px solid green;
margin: 1;

}

</style>

</head>
<body>

<nav class="navbar navbar-default">

<div class="container-fluid">
<!-- Brand and toggle get grouped for better mobile display -->

<div class="navbar-header">
<button type="button" class="navbar-toggle collapsed" data-toggle="collapse" data-target="#bs-example-navbar-collapse-1" aria-expanded="false">
<span class="sr-only">Toggle navigation</span>
<span class="icon-bar"></span>
<span class="icon-bar"></span>
<span class="icon-bar"></span>
</button>
<a class="navbar-brand" href="#">TechPlugg Dashboard</a>
</div>

<ul class="nav navbar-nav navbar-right">
</ul>

</div>

<!-- /.navbar-collapse -->
</div>

<!-- /.container-fluid -->
</nav>

<center>

<h1>Welcome to TechPlugg</h1>

We are a Tech Blog with Programming tutorials

</center>

<div id ="main">



<?php

echo '<table class="table table-condensed">';
echo '<tr>';
echo '<th>ID</th>';
echo'<th>First name</th>';
echo'<th>Subject</th>';

echo '</tr>';
foreach ($qry->result() as $row) {

echo "<tr>";
echo "<td>$row->ID</td>";
echo "<td>$row->first_name</td>";
echo "<td>$row->subject</td>";

echo "</tr>";

}
echo '<table>';
?>

</div>
</body>
</html>

To View Details enter http://localhost/codeignter/index.php/Student on your browser. If you like this post share it with your friends.