MySQL Order By in CodeIgniter 4 Query Builder Tutorial

Share this Article
Reading Time: 4 minutes
3,424 Views

CodeIgniter 4 provides the complete set of Query builder methods to use in querying database. Inside this article we will see the concept of MySQL Order By in codeigniter 4 Query builder.

In Query builder to use Order By we have a method available. Order by helps us to get data with a certain order like ascending or descending.

Learn More –

Let’s get started.


Create Database

To create a database, either we can create via Manual tool of PhpMyadmin or by means of a mysql command.

CREATE DATABASE codeigniter4_app;

Successfully, we have created a database.


Create Database Table

Next, we need a table. That table will be responsible to store data.

Let’s create a table with some columns.

CREATE TABLE `students` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `name` varchar(50) NOT NULL,
 `email` varchar(50) NOT NULL,
 `phone_no` varchar(15) NOT NULL,
 PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

Successfully, we have created a table.


Dump Test Data To Table

Open MySQL database and run this command to insert test data into table.

--
-- Dumping data for table `students`
--

INSERT INTO `students` (`id`, `name`, `email`, `phone_no`) VALUES
(1, 'Sanjay Kumar', 'sanjay_kumar@gmail.com', '8527419630'),
(2, 'Sanjay Mishra', 'sanjaymishra1001@yahoo.com', '7456321089'),
(3, 'Rahul Khanna', 'rahulkhanna@yahoo.com', '9632145870'),
(4, 'Vijay Singh', 'vijay_singh@gmail.com', '7896541230'),
(5, 'Suraj Lal', 'suraj_lal@outlook.com', '7894563210'),
(6, 'Dhananjay Kumar', 'dhananjay_kumar@outlook.com', '8529633217');

Here, we have few rows taking to understand the concept of orderBy() method. You can consider many more rows.


CodeIgniter 4 Order By Method

In CodeIgniter 4, orderBy() named method available for order specific.

$builder->orderBy('column_name','ASC | DESC | RANDOM')

Example #1 (DESC)

Task

We want to list all students order by name in descending order.

MySQL Query

SELECT * FROM students order by name DESC

Writing in CodeIgniter 4

We will use Query builder –

<?php

namespace App\Controllers;

class Home extends BaseController
{
	public function __construct()
	{
		$this->db = db_connect();
	}

	public function index()
	{
		$builder = $this->db->table("students");

		$builder->select('*');
		$builder->orderBy('name', 'DESC');
		$query = $builder->get();

		echo "<pre>";
		print_r($query->getResult());
      
        // Get last executed query
        // $this->db->getLastQuery();
	}
    
    //...
}

Example #2 (ASC)

Task

We want to list all students order by name in ascending order.

MySQL Query

SELECT * FROM students order by name ASC

Writing in CodeIgniter 4

$builder = $this->db->table("students");

$builder->select('*');
$builder->orderBy('name');
$query = $builder->get();

As, you can see we have used $builder->orderBy(‘name’); Optionally if we want ASC into second value, it is also Ok.

$builder = $this->db->table("students");

$builder->select('*');
$builder->orderBy('name', 'ASC');
$query = $builder->get();

Example #2 (RANDOM)

Task

We want to list all students order by name in random order. It will provide you the data in random order. It uses MySQL RAND() function.

MySQL Query

SELECT * FROM `students` ORDER BY RAND()

Writing in CodeIgniter 4

$builder = $this->db->table("students");

$builder->select('*');
$builder->orderBy('name', 'random');
$query = $builder->get();

We hope this article helped you to learn MySQL Order By in CodeIgniter 4 Query Builder Tutorial in a very detailed way.

Buy Me a Coffee

If you liked this article, then please subscribe to our YouTube Channel for PHP & it’s framework, WordPress, Node Js video tutorials. You can also find us on Twitter and Facebook.

Find More on CodeIgniter 4 here

Learn Web Development Courses @ $4 only.
Web Development Courses @ $4