Hi, today I have come up with another useful tutorial which 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 (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_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 |
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_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 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_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 |
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_id | book_name | category_name | no_copies |
---|---|---|---|
1 | The 48 Laws of Power | Self Development | 30 |
2 | Think and Grow Rich | Self Development | 25 |
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_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 |
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_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.
Read Also:
$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
- How to Send Email in CodeIgniter using Gmail Server
- Easy Image and File Uploading in CodeIgniter with Validations
And that explains about using join queries in codeigniter. I hope you like this post. Meet you in another interesting tutorial. Good day!