how to use join query in codeigniter
Sure! Here is an example of how to use a join query in CodeIgniter:
$this->db->select('users.name, orders.order_number');
$this->db->from('users');
$this->db->join('orders', 'users.id = orders.user_id', 'inner');
$query = $this->db->get();
Let's go through each step:
Selecting columns: First, you need to specify the columns you want to select from the tables involved in the join query. In this example, we are selecting the
name
column from theusers
table and theorder_number
column from theorders
table. You can add more columns by separating them with commas.Specifying the main table: Next, you need to specify the main table from which you want to retrieve data. In this case, the main table is
users
. You can use thefrom()
method to specify the table.Joining tables: To join tables, you can use the
join()
method. The first parameter is the name of the table you want to join, which isorders
in this example. The second parameter is the join condition, which specifies how the tables are related. In this case, we are joining theusers
table with theorders
table using the conditionusers.id = orders.user_id
. This means that we are joining the tables where theid
column in theusers
table matches theuser_id
column in theorders
table.Specifying the join type: The third parameter of the
join()
method is the join type. In this example, we are using theinner
join type, which returns only the rows where there is a match in both tables. Other join types you can use areleft
,right
, andouter
.Executing the query: Finally, you can execute the query using the
get()
method. This will return the result set that matches the join condition specified.
I hope this helps! Let me know if you have any further questions.