CodeIgniter Order By Query Example

CodeIgniter Order By Query: In SQL queries, ORDER BY clause/keyword provides a way to sort the resultset in ascending or descending order based upon columns/fields. The resultset can be sorted using one or more columns too. Most of the database systems sort the records in ascending order by default if not specified. Here we'll see how to write order by query in codeigniter framework to sort the database records.

To sort query resultset in codeigniter you have to use order_by() method from 'Active Record Class' library. The method takes up two parameters, first one is the table column name and second one is optional and specifies if the column values should be sorted in ascending or decending order.

order_by(column_name, sort_type);

CodeIgniter Order By Query Example:

Here let's see how to write order by query in codeigniter using order_by() function. Below is a sample db table we'll be using for writing order_by queries.

MySQL Table: Books

Below is a simple order_by query in codeigniter which pulls off the data from the table 'Books' and sort the result set by 'BookName' field in alphabetical order. Also note that the query doesn't specify if 'BookName' should be sorted in ascending or descending order. But by default MySQL DB sorts data in ascending order so the column will be sorted alphabetically.

$this->db->select('*');
$this->db->from('tbl_Books');
$this->db->order_by('BookName');
$query = $this->db->get();
return $query->result();

// Produces SQL
// SELECT * FROM tbl_Books ORDER BY BookName;

Resultset:

You can skip the statement $this->db->select('*'); unless you want to select only specific columns/fields.

The above query can be shortened like this in codeignitar,

$this->db->order_by('BookName');
$query = $this->db->get('tbl_Books');
return $query->result();

// Produces SQL
// SELECT * FROM tbl_Books ORDER BY BookName;

CodeIgniter Order By Asc:

This codeigniter order by query explicitly specifies to sort the resultset using 'Author' column in ascending order. To sort a column by ascending order use asc as second parameter in order_by() method.

$this->db->select('*');
$this->db->from('tbl_Books');
$this->db->order_by('Author', 'asc');
$query = $this->db->get();
return $query->result();

// Produces SQL
// SELECT * FROM tbl_Books ORDER BY Author asc;

Resultset:

CodeIgniter Order By Desc:

To sort a field in descending order you have to use desc keyword. The below codeigniter order by query example sorts the above resultset by author in reverse alphabetical order.

$this->db->select('*');
$this->db->from('tbl_Books');
$this->db->order_by('Author', 'desc');
$query = $this->db->get();
return $query->result();

// Produces SQL
// SELECT * FROM tbl_Books ORDER BY Author desc;

Resultset:

CodeIgniter Order By Two Columns:

You can also sort records using more than one column. This code igniter query sorts the table 'Books' with two columns, first in descending order of 'Category' and then ascending order of 'BookName'.

$this->db->select('*');
$this->db->from('tbl_Books');
$this->db->order_by('Category', 'desc');
$this->db->order_by('BookName', 'asc');
$query = $this->db->get();
return $query->result();

// Produces SQL
// SELECT * FROM tbl_Books ORDER BY Category desc, BookName asc;

Resultset:

To set order by clause for two columns either you can use order_by() method twice (like above) or pass the parameters as a single string.

$this->db->order_by('Category desc, BookName asc');

Method Chaining in CodeIgniter:

If you think above query has so many lines, then simply shorten it using method chaining in codeigniter. But keep in mind this works only with PHP 5 or above.

$this->db->select('*')->from('tbl_Books')->order_by('Category desc, BookName asc');
$query = $this->db->get();
return $query->result();

// Produces SQL
// SELECT * FROM tbl_Books ORDER BY Category desc, BookName asc;

CodeIgniter Order By Limit:

You can also control the number of rows returned by the query using LIMIT clause. Function limit() can be used along with order_by() like this,

$this->db->select('*');
$this->db->from('tbl_Books');
$this->db->order_by('Category', 'asc');
$this->db->limit(3, 1);
$query = $this->db->get();
return $query->result();

// Produces SQL
// SELECT * FROM tbl_Books ORDER BY Category ASC LIMIT 1, 3;

Resultset:

You can shorten the above codeigniter sql query like this without using the limit() function.

$this->db->order_by('Category', 'asc');
$query = $this->db->get('tbl_Books', 3, 1);
return $query->result();

// Produces SQL
// SELECT * FROM tbl_Books ORDER BY Category ASC LIMIT 1, 3;

That was all about working with codeigniter order by query. I hope you find all the above examples of order by query in codeigniter useful. Please let me know your queries through comments.

Tweet