CodeIgniter Limit Query Example

Codeingiter Limit Query: Limit clause is used in sql queries to limit the number of rows returned from the query. Fetching large tables with several hundreds of records will impact on performance - besides it's not an easy task for the users to go through such a huge list of records at a time. So your best bet would be to create pagination or multi page results where users will be shown limited set of records on one go.

Limit queries are quite useful for this sort of tasks and we'll see here how to write limit query in codeigniter.

CodeIgniter Limit Query Example:

In code igniter, you have to use active records $this->db->limit(); function to generate LIMIT 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 :)

Tweet