SELECT with LIMIT and OFFSET in Codeigniter

SELECT with LIMIT and OFFSET in Codeigniter

  1. Blog
  2. 15 November 2026
  3. 4 min read

Stefan Bogdanescu

Founder & Senior Architect

SELECT with LIMIT and OFFSET in CodeIgniter

When working with pagination or list views in a legacy CodeIgniter application, you often encounter behavior where a database query returns results only when explicit limit and offset parameters are provided. If both arguments are null, the resulting dataset might be empty or return zero rows, even though the underlying table contains data. This isn't a bug in your logic; it is a consequence of how the CodeIgniter Active Record class handles pagination parameters and how SQL interprets them.

The Default Behavior of LIMIT NULL

In standard SQL, SELECT * FROM nation LIMIT 0, 10 works perfectly fine. However, when you pass null to the CodeIgniter DB driver, the framework does not simply omit the LIMIT clause from the generated query. Instead, it translates null into 0.

If your controller calls $this->Nation_model->nationList() without arguments, both $limit and $start are null. The Active Record class processes these values and generates a SQL statement equivalent to:

SELECT nation.id, nation.name_it AS name 
FROM nation 
ORDER BY name ASC 
LIMIT 0, 0

In many database engines, specifically MySQL versions prior to 5.7, LIMIT 0, 0 is interpreted as "return zero rows." Even in modern versions, if the offset is treated as a count of rows to skip before returning results, skipping all rows (offset 0) with a limit of 0 yields an empty set. This explains why your function returns an empty array when called without arguments.

Handling Null Parameters Gracefully

To solve this without creating a second function or adding excessive conditional logic inside the model, you need to normalize the input parameters before passing them to the database driver. The goal is to ensure that if pagination is not required, the query should retrieve all matching records rather than zero rows.

You can achieve this by defaulting null values to specific integers that bypass the strict pagination behavior. For retrieving all data, you typically want no limit. For offsetting, if no pagination is requested, the offset should be 0 (skip nothing). However, since LIMIT 0 forces an empty result in some contexts, you must ensure the limit is only applied when explicitly needed.

Here is how you can refactor the model function to handle this efficiently:

public function nationList($limit = null, $start = null)
{
    // Determine which language column to use based on session
    $languageColumn = ($this->session->userdata('language') == 'it') ? 'name_it' : 'name_en';
    
    $this->db->select('nation.id, nation.' . $languageColumn . ' as name');
    $this->db->from('nation');
    $this->db->order_by('name', 'asc');

    // Handle LIMIT: Only apply if a specific limit is requested.
    // If $limit is null, do not set the limit clause to avoid empty results.
    if ($limit !== null) {
        $this->db->limit($limit, $start);
    } else {
        // Ensure we don't accidentally set LIMIT 0 if start was null.
        // We simply omit the limit clause entirely.
        $this->db->limit(NULL, NULL); 
    }

    $query = $this->db->get();
    
    // Convert result object to array for easier consumption
    return is_object($query) ? array_values((array)$query->result()) : [];
}

The Trade-off: Query Performance and Readability

While the previous snippet uses limit(NULL, NULL) to clear the clause, a more robust approach in modern CodeIgniter versions (3.1.0+) involves checking if the limit property is actually set before executing. However, the most direct fix for your specific scenario—where passing null results in zero rows—is to ensure that the LIMIT clause is only appended when the user explicitly requests pagination.

If you prefer a cleaner implementation that avoids modifying the Active Record properties directly based on null checks, you can adjust the logic to treat a null limit as "no limit" and a null start as "start from 0".

public function nationList($limit = null, $start = null)
{
    if ($this->session->userdata('language') == 'it') {
        $this->db->select('nation.id, nation.name_it as name');
    } elseif ($this->session->userdata('language') == 'en') {
        $this->db->select('nation.id, nation.name_en as name');
    } else {
        // Fallback for default language or unexpected states
        $this->db->select('nation.id, nation.name_it as name');
    }

    $this->db->from('nation');
    $this->db->order_by('name', 'asc');

    // Only apply pagination if specific values are provided.
    // If limit is null, we skip the LIMIT clause entirely to fetch all rows.
    if ($limit !== null) {
        $this->db->limit($limit, (is_numeric($start) ? $start : 0));
    }

    $query = $this->db->get();
    
    return is_object($query) ? array_values((array)$query->result()) : [];
}

Why This Works

The core issue was that null was being converted to 0, which created a LIMIT 0, 0 scenario. By wrapping the limit() call in an if ($limit !== null) check, you ensure that:

  1. Full Dataset: When called without arguments, no LIMIT clause is added, and all rows are returned.
  2. Paginated Dataset: When called with $limit = 10, the LIMIT clause is applied correctly, regardless of whether a start offset is provided.

This approach keeps your model clean, avoids duplicating functions for "with pagination" and "without pagination," and adheres to the principle of least surprise: calling the function without arguments should naturally return the full list.

If you are migrating from CodeIgniter to a newer stack like Laravel, you will find that their query builder handles null limits differently by default, often ignoring them gracefully. However, in legacy CI projects, explicit checks remain necessary to prevent empty result sets caused by implicit zero conversions.

Our network