So, I am going to show you how to use a join query in Codeigniter. Also, retrieve the data as you want with example.
Here, we have defined many ways to use join query with where condition.
As well as, fetch data multiple methods like left join, right join, full join, and inner join in detail.
Therefore, I am using the example of multiple tables with the multiple join query in Codeigniter.
public function getdata(){
$this->db->select('*');
$this->db->from('table1'); // this is first table name
$this->db->join('table2', 'table2.id = table1.id'); // this is second table name with both table ids
$query = $this->db->get();
return $query->result();
}Table of Contents
Join Query in Codeigniter
Hence, we display some method and full code with the using join query using the table.
So, the retrieved data code with joins table using Codeigniter step by step in the below section.
Step 1: Model ( join query in Codeigniter with example )
class UserModel extends CI_Model{
public function getdata(){
$this->db->select('*');
$this->db->from('user');
$this->db->join('vendor', 'vendor.userid = user.id');
$query = $this->db->get();
return $query->result();
}
}This is a model file and created a class for displaying joins query working in this model class.
Thus, there have used 2 tables to fetch the data from the database. Therefore, staring apply select query and created two tables in the MySQL database.
Similarly, you can apply to join two or three tables using Codeigniter according to this code.
Now we showing the next step for the controller with the function example in Codeigniter. After that explain left, right, and full joins.
Step 2: controller ( Including class and function )
defined('BASEPATH') OR exit('No direct script access allowed');
class Welcome extends CI_Controller {
public function index()
{
$this->load->database();
$this->load->model('UserModel');
$data['users'] = $this->UserModel->getdata();
$this->load->view('user', $data);
}
}
Here, I am fetching the model from the model page and store a whole data a variable $data.
Still, created a key which name is user and pass on view section with all data in array form.
Firstly, you loaded a database from this function we define above section $this->load->database().
Then again, the model load is the right way. So, the last line for view part load file and pass a variable of the whole array as we define apply to join query $this->load->view(‘user’, $data).
Step 3: View ( Here are showing table and insert data in Codeigniter )
<!DOCTYPE html>
<html>
<head>
<title>User Data with Project</title>
</head>
<body>
<h1>This is Join conditions Output</h1>
<table border="2px solid #000 ">
<?php foreach ($users as $userdata) {
// echo "<pre>";
// print_r($userdata);
?>
<tr>
<td><?php echo $userdata->name; ?></td>
<td><?php echo $userdata->address; ?></td>
</tr>
<?php } ?>
</table>
</body>
</html>After all, created a Html structure in view file and getting a variable.
Then, take the Foreach loop including an array variable. After that, the print view in a table forms multiple data.
Output: View page data in table form retrieve from the database using MVC Codeigniter.
Left Join in Codeigniter
Similarly, apply code join but including a left join query. At the same time, given an example that how to use left join in the model file using Codeigniter.
public function getdata(){
$this->db->select('*');
$this->db->from('table1'); // this is first table name
$this->db->join('table2', 'table2.id = table1.id','left'); // this is second table name with both table ids
$query = $this->db->get();
return $query->result();
}Here, you do that’s a simple example. So, we have to use 2 tables can do more than multiple tables and multiple joins. likewise, two, three, and 4 tables as so on.
Right Join in Codeigniter
As much as, add right join condition in the table. in the below section given an example add right join query in Codeigniter.
public function getdata(){
$this->db->select('*');
$this->db->from('table2'); // this is first table name
$this->db->join('table3', 'table3.id = table2.id','right'); // this is second table name with both table ids
$query = $this->db->get();
return $query->result();
}Note: If you want to do a simple example whole Codeigniter setup and step by step install file structure. Also, learn the basic project in this Codeigniter tutorial
Where Conditions
Here, you can apply ‘where condition‘ with the join query. Also, given model page code to created where conditions getting data use id from the database.
public function FetchData(){
$this->db->select('*');
$this->db->->where('user.id',1);
$this->db->from('user'); // this is first table name
$this->db->join('vendor', 'vendor.id = user.id'); // this is second table name with both table ids
$query = $this->db->get();
return $query->result();
}Multiple Joins in Codeigniter
In case, this is a simple way to apply multiple joins using Codeigniter.
Therefore, I have created a query in which join added multiple times with condition. you can saw in below code section.
public function multidata(){
$this->db->select('*');
$this->db->from('table1');
$this->db->join('table', 'table2.id = table1.id');
$this->db->join('table2', 'table2.id = table3.id');
$this->db->join('table4', 'table4.id = table3.id');
$query = $this->db->get();
return $query->result();
}Conclusion
Finally, we describe whole ways to use join query in Codeigniter to retrieve data from the database.
As well as, if you have any issues regarding this ask us. Also, check this simple example of How to Store and Retrieve Image from database in Php
this is great example i used very helpful thanks for it
Thanks…