Pagination with join

[eluser]Bainzy[/eluser]
Hey guys,

little stuck here at the moment what I am trying to do is create a forum using codeigniter. Now I got the basic forum working using a few queries I am trying to make the code neater and more effiient. what I have is the forum home page here I used two tables to contain my data a "topics" table and a "category" table.

On the forum home screen I use two queries 1 to return the following information :

TopicName,
CreatedBy,
Topics,
LastPost,

then I would use a second query to retreive the following from the category table

CategoryName

Now I modified my code to do the following using a join query

function fetchForumJoinRow()
{

$this->db->select('*');
$this->db->from('topics');
$this->db->join('category', 'category.CategoryID = topics.CategoryID');

$query = $this->db->get();
return $query->result_array();
}

now this code works fine for getting me the category name for use on the home page in a single query but how can I now add the pagination into this because whe I try it forms a error. ?

[eluser]flaky[/eluser]

Code:

function fetchForumJoinRow($limit=10, $offset=0)
{

$this->db->select(’*’);
$this->db->from(‘topics’);
$this->db->join(‘category’, ‘category.CategoryID = topics.CategoryID’);
$this->db->limit($limit, $offset);

$query = $this->db->get();
return $query->result_array();
}

[eluser]Unknown[/eluser]

Code:

SELECT studinfo.STUDID, studinfo.NAME, program.PROGNAME, course.COURSE, course.CTITLE, course.CHOUR,
    result.classattn, result.classtest, result.assign, result.midterm, result.final, result.retake_mid, result.retake_fin,
    result.total, result.SEMESTER, result.SYEAR, result.GP, result.LGRADE, (result.gp*course.CHOUR) AS QuPt
    FROM
    (
    (program
    INNER JOIN studinfo
    ON program.PROGCODE=studinfo.PROGCODE
        INNER JOIN result
    ON studinfo.STUDID=result.STUDID
    )
)
    INNER JOIN course
    ON result.COURSE=course.COURSE
    WHERE studinfo.STUDID='".$_REQUEST["id"]."'
    ORDER BY result.SYEAR, result.SEMESTER

How can I convert the above-like Query into Active record pattern? Also, pagination library needs a count of records do I need to run the query twice to get a count of results then get the query result?

Help would be appreciated.