如何用CodeIgniter查询构造器限制结果集？分页查询异常求助

Hey there, let's get your pagination working right and break down how to properly limit results with CodeIgniter's query builder.

First: Fixing Your Broken Limit Query #

Looking at your code, you’ve got the right idea for calculating the offset ($start), but you’re missing the correct limit() method call—plus the line you commented out was written incorrectly. Here’s the corrected version of your query:

if ($this->input->get('page')) { 
    $start = abs(($this->input->get('page', TRUE)-1)*$config['per_page']); 
} else { 
    $start = 0; 
}

$condition = array();
if (preg_match('/WMV/', $whatFind)) { 
    $condition = array('registerId' => $whatFind); 
}

// Corrected query with proper limit() usage
$results = $this->db
    ->group_by('registerId')
    ->order_by('id', 'desc')
    ->limit($config['per_page'], $start) // This is the key fix!
    ->get_where('tbl_applicants', $condition); // Assumed full table name + added condition parameter

The mistake here was twofold:

1. You had the limit line commented out
2. Even if uncommented, you used ->($start, $config['per_page']) instead of CodeIgniter’s built-in ->limit() method.

How to Use CodeIgniter’s Query Builder for Limiting Results #

CodeIgniter’s query builder makes limiting results straightforward with the limit() method. Here’s how it works:

Basic Syntax

$this->db->limit($number_of_rows, $offset);

• $number_of_rows: The total number of records you want to retrieve (e.g., your $config['per_page'] value)
• $offset: Optional parameter for which record to start at (calculated as (page_number - 1) * per_page for pagination)

Common Examples

• Get the first 10 records from a table:

  $query = $this->db->limit(10)->get('your_table_name');
  

• Get 15 records starting from the 30th record (for page 3, if per page is 15):

  $query = $this->db->limit(15, 30)->get('your_table_name');
  

Pro Tips for Pagination #

Don’t forget to set the total number of records in your pagination config—this ensures the pagination links are generated correctly:

// Count total matching records (use count_all_results to avoid running two separate queries)
$config['total_rows'] = $this->db->count_all_results('tbl_applicants', $condition);
// Initialize pagination
$this->pagination->initialize($config);

Make sure the limit() method is always placed before your get() or get_where() call—otherwise it won’t be applied to the query.

内容的提问来源于stack exchange，提问作者Joynal Abedin