phpmysqlcodeigniterlimitquery-builder

How to pass offset and limit parameters to CodeIgniter's limit()

I'm doing some database queries using CodeIgniter's query builder and I need to paginate the results. I am using limit() like this:

$this->db->limit($pPagination['Start'], $pPagination['Length']);

but I don't get any results.

Using $this->db->last_query(); it seems that CodeIgniter produces the following SQL.

SELECT *
FROM (`viw_contacts`)
WHERE `user_id` = '1'
ORDER BY `contact_name` asc
LIMIT 0 OFFSET 15

which when I run it inside PHPMyAdmin also returns 0 rows.

But if I modify it an run:

SELECT *
FROM (`viw_contacts`)
WHERE `user_id` = '1'
ORDER BY `contact_name` asc
LIMIT 0, 15

Then I get the correct results.

Why does CodeIgniter generate this SQL the way it does and how can I fix it?

I use CodeIgniter 1.7.3 and MySQL 5.1.41

Solution

  • Ok, found the issue.

    It's 

    $this->db->limit($pPagination['Length'], $pPagination['Start']);

    instead of

    $this->db->limit($pPagination['Start'], $pPagination['Length']);

    First param is the Length and second is the offset, not the other way around as i thought.