How to INNER JOIN 3 tables using CodeIgniter?

Today, We want to share with you inner join codeigniter.In this post we will show you codeigniter subquery in join, hear for left join in codeigniter we will give you demo and example for implement.In this post, we will learn about Join Query In Codeigniter Example Tutorial with an example.

CodeIgniter Inner Join Query Example

CodeIgniter Inner Join Query:

$this->db->select('MemberID, MemberName, CartName, GSTNO');
$this->db->from('Members');
$this->db->join('Cart', 'Cart.CartID = Members.CartID');
$query = $this->db->get();

// Produces SQL
// SELECT MemberID, MemberName, CartName, GSTNO FROM Members JOIN Cart on Cart.CartID = Members.CartID;

Multiple Inner Join Query in CodeIgniter:

$this->db->select('MemberID, MemberName, LevelName, CartName, GSTNO');
$this->db->from('Members');
$this->db->join('Level', 'Level.LevelID = Members.LevelID');
$this->db->join('Cart', 'Cart.CartID = Members.CartID');
$query = $this->db->get();

// Produces SQL
// SELECT MemberID, MemberName, LevelName, CartName, GSTNO FROM Members JOIN Level ON Level.LevelID = Members.LevelID JOIN Cart ON Cart.CartID = Members.CartID;

CodeIgniter Inner Join Query with Where Clause:

$this->db->select('MemberID, MemberName, LevelName, CartName, GSTNO');
$this->db->from('Members');
$this->db->join('Level', 'Level.LevelID = Members.LevelID');
$this->db->join('Cart', 'Cart.CartID = Members.CartID');
$this->db->where('LevelName', 'Self Development');
$query = $this->db->get();

// Produces SQL
// SELECT MemberID, MemberName, LevelName, CartName, GSTNO FROM Members JOIN Level ON Level.LevelID = Members.LevelID JOIN Cart ON Cart.CartID = Members.CartID WHERE LevelName = 'Self Development';

CodeIgniter Inner Join Query Example:

Create MySQL Database:

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

INSERT INTO `Level` (`LevelID`, `LevelName`) VALUES
(1, 'Self Development'),
(2, 'Literature'),
(3, 'Science');

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

INSERT INTO `Cart` (`CartID`, `CartName`) VALUES
(1, 'Robert Greene'),
(2, 'Napoleon Hill'),
(3, 'Eckhart Tolle'),
(4, 'Charlotte Bronte');

CREATE TABLE IF NOT EXISTS `Members` (
  `MemberID` int(8) NOT NULL AUTO_INCREMENT,
  `MemberName` varchar(100) NOT NULL,
  `LevelID` int(8) NOT NULL,
  `CartID` int(8) NOT NULL,
  `GSTNO` varchar(30) NOT NULL,
  PRIMARY KEY (`MemberID`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

INSERT INTO `Members` (`MemberID`, `MemberName`, `LevelID`, `CartID`, `GSTNO`) VALUES
(1, 'The 48 Laws of Power', 1, 1, 'GSTNO-13: 825-1491918661'),
(2, 'Think and Grow Rich', 1, 2, 'GSTNO-13: 825-0321784070'),
(3, 'The Power of Now', 1, 3, 'GSTNO-13: 825-1449363758'),
(4, 'Jane Eyre', 2, 4, 'GSTNO-13: 825-1449392772');

Joindemo_model.php

<?php
class joindemo_model extends CI_Model
{
    function __construct()
    {
        parent::__construct();
    }
    
    //fetch members
    function getMembers()
    {
        $this->db->select('MemberID, MemberName, LevelName, CartName, GSTNO');
        $this->db->from('Members');
        $this->db->join('Level', 'Level.LevelID = Members.LevelID');
        $this->db->join('Cart', 'Cart.CartID = Members.CartID');
        $query = $this->db->get();
        return $query->result();
    }
}
?>

application/controllers/Joindemo.php

<?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['members'] = $this->joindemo_model->getMembers();           
        $this->load->view('joindemo_view',$data);
    }
}
?>

application/views/Joindemo_view.php

<!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>Member Name</th>
                        <th>Level Name</th>
                        <th>Cart Name</th>
                        <th>GSTNO</th>
                    </tr>
                </thead>
                <tbody>
                <?php for ($i=0;$i<count($members);$i++) { ?>
                <tr>
                    <td><?php echo ($i+1); ?></td>
                    <td><?php echo $members[$i]->MemberName; ?></td>
                    <td><?php echo $members[$i]->LevelName; ?></td>
                    <td><?php echo $members[$i]->CartName; ?></td>
                    <td><?php echo $members[$i]->GSTNO; ?></td>
                </tr>
                <?php } ?>
                </tbody>
            </table>
        </div>
    </div>
</div>
</body>
</html>

I hope you get an idea about inner join codeigniter.
I would like to have feedback on my infinityknow.com blog.
Your valuable feedback, question, or comments about this article are always welcome.
If you enjoyed and liked this post, don’t forget to share.