▼

CodeIgniter Inner Join Query Example

CodeIgniter Inner Join Query: Inner Join picks up the rows from both the tables using common column data. In other words it takes up the cross product of two (or more) tables and selects all rows that contain matching values in their common field. Inner join is also called as simple join and has several variations to it. In this post, we'll see about forming inner join query in codeigniter with proper example. Since Codeigniter is an MVC one should follow a specific pattern in query formation.

CodeIgniter Inner Join Query:

Codeigniter has a library called Active Record Class for building database queries. Although the framework accepts plain old sql queries, it’s a good practice to stick on to the MVC standards and keep the application database independent. Let's see how to write codeigniter join queries with some examples.

Sample MySQL Database:

These are the three sample tables we are going to use for the rest of the tutorial.

Table: Category

table-category

Table: Author

table-author

Table: Books

table-books

Creating Inner Join Query in CodeIgniter:

$this->db->join(); should be used to join tables in codeigniter. If you want to join more than two tables then you have to use extra $this->db->join(); - one for each join resp.

$this->db->select('BookID, BookName, AuthorName, ISBN');
$this->db->from('Books');
$this->db->join('Author', 'Author.AuthorID = Books.AuthorID');
$query = $this->db->get();

// Produces SQL
// SELECT BookID, BookName, AuthorName, ISBN FROM Books JOIN Author on Author.AuthorID = Books.AuthorID;

Join Query Resultset

inner-join-query-in-codeigniter

Multiple Inner Join Query in CodeIgniter:

Here I have inner joined three tables, so used $this->db->join(); two times to form the query.

$this->db->select('BookID, BookName, CategoryName, AuthorName, ISBN');
$this->db->from('Books');
$this->db->join('Category', 'Category.CategoryID = Books.CategoryID');
$this->db->join('Author', 'Author.AuthorID = Books.AuthorID');
$query = $this->db->get();

// Produces SQL
// SELECT BookID, BookName, CategoryName, AuthorName, ISBN FROM Books JOIN Category ON Category.CategoryID = Books.CategoryID JOIN Author ON Author.AuthorID = Books.AuthorID;

Multiple Join Query Resultset

codeigniter-multiple-inner-join-query-example

CodeIgniter Inner Join Query with Where Clause:

Here is another inner join query with condition filter.

$this->db->select('BookID, BookName, CategoryName, AuthorName, ISBN');
$this->db->from('Books');
$this->db->join('Category', 'Category.CategoryID = Books.CategoryID');
$this->db->join('Author', 'Author.AuthorID = Books.AuthorID');
$this->db->where('CategoryName', 'Self Development');
$query = $this->db->get();

// Produces SQL
// SELECT BookID, BookName, CategoryName, AuthorName, ISBN FROM Books JOIN Category ON Category.CategoryID = Books.CategoryID JOIN Author ON Author.AuthorID = Books.AuthorID WHERE CategoryName = 'Self Development';

Join Query with Where Clause Resultset

codeigniter-join-query-with-where-clause

CodeIgniter Inner Join Query Example:

Now let us see the actual implementation of inner join in codeigniter. Below is a codeigniter demo which uses the inner join query to pull-off the records from mysql database and display it in html table.

Create MySQL Database:

CREATE TABLE IF NOT EXISTS `Category` (
  `CategoryID` int(8) NOT NULL AUTO_INCREMENT,
  `CategoryName` varchar(30) NOT NULL,
  PRIMARY KEY (`CategoryID`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

INSERT INTO `Category` (`CategoryID`, `CategoryName`) VALUES
(1, 'Self Development'),
(2, 'Literature'),
(3, 'Science');

CREATE TABLE IF NOT EXISTS `Author` (
  `AuthorID` int(8) NOT NULL AUTO_INCREMENT,
  `AuthorName` varchar(40) NOT NULL,
  PRIMARY KEY (`AuthorID`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

INSERT INTO `Author` (`AuthorID`, `AuthorName`) VALUES
(1, 'Robert Greene'),
(2, 'Napoleon Hill'),
(3, 'Eckhart Tolle'),
(4, 'Charlotte Bronte');

CREATE TABLE IF NOT EXISTS `Books` (
  `BookID` int(8) NOT NULL AUTO_INCREMENT,
  `BookName` varchar(100) NOT NULL,
  `CategoryID` int(8) NOT NULL,
  `AuthorID` int(8) NOT NULL,
  `ISBN` varchar(30) NOT NULL,
  PRIMARY KEY (`BookID`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

INSERT INTO `Books` (`BookID`, `BookName`, `CategoryID`, `AuthorID`, `ISBN`) VALUES
(1, 'The 48 Laws of Power', 1, 1, 'ISBN-13: 978-1491918661'),
(2, 'Think and Grow Rich', 1, 2, 'ISBN-13: 978-0321784070'),
(3, 'The Power of Now', 1, 3, 'ISBN-13: 978-1449363758'),
(4, 'Jane Eyre', 2, 4, 'ISBN-13: 978-1449392772');

Run above sql commands in mysql db and make sure you have the required db and tables in place before moving to the coding part.

Joindemo_model.php

Next create the model file for handling database transactions inside 'application/models' folder.

<?php
class joindemo_model extends CI_Model
{
    function __construct()
    {
        parent::__construct();
    }
    
    //fetch books
    function getBooks()
    {
        $this->db->select('BookID, BookName, CategoryName, AuthorName, ISBN');
        $this->db->from('Books');
        $this->db->join('Category', 'Category.CategoryID = Books.CategoryID');
        $this->db->join('Author', 'Author.AuthorID = Books.AuthorID');
        $query = $this->db->get();
        return $query->result();
    }
}
?>

Joindemo.php

Then create the controller file inside 'application/controllers' folder.

<?php
class joindemo extends CI_Controller {
            
    public function __construct()
    {
        parent::__construct();
        $this->load->helper('url');
        $this->load->database();
        $this->load->model('joindemo_model');
    }

    public function index()
    {
        $data['books'] = $this->joindemo_model->getBooks();           
        $this->load->view('joindemo_view',$data);
    }
}
?>

Joindemo_view.php

Finally create view file inside 'application/views' folder. This comprises of the user interface (html) required for our demo.

<!DOCTYPE html>
<html>
<head>
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <title>CodeIgniter Inner Join Query Example</title>
    <!--load twitter bootstrap css-->
    <link rel="stylesheet" href="<?php echo base_url("assets/bootstrap/css/bootstrap.css"); ?>">
</head>
<body>
<div class="container">
    <div class="row">
        <div class="col-md-12">
            <table class="table table-striped table-hover">
                <thead>
                    <tr>
                        <th>#</th>
                        <th>Book Name</th>
                        <th>Category Name</th>
                        <th>Author Name</th>
                        <th>ISBN</th>
                    </tr>
                </thead>
                <tbody>
                <?php for ($i=0;$i<count($books);$i++) { ?>
                <tr>
                    <td><?php echo ($i+1); ?></td>
                    <td><?php echo $books[$i]->BookName; ?></td>
                    <td><?php echo $books[$i]->CategoryName; ?></td>
                    <td><?php echo $books[$i]->AuthorName; ?></td>
                    <td><?php echo $books[$i]->ISBN; ?></td>
                </tr>
                <?php } ?>
                </tbody>
            </table>
        </div>
    </div>
</div>
</body>
</html>

Now run the controller and it will produce an html table with all the records from the books table along with category name and author name.

codeigniter-inner-join-query-example

Also Read: Codeigniter Left Join Query Example

That was all about working with codeigniter inner join query. I hope you find this tutorial and example useful. Please let me know your queries through comments.

Posted by on

No comments:

Post a Comment