Introduction

This tutorial will show you CodeIgniter join example. We will use CodeIgniter’s Query Builder Class to build join queries for fetching data from multiple tables.

The SQL Join clause is used to combine records together from two or more tables in a database.

A Join locates related column values in two or more tables. This will display all the records that have matched.

Join and Inner Join are functionally equivalent, but INNER JOIN can be a bit clearer to read, especially if the query has other join types (i.e. LEFT or RIGHT or CROSS) included in it. Here we will see Join or Inner Join example.

We will use CodeIgniter’s $this->db->join() that permits you to write the Join portion of your query.

You can read also Codeigniter Full Outer Join Example ,  Codeigniter Right Outer Join Example and Codeigniter Left Outer Join Example

Prerequisites

Apache HTTP Server 2.4, CodeIgniter 3.1.10, PHP 7.3.5, MySQL 8.0.17

Example on Join Query

Suppose we have blogs table with below data:

sql join

And we have blog_comments table with below data:

sql join

On executing below query:

SELECT blogs.blog_id,comment_id,blog_title,blog_content,blog_date,comment_text,comment_date
 FROM blogs JOIN blog_comments ON blog_comments.blog_id = blogs.blog_id;

or

SELECT blogs.blog_id,comment_id,blog_title,blog_content,blog_date,comment_text,comment_date
 FROM blogs INNER JOIN blog_comments ON blog_comments.blog_id = blogs.blog_id;

We will get below results:

join query result

Creating Project Directory

It’s assumed that you have setup Apache 2.4, PHP 7.3.5 and Codeigniter 3.1.10 in Windows system.

Now we will create a project root directory called codeIgniter-join under the Apache server’s htdocs folder.

Now move all the directories and files from CodeIgniter 3.1.10 framework into  codeIgniter-join directory.

We may not mention the project root directory in subsequent sections and we will assume that we are talking with respect to the project’s root directory.

Configuring Database

Configure your MySQL database to work with your CodeIgniter application.

'hostname' => 'localhost',
'username' => 'root',
'password' => 'root',
'database' => 'roytuts',
'dbdriver' => 'mysqli',

Configuring Auto-load

We will configure database library and helpers to auto-load to avoid load every time we need to use such things in the application.

Update the below lines in the file application/config/autoload.php.

$autoload['libraries'] = array('database');
$autoload['helper'] = array('url', 'file');

Creating Model Class

We will perform database operation to fetch data using join query so we will create model class.

We will use CodeIgniter’s Query builder class to build the join query.

Create a file JoinModel.php under application/models folder with the below source code.

<?php

/**
 * Description of JoinModel
 *
 * @author https://www.roytuts.com
 */
class JoinModel extends CI_Model {

    private $blogs = 'blogs';   // blog table
    private $blog_comments = 'blog_comments';   // blog comment table

    function __construct() {
        parent::__construct();
    }

    function join() {
        // Produces:
        // SELECT * FROM blogs JOIN blog_comments ON blog_comments.blog_id = blogs.blog_id
        //$this->db->select('*');
        //$this->db->from($this->blogs);
        //$this->db->join($this->blog_comments, $this->blog_comments . 'blog_id = ' . $this->blogs . 'blog_id');
        //or
        //$this->db->join($this->blog_comments, $this->blog_comments . 'blog_id = ' . $this->blogs . 'blog_id', 'inner');
        //$query = $this->db->get();
        
        // Produces:
        // SELECT blogs.blog_id,comment_id,blog_title,blog_content,blog_date,comment_text,comment_date
        //    FROM blogs JOIN blog_comments ON blog_comments.blog_id = blogs.blog_id
        $this->db->select($this->blogs . '.blog_id,comment_id,blog_title,blog_content,blog_date,comment_text,comment_date');
        $this->db->from($this->blogs);
        $this->db->join($this->blog_comments, $this->blog_comments . '.blog_id = ' . $this->blogs . '.blog_id');
        //or
        //$this->db->join($this->blog_comments, $this->blog_comments . 'blog_id = ' . $this->blogs . 'blog_id', 'inner');
        $query = $this->db->get();
        return $query->result();
    }

}

Creating Controller Class

We will create controller class to handle request/response for clients. This controller class will communicate between model class and the view file.

Create a file JoinController.php under application/controllers folder with below source code.

<?php

defined('BASEPATH') OR exit('No direct script access allowed');

class JoinController extends CI_Controller {
	
	function __construct() {
        parent::__construct();
        $this->load->model('joinmodel');
    }
	
	public function index()	{
		$data['blogs'] = $this->joinmodel->join();
        $this->load->view('join_view', $data);
	}
}

Creating View File

Create a view file join_view.php under application/views which will be used to display blogs and associated comments for blogs.

<?php
defined('BASEPATH') OR exit('No direct script access allowed');
?><!DOCTYPE html>
<html lang="en">
<head>
	<meta charset="utf-8">
	<title>Welcome to CodeIgniter Join Example</title>

	<style type="text/css">

	::selection { background-color: #E13300; color: white; }
	::-moz-selection { background-color: #E13300; color: white; }

	body {
		background-color: #fff;
		margin: 40px;
		font: 13px/20px normal Helvetica, Arial, sans-serif;
		color: #4F5155;
	}

	a {
		color: #003399;
		background-color: transparent;
		font-weight: normal;
	}

	h1 {
		color: #444;
		background-color: transparent;
		border-bottom: 1px solid #D0D0D0;
		font-size: 19px;
		font-weight: normal;
		margin: 0 0 14px 0;
		padding: 14px 15px 10px 15px;
	}

	code {
		font-family: Consolas, Monaco, Courier New, Courier, monospace;
		font-size: 12px;
		background-color: #f9f9f9;
		border: 1px solid #D0D0D0;
		color: #002166;
		display: block;
		margin: 14px 0 14px 0;
		padding: 12px 10px 12px 10px;
	}

	#body {
		margin: 0 15px 0 15px;
	}

	p.footer {
		text-align: right;
		font-size: 11px;
		border-top: 1px solid #D0D0D0;
		line-height: 32px;
		padding: 0 10px 0 10px;
		margin: 20px 0 0 0;
	}

	#container {
		margin: 10px;
		border: 1px solid #D0D0D0;
		box-shadow: 0 0 8px #D0D0D0;
	}
	</style>
</head>
<body>

<div id="container">
	<h1>CodeIgniter Join Example</h1>            
	<div id="body">
		<?php
		foreach ($blogs as $blog) {
			?>
			<div class="post">
				<h2 class="title"><?php echo $blog->blog_title; ?></h2>
				<p class="meta">
					<?php
					echo $blog->blog_date;
					?>
				<div class="entry">
					<p><?php echo $blog->blog_content; ?></p>
				</div>
			</div>
		<p>    <strong>Comment on <?php echo $blog->comment_date; ?> :</strong> <?php echo $blog->comment_text; ?></p>
			<?php
		}
		?>
	</div>
</div>

</body>
</html>

Configuring Route

Now modify application/config/routes.php file for configuring route with our controller class.

$route['default_controller'] = 'JoinController';

Testing the Application

Make sure your Apache 2.4 server is running and codeigniter join application is deployed.

Hit the URL http://localhost/codeIgniter-join/index.php, you will see below output on the browser:

codeigniter join example

Source Code

You can download source code.

Thanks for reading.

Tags:

1 thought on “Codeigniter Join Example

Leave a Reply

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