Skip to content

How to JOIN Tables in Codeigniter

Last updated on February 10, 2018

I am gonna use CodeIgniter’s active record class to generate Join Queries in this tutorial. Because by using AR class we can write complex joins very easily and a major benefit to using the Active Record features is that it allows you to create database independent applications since the query syntax is generated by each database adapter. It also allows for safer queries, since the values are escaped automatically by the system.

Codeigniter JOIN Query

we use join() method to join the 2 or more tables in codeigniter.

Normal Join

$this->db->select('*');
$this->db->from('articles');
$this->db->join('category', 'category.id = articles.id');

$query = $this->db->get();

// Produces:
// SELECT * FROM articles JOIN category ON category.id = articles.id

Join With Condition

$this->db->select('*');
$this->db->from('articles');
$this->db->join('category', 'category.id = articles.id');
$this->db->where(array('category.id' => 10));

$query = $this->db->get();

// Produces:
// SELECT * FROM articles JOIN category ON category.id = articles.id where category.id = 10

Join with multiple tables

You need to call join() method multiple times if you need several joins in one query.

$this->db->select('*');
$this->db->from('articles');
$this->db->join('category', 'category.id = articles.id');
$this->db->join('comments', 'comments.id = articles.id');

$query = $this->db->get();

// Produces:
// SELECT * FROM articles
// JOIN category ON category.id = articles.id
// JOIN comments ON comments.id = articles.id

If you need a specific type of JOIN you can specify it via the third parameter of the function. Options are: left, right, outer, inner, left outer, and right outer.

Join with specific type (left, right, outer, inner, left outer, and right outer)

You need to call join() method multiple times if you need several joins in one query.

$this->db->select('*');
$this->db->from('articles');
$this->db->join('category', 'category.id = articles.id','left');
$this->db->join('comments', 'comments.id = articles.id','left');

$query = $this->db->get();

// Produces:
// SELECT * FROM articles
// LEFT JOIN category ON category.id = articles.id
// LEFT JOIN comments ON comments.id = articles.id

Do share it and let your friend’s know about it.

3.3 4 votes
Article Rating
Subscribe
Notify of
guest

18 Comments
Most Voted
Newest Oldest
Inline Feedbacks
View all comments