How to use Join Query in CodeIgniter –



It is very useful tutorial we discusses about using join query in PHP codeigniter framework. CodeIgniter provides its own database library known as active record class and it enables you to write database independent applications. Though you can use raw SQL queries with codeigniter, sticking on to active record features is more beneficial and let you easily migrate to different database system in the future. Also it automatically escapes the values and makes for safer queries.

CodeIgniter Join Query Example
 
JOIN Queries are used to merge data from two or more database tables using a common field name. There are different types of joins available based upon the way they pull off the data from the db. For better understanding I go by example.

Sample Database Tables
Consider having three database tables ‘Category’, ‘Books’, and ‘orders’.



Table 1: Category
category_id
category_name
1
Self Development
2
Literature
3
Science

Table 2: Books
book_id
book_name
author_name
category_id
1
The 48 Laws of Power
Robert Greene
1
2
Think and Grow Rich
Napoleon Hill
1
3
The Power of Now
Eckhart Tolle
1
4
Jane Eyre
Charlotte Bronte
2

Table 3: Orders
order_id
book_id
no_copies
order_date
1
1
30
3/15/2015
2
2
25
4/22/2015

Let’s see using the join queries in codeigniter step by step



Inner Join

Inner Join or otherwise called as simple join is the most common type of joins used. It returns the set of rows that has matching values on all the joined tables. This is how we write inner join query in codeigniter.

$this->db->select('book_id, book_name, author_name, category_name');
$this->db->from('books');
$this->db->join('category', 'category.category_id = books.category_id');
$query = $this->db->get();


 // Produces SQL:
// select book_id, book_name, author_name, category_name from books join category on category.category_id = books.category_id


Output
book_id
book_name
author_name
category_name
1
The 48 Laws of Power
Robert Greene
Self Development
2
Think and Grow Rich
Napoleon Hill
Self Development
3
The Power of Now
Eckhart Tolle
Self Development
4
Jane Eyre
Charlotte Bronte
Literature


Inner Join with Condition
We can also use conditions with inner joins and it returns the combined result set that met the conditional filter provided by the ‘WHERE’ clause.

$this->db->select('book_id, book_name, author_name, category_name');
$this->db->from('books');
$this->db->join('category', 'category.category_id = books.category_id');
$this->db->where('category_name', 'Self Development');
$query = $this->db->get();

// Produces SQL:
// select book_id, book_name, author_name, category_name from books join category on category.category_id = books.category_id where category_name = "Self Development"


Output
book_id
book_name
author_name
category_name
1
The 48 Laws of Power
Robert Greene
Self Development
2
Think and Grow Rich
Napoleon Hill
Self Development
3
The Power of Now
Eckhart Tolle
Self Development


Join Multiple Tables

We can also join more than two tables (multiple join) and should make multiple calls to the join() function like this.

$this->db->select('book_id, book_name, category_name, no_copies');
$this->db->from('books');
$this->db->join('category', 'category.category_id = books.category_id');
$this->db->join('orders', 'orders.book_id = books.book_id');
$query = $this->db->get();

// Produces SQL:
// select book_id, book_name, category_name, no_copies from books  join category on category.category_id = books.category_id join orders on orders.book_id = books.book_id



Output
book_id
book_name
category_name
no_copies
1
The 48 Laws of Power
Self Development
30
2
Think and Grow Rich
Self Development
25


Left Join

The left join (or left outer join) returns all the records from the left side table along with the matching records from the right side table.

$this->db->select('book_id, book_name, author_name, no_copies, order_date');
$this->db->from('books');
$this->db->join('orders', 'orders.book_id = books.book_id', 'left');
$query = $this->db->get();


// Produces SQL:
// select book_id, book_name, author_name, no_copies, order_date from books left join category on orders.book_id = books.book_id



Output
book_id
book_name
author_name
no_copies
order_date
1
The 48 Laws of Power
Robert Greene
30
3/15/2015
2
Think and Grow Rich
Napoleon Hill
25
4/22/2015
3
The Power of Now
Eckhart Tolle
null
null


Right Join

The right join (or right outer join) returns all the records from the right side table along with the matching records from the left side table.

$this->db->select('book_id, book_name, author_name, category_name');
$this->db->from('books');
$this->db->join('category', 'category.category_id = books.category_id', 'right');
$query = $this->db->get();


// Produces SQL:
// select book_id, book_name, author_name, category_name from books
right join category on category.category_id = books.category_id



Output
book_id
book_name
author_name
category_name
1
The 48 Laws of Power
Robert Greene
Self Development
2
Think and Grow Rich
Napoleon Hill
Self Development
3
The Power of Now
Eckhart Tolle
Self Development
4
Jane Eyre
Charlotte Bronte
Literature
null
null
null
Science


Outer Join

The outer join or otherwise called as full outer join returns all the records from both the tables even they don’t have matching records. In simple words, the outer join is a combination of both left and right joins.

$this->db->select('book_id, book_name, author_name, category_name');
$this->db->from('books');
$this->db->join('category', 'category.category_id = books.category_id', 'outer');
$query = $this->db->get();


// Produces SQL:
// select book_id, book_name, author_name, category_name from books outer join category on category.category_id = books.category_id

0 comments: Post Yours! Read Comment Policy ▼
PLEASE NOTE:
We have Zero Tolerance to Spam. Chessy Comments and Comments with Links will be deleted immediately upon our review.

Post a Comment

Featured Post

UPSC various jobs last date 11th Aug-2016

Union Public Service Commission (UPSC)  Shahjahan Road, Dholpur House, New Delhi – 110069 UPSC Advertisement No. 13/2016 for various Job ...

 

Contact Form

Name

Email *

Message *

Copyright © 2014 Govtfreshjobs All Right Reserved
Designed by Owebzone | Powered by Blogger