CodeIgniter Left Join Query Example

CodeIgniter Left Join Query: The LEFT JOIN is sometimes called as LEFT OUTER JOIN and is performed on two database tables - the first table (on the left side) is referred as LEFT TABLE and and the second (on the right side) one as RIGHT TABLE. The LEFT JOIN returns all the rows from the LEFT TABLE along with the matching rows on the RIGHT TABLE. In this post, let's see how to write left join query in codeigniter with example.

CodeIgniter Left Join Query

In codeigniter we have to make use of 'Active Records' library to form database queries. There is a method called join() which is used for writing join queries. It takes up the form,

...

$this->db->from(table1);
$this->db->join(table2, table2.columnname = table1.columname', 'left');

The parameter 'left' represents that the join to be performed on the tables should be a left join.

How to Write Left Join Query in CodeIgniter?

Consider the below two mysql tables. Let us use them for writing left join query.

Table: Books

Table: Orders

This is how we write left join query in code igniter.

$this->db->select('Books.BookID, BookName, NumCopies, OrderDate');
$this->db->from('Books');
$this->db->join('Orders', 'Orders.BookID = Books.BookID', 'left');
$query = $this->db->get();

// Produces SQL
SELECT Books.BookID, BookName, NumCopies, OrderDate FROM Books LEFT JOIN Orders ON Orders.BookID = Books.BookID;

Resultset:

Note: The table 'Books' contains two foreign keys (CategoryID & AuthorID). You can check for those parent tables Category & Author here.

CodeIgniter Left Join Query Example

Here is the actual implementation of the above left join query which is used to fetch records from the database and display it in a html table in codeignitor.

Create MySQL DB:

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');

CREATE TABLE IF NOT EXISTS `Orders` (
  `OrderID` int(8) NOT NULL AUTO_INCREMENT,
  `BookID` int(8) NOT NULL,
  `NumCopies` int(6) NOT NULL,
  `OrderDate` date NOT NULL,
  PRIMARY KEY (`OrderID`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;
INSERT INTO `Orders` (`OrderID`, `BookID`, `NumCopies`, `OrderDate`) VALUES
(1, 1, 50, '2016-03-14'),
(2, 2, 100, '2016-03-28');

Joindemo_model.php

This is the codeigniter model file for the left join demo. Place it inside 'application/models' folder.

<?php
class joindemo_model extends CI_Model
{
    function __construct()
    {
        parent::__construct();
    }

    function getBookOrders()
    {
        $this->db->select('Books.BookID, BookName, NumCopies, OrderDate');
        $this->db->from('Books');
        $this->db->join('Orders', 'Orders.BookID = Books.BookID', 'left');
        $query = $this->db->get();
        return $query->result();
    }
}
?>

Joindemo.php

This is the codeigniter controller file for the join example. Place this 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->getBookOrders();
        $this->load->view('joindemo_view',$data);
    }
}
?>

Joindemo_view.php

This is the codeigniter view file which contains the user interface for the join demo. It uses Twitter Bootstrap CSS for front-end designing. Place the file inside 'application/views' folder.

<!DOCTYPE html>
<html>
<head>
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <title>CodeIgniter Left Join Query Example</title>
    <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>Num. of Copies</th>
                        <th>Order Date</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]->NumCopies) ? $books[$i]->NumCopies : "NIL");?></td>
                    <td><?php echo (($books[$i]->OrderDate) ? $books[$i]->OrderDate : "NIL"); ?></td>
                </tr>
                <?php } ?>
                </tbody>
            </table>
        </div>
    </div>
</div>
</body>
</html>

Now run the above controller and you can see all the records from the 'Books' table along with their order details (from 'Orders') displayed in a html table. If the orders are not placed for the books then it will be shown as NIL.

Also Read: Codeigniter Inner Join Query Example

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

Tweet