CodeIgniter Limit Query

In code igniter, you have to use active records $this->db->limit(); function to generateLIMIT clause. It should be combined with select query to restrict the number of records returned from the database.

$this->db->limit(10, 5);

The limit() method takes up two parameters - the first one defines the number of records to be fetched and the second is the offset value.

The above statement produces the string, 'LIMIT 5, 10' which returns 10 records starting right from the 5th record.

The second param Offset is optional, so you can simply ignore and pass to limit(), just the no. of records you want the query to return.

For example, this codeigniter limit query will return the first 50 records from the 'Students' table.

$this->db->select('*');
$this->db->from('Students');
$this->db->limit(50);
$query = $this->db->get();

// Produces SQL
// SELECT * FROM Students LIMIT 50;

Limit Query with Order By Clause:

Function limit() can be combined with other sql clauses like where(), like(), group_by(), order_by() etc. Below example shows a limit query which returns the name of the top 10 students who has scored highest marks.

$this->db->select('Name');
$this->db->from('Students');
$this->db->order_by('Total', 'desc');
$this->db->limit(10);
$query = $this->db->get();

// Produces SQL
// SELECT Name FROM Students ORDER BY Total DESC LIMIT 10;

Codeigniter Limit Query with Offset:

Here is another example using LIMIT with offset and LIKE clause.

$this->db->select('*');
$this->db->from('Students');
$this->db->like('Name', 'S');
$this->db->limit(20, 10);
$query = $this->db->get();

// Produces SQL
// SELECT * FROM Students WHERE Name LIKE "%S%" LIMIT 10, 20;

Alternative Way: LIMIT Without limit() Function

There is an alternative way to write limit query in codeigniter. You can limit the number of rows returned without using limit() function itself. Instead you have to pass the no. of rows & offset as second and third parameters to $this->db->get(); function. Obviously the first param should be the table name.

$this->db->get('Students', 30, 10);

// Produces SQL
// SELECT * FROM Students LIMIT 10, 30;

Also Read: CodeIgniter Pagination with Search Filter Example

That was all about codeigniter limit queries. I hope all the above query examples help you to understand limit query in codeigniter. Meet you in the next post :)