Codeigniter Full Outer 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 purpose of a join is to combine the data across tables.
A join is actually performed by the where clause which combines the specified rows of tables.
If a join involves in more than two tables then first two tables are joined based on the join condition and then compares the result with the next table and so on.

Here we will see Full Outer Join example. Outer join gives the non-matching records along with matching records. Full Outer Join will display the all records from both tables.

MySQL does not support Full Outer Join but we will simulate full outer join using Union query. Codeigniter’s Active Record does not support Union query so we will use db->query() to get results from MySQL.

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


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.

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

Example on Full Outer 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

We will get below results
union results

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

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

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

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

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

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

Step 7. Now if everything is fine run the application, you will see below output in the browser. You will see all records from both tables in the browser.
full outer join

Thanks for reading.

Soumitra Roy Sarkar

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

Leave a Reply

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