How to use Join Query in CodeIgniter

Send Email Codeigniter Login Registration Join Query CodeIgniter Interview Questions

Today I am sharing How to use Join Query in Codeigniter. This is Best Question answer solution for Codeigniter Developer and Who is learning Codeigniter. this is very useful tutorial which discusses about using join query in php codeigniter framework. 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.

Also Read : How to Remove Index.php from URL in Codeigniter

Here is Step for Join Query in CodeIgniter

JOIN Queries (or JOIN Statements) are used to combine 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_idcategory_name
1Self Development
2Literature
3Science

Table 2: Books

book_idbook_nameauthor_namecategory_id
1The 48 Laws of PowerRobert Greene1
2Think and Grow RichNapoleon Hill1
3The Power of NowEckhart Tolle1
4Jane EyreCharlotte Bronte2

Table 3: Orders

order_idbook_idno_copiesorder_date
11303/15/2015
22254/22/2015

Now let’s see about using the join queries in codeigniter one by one.

CodeIgniter 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_idbook_nameauthor_namecategory_name
1The 48 Laws of PowerRobert GreeneSelf Development
2Think and Grow RichNapoleon HillSelf Development
3The Power of NowEckhart TolleSelf Development
4Jane EyreCharlotte BronteLiterature

Inner Join with Condition

We can also use conditions with inner joins and it returns the combined resultset 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_idbook_nameauthor_namecategory_name
1The 48 Laws of PowerRobert GreeneSelf Development
2Think and Grow RichNapoleon HillSelf Development
3The Power of NowEckhart TolleSelf Development

Codeigniter Multiple Join

We can also join more than two tables (multiple join) and should make multiple calls to the join() function. This is how we write multiple join query in codeigniter.

$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_idbook_namecategory_nameno_copies
1The 48 Laws of PowerSelf Development30
2Think and Grow RichSelf Development25

CodeIgniter 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. Here is how we write left join query in codeigniter.

$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_idbook_nameauthor_nameno_copiesorder_date
1The 48 Laws of PowerRobert Greene303/15/2015
2Think and Grow RichNapoleon Hill254/22/2015
3The Power of NowEckhart Tollenullnull

CodeIgniter 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. Here is the example for writing right 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', '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_idbook_nameauthor_namecategory_name
1The 48 Laws of PowerRobert GreeneSelf Development
2Think and Grow RichNapoleon HillSelf Development
3The Power of NowEckhart TolleSelf Development
4Jane EyreCharlotte BronteLiterature
nullnullnullScience

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

Leave a Reply