How to use joins in codeIgniter

joins in codeigniter

We always retrieve data from a single table, but what happens if you want to retrieve the data from multiple tables.

In other words, if you’re going to combine two tables, how can you use the left join in Codeigniter or the right join in Codeigniter, we discuss that how can you do this using CodeIgniter.

Today’s I am going to show you how to use joins in ci, so here is Codeigniter joins.

In SQL you always write your query like this SELECT * from users INNER JOIN comments ON users.u_id =  comments.user_id , if there is a match between the columns in both tables users.u_id =  comments.user_id because you are using the INNER JOIN keyword.

This is a Users table

u_id fname lname email
1 Shakzee Ahmed info@shakzee.com
2 Sidra Ali slidra@hotmail.com

 

This is a comments table

c_id comment created user_id
111 This is my first comment 2016-09-27 11:53:12 1
223 Sidra 2016-09-27 11:53:12 3
355 Waqar 2016-09-27 11:53:12 3

Now you can write a query in CodeIgniter.
Note: $this->db->select(‘*’) means you are getting all columns from both tables

this->db->select('*');
$this->db->from('users');
$this->db->join('comments','comments.user_id = users.u_id');
$this->db->get();

This is chaining (in Codeigniter) means you can chaining multiple methods by using a single statement.

$this->db->select('*')
->from('users')
->join('comments','comments.user_id = users.u_id')
->get();

If you want to use left outer join OR left join, just pass the third parameter in join() function/method using Codeigniter.

$this->db->select('*')
->from('users')
->join('comments','comments.user_id = users.u_id','left')//this is the left join in codeigniter
->get();

If you want to use the right outer join in Codeigniter OR right join in Codeigniter, just pass the third parameter in join() function/method.

$this->db->select('*')
->from('users')
->join('comments','comments.user_id = users.u_id','right')//this is the right join in codeigniter
->get();

If you want to join more than two tables, how can you do that it’s simple, in this example, I am using the third table named city.

$this->db->select('*')
->from('users')
->join('comments','comments.user_id = users.u_id')
->join('city','city.user_id = users.u_id')
->get();

You can also use WHERE clause

$this->db->select('*')
->from('users')
->where('users.u_id',1)
->join('comments','comments.user_id = users.u_id')
->join('city','city.user_id = users.u_id')
->get();

joins/multiple joins with a group by in Codeigniter

$this->db->query("select table1.ud_id,table1.ud_date,table1.up_lat_profit_date,table1.ud_currency
                  ,sum(table1.ud_amount*table3.dp_percentage/100) as profit
               from table1
               JOIN `table2` ON `table2`.`deposit_id` = `table1`.`ud_id`
               JOIN `table3` ON `table3`.`dp_id` = `table2`.`daily_profit_id`
               where `table1`.`ud_status` = 1
               AND `table3`.`dp_status` = 1
               AND `table1`.`user_plan_id` = $up_id
               AND table2.pr_status = 1
               group by table1.ud_id");

 

Conclusion

We always use joins to fetch the data from multiple tables; managing joins in SQL is a difficult task, but working in Codeigniter, it’s straightforward to use the joins either. It’s a left join, or it’s a right join in Codeigniter.
We just need to call a method in Codeigniter $this->db->join(‘table’.’condition’,’left,right’);. Joins take three parameters first is the table, second is the condition, and the third is the left join or the right join in Codeigniter.

shakzee
administrator
My Name is Shehzad Ahmed and I am a full-time developer with over 7+ years of experience. In that time, I’ve worked as a developer with many big and small firms and was teaching web design and development skills to many keen learners and by the time passes I’ve found a great passion and potential on myself as a teacher on this particular field and now I am a full-time teacher and working as a freelancer.

Leave a Reply

Your email address will not be published. Required fields are marked *