[Solved] Bug in Pagination with Oracle

[eluser]nebulom[/eluser]
Though I think it's not related with the Pagination class, I think it's with the construction of the Oracle db driver. I'm not familiar with CodeIgniter's core as I'm a newbie with this framework but I echoed the $sql in the query and it is

Code:

SELECT *
  FROM (SELECT inner_query.*, ROWNUM rnum
          FROM (SELECT *
                  FROM "WWW_USER_LEVEL_NAMES"
                 WHERE "SYSTEM_ID" = 27) inner_query
         WHERE ROWNUM < 4)
WHERE rnum >= 2

This should be the second page of 2 per page. I have a query that is working fine and it has this

Code:

select *
            from (
                select a.*, rownum r
                from (
                    select * from www_user_level_names where system_id = 27
                ) a
                where rownum <= :higherbound
            )
            where r > (:higherbound - :xlimit)

Where higherbound is 4 and limit is 2 per page. Guys please check as I found the first query to select rows 2 and 3 where it should be selecting 3 and 4. Thanks a lot in advance.

[eluser]nebulom[/eluser]
I tried modifying oci8_driver's

Code:

function _limit($sql, $limit, $offset)
    {
        $limit = $offset + $limit;
        $newsql = "SELECT * FROM (select inner_query.*, rownum rnum FROM ($sql) inner_query WHERE rownum < $limit)";

        if ($offset != 0)
        {
            $newsql .= " WHERE rnum >= $offset";
        } . . .

to

Code:

function _limit($sql, $limit, $offset)
    {
        $limit = $offset + $limit;
        $newsql = "SELECT * FROM (select inner_query.*, rownum rnum FROM ($sql) inner_query WHERE rownum <= $limit)";

        if ($offset != 0)
        {
            $newsql .= " WHERE rnum > $offset";
        } . . .

And it worked. Hopefully this helps someone. Thanks a lot.

[eluser]Unknown[/eluser]
wew,
thx a lot.
it's very useful for me.

but, i get some issues after that.
When i use pagination, although i navigate through the ‘per_page’ number of records and shown on correctly, but the pagination link only and always highlights the first page link.

can you help me?

[eluser]DogWin[/eluser]
thanks!
it's very useful for me too.
Maybe the following will work too.

$query=$this->odb->get('tb',$per_page,$page);
return $query->result();