Codeigniter Distinct Query: Select Distinct Column

CodeIgniter Distinct Query: This tutorial will show you how to write distinct query in codeigniter. Database table columns may contain duplicate values but you wish to fetch only unique (different) ones. In such cases you have to use DISTINCT keyword along with SELECT Query to eliminate duplicates and return only unique data.

With the help of Active Records distinct() function you can form select query with distinct operator in code igniter.

How to Write Distinct Query in CodeIgniter?

In CodeIgniter you can write distinct query in two ways. First with active records library function $this->db->distinct(); and the second one using the 'DISTINCT' keyword directly inside the select() function.

Here is a sample mysql table which we'll use to write distinct queries in codeignitor.

Table - Books

1. Using distinct() Method:

The method adds the keyword 'DISTINCT' to Active Records Select query. If you want to fetch unique values of a specific column then pass it as a parameter to select() function. To fetch unique rows, skip select().

$this->db->select('Category');
$this->db->distinct();
$query = $this->db->get('Books');

// Generates SQL String
// SELECT DISTINCT `Category` FROM `Books`;

Result:

2. Using DISTINCT Keyword in Select() Method:

This is an alternative way to write distinct query. In this method you can directly pass 'DISTINCT' keyword to select() like this,

$this->db->select('DISTINCT(Category)');
$query = $this->db->get('Books');

// Generates SQL String
// SELECT DISTINCT(Category) FROM `Books`;

Result:

This one is another example of codeigniter distinct query which counts the number of unique values present in a specific column/field.

$this->db->distinct();
$this->db->select('Category');
$query = $this->db->get('Books');
echo $query->num_rows();

// Outputs, 3

Also keep in mind in case you wish to fetch a distinct column together with non-distinct columns, make sure to group by the distinct column name.

$this->db->select('DISTINCT(Category), BookName');
$this->db->group_by('Category'); 
$query = $this->db->get('Books');

// Generates SQL String
// SELECT DISTINCT(Category), `BookName` FROM `Books` GROUP BY `Category`;

Likewise you can easily write distinct query in codeigniter. I hope you find this codeigniter distinct query tutorial useful. See you in the next post:)

Tweet