Safe Pagination with CodeIgniter’s Query Builder

Problem: fetching large tables safely and efficiently

When a web page needs to show a slice of rows from a table—say, the latest 20 blog posts—you must avoid pulling the whole table into memory, protect against SQL injection, and keep the code usable if you later change the database engine. Writing raw SQL for each controller method quickly becomes repetitive and error‑prone.

Thesis: the Query Builder gives a chainable, escaped, driver‑agnostic way to paginate

CodeIgniter’s $this->db object provides methods like select(), from(), where(), limit() and get(). Chaining them builds the SQL statement while automatically escaping any values you pass. The limit($per_page, $offset) call adds the pagination clause, so you only fetch the needed rows.

Worked example: paginated article list

Assume a fresh CodeIgniter project (v3 or v4) with the database library loaded. The controller method below shows a typical pattern.

public function articles($page = 1)
{
    // 1️⃣ Load the DB library (auto‑loaded in config/autoload.php is fine)
    $this->load->database();

    // 2️⃣ Pagination settings
    $per_page = 10;
    $offset   = ($page - 1) * $per_page;

    // 3️⃣ Build the query
    $this->db->select('id, title, created_at');
    $this->db->from('articles');
    $this->db->where('status', 'published');          // value is escaped automatically
    $this->db->like('title', $this->input->get('q'));   // search term also escaped
    $this->db->order_by('created_at', 'desc');
    $this->db->limit($per_page, $offset);               // adds LIMIT … OFFSET

    // 4️⃣ Execute
    $query = $this->db->get();

    // 5️⃣ Optional: inspect the generated SQL for debugging
    // log_message('debug', $this->db->last_query());

    // 6️⃣ Return results as objects (could be array or custom class)
    $data['articles'] = $query->result();
    $data['pagination'] = $this->create_pagination_links($page, $per_page, $this->db->count_all_results('articles'));

    $this->load->view('articles/index', $data);
}

Where to run: Place this method in a controller (e.g., Articles.php) inside application/controllers/ (v3) or app/Controllers/ (v4). No special permissions are needed beyond the usual web‑server access to the framework.

Expected checks: After a request, you can view the log or call $this->db->last_query() to see something like:

SELECT `id`, `title`, `created_at` FROM `articles` WHERE `status` = 'published' AND `title` LIKE '%search%' ORDER BY `created_at` DESC LIMIT 10 OFFSET 20

Notice that the user‑supplied search term appears inside quotes and is escaped by the driver, confirming protection against injection.
Switching database drivers without rewriting queries
Because the Query Builder abstracts the SQL syntax, you can change $db['default']['dbdriver'] in application/config/database.php (v3) or app/Config/Database.php (v4) from mysqli to postgre, for example, and the same controller code works unchanged. The builder translates limit() and like() to the appropriate native syntax for each driver.
Trade‑off: debugging chained calls
While chaining improves readability, forgetting to reset the builder between unrelated queries can cause subtle bugs—especially in long‑running scripts or libraries that share the same $this->db instance. The builder retains the previous select(), where(), etc., unless you explicitly call $this->db->reset_select() or start a fresh instance with $this->db->table('new_table') (v4).
Practical way to check: After each logical block, log $this->db->last_query() and verify it contains only the clauses you intended. If you see extra conditions, add a reset call before the next build.
Actionable closing
Adopt the pattern shown above for any list that needs pagination:

Set $per_page and compute $offset from the page number.
Chain select(), from(), where()/like(), order_by(), and finally limit($per_page, $offset).
Execute with $get() and inspect $this->db->last_query() during development.
Reset the builder if you reuse the same DB object for another unrelated query.
Test driver swaps by editing the config and running your suite; the same code should continue to work.

This approach gives you safe, efficient pagination while keeping your SQL generation centralized and portable across MySQL, PostgreSQL, SQLite, and other drivers supported by CodeIgniter.