Codeigniter Join Example

This tutorial will show you how we can 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.

Read also Codeigniter Full Outer Join Example ,  Codeigniter Right Outer Join Example and Codeigniter Left Outer Join Example

Prerequisites

Netbeans 8.1
XAMPP in Windows
Codeigniter 3.0.6


Configure XAMPP and Netbeans

From Netbeans IDE go to Tools->Options. Click on PHP. Now on tab “General” browse the file for “PHP 5 Interpreter”. The php interpreter file generally placed inside the <physical drive in Windows OS>:xamppphpphp.exe

Configure Codeigniter and Netbeans

Create a new PHP project in Netbeans. Then remove the index.php file from the newly created project. Now copy the extracted files from Codeigniter 3.0.6 to the newly created project directory.

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

Directory Structure

A typical directory structure for the project would be as shown below. Here assests directory will contain static resources like css, js, images.
codeigniter join

Step 1. Now modify <root directory>/application/config/autoload.php file for auto-loading html, url, file, form and session

$autoload['helper'] = array('html', 'url', 'file', 'form');

$autoload['libraries'] = array('database', 'session');

Step 2. Now modify <root directory>/application/config/config.php file to define encryption key

$config['encryption_key'] = '2d8+e6]K0?ocWp7&`K)>6Ky"|.x|%nuwafC~S/+6_mZI9/17y=sKKG.;Tt}k';

Step 3. Create a view file under <root directory>/application/views which will be used to display blogs

<!DOCTYPE html>
<html>
    <head>
        <meta charset="UTF-8">
        <title>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;
            }            

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

            #container {
                margin: 10px;
                border: 1px solid #D0D0D0;
                box-shadow: 0 0 8px #D0D0D0;
            }

            h1 {
                margin-left: 15px;
            }

            .error {
                color: #E13300;
            }

            .success {
                color: darkgreen;
            }
        </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>&nbsp;&nbsp;&nbsp;&nbsp;<strong>Comment on <?php echo $blog->comment_date; ?> :</strong> <?php echo $blog->comment_text; ?></p>
                    <?php
                }
                ?>
            </div>
        </div>
    </body>
</html>

Step 4. Create a Controller class under <root directory>/application/controllers for handling client’s request and response

<?php

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

/**
 * Description of Blog
 *
 * @author https://www.roytuts.com
 */
class Blog extends CI_Controller {

    function __construct() {
        parent::__construct();
        $this->load->model('blogmodel');
    }

    public function index() {
        $data['blogs'] = $this->blogmodel->join();
        $this->load->view('blog_join', $data);
    }

}

Step 5. Create a Model class under <root directory>/application/models for performing database operatins

<?php

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

/**
 * Description of BlogModel
 *
 * @author https://www.roytuts.com
 */
class BlogModel 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();
    }

}

Step 6. Now modify <root directory>/application/config/routes.php file for pointing the default controller class

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

Step 7. Now if everything is fine run the application, you will see below output in the browser

codeigniter join

Download MySQL script

blogs

Thanks for reading.

Soumitra Roy Sarkar

I am a professional Web developer, Enterprise Application developer, Software Engineer and Blogger. Connect me on JEE Tutorials Twitter Facebook  Google Plus Linkedin Or Email Me

Leave a Reply

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