Tell me more ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.
up vote 1 down vote favorite

How to get total number of rows for particular query and also limiting the query results to 10 rows. For example. I've a table called sample. It has 400 rows. On running a query like where name = "%Sam%" it returns me 213 rows. Now I'l be taking only first 10 rows and displaying the result to user but I need the total rows returned. How should I need to do it in code igniter?

SELECT
        SQL_CALC_FOUND_ROWS *
FROM
        sample
WHERE
        name like "%sam%"

like this? How to retrieve total number counts?

share|improve this question

2 Answers

up vote 0 down vote accepted

You need to run a second query to get the results of SQL_CALC_FOUND_ROWS:

SELECT FOUND_ROWS()

That will return the value found using SQL_CALC_FOUND_ROWS. You may find using an alias easier for getting the result, though:

SELECT FOUND_ROWS() AS num_results
share|improve this answer
It worked perfectly. Thanks a lot :) – Joshua Shrav Jun 14 at 13:04
up vote 0 down vote

You can use two query one to get the count and the other return the limited result.

Like in your model create a function that only returns the count variable

and create a second function that generates the paginated results.

eg..

public function count($where){
        $query = $this->db->query("select count(id) as count from clients $where");
        return $query->row('count');
        }
}

public function limit($sidx,$sord,$start,$limit,$where){
        $query = $this->db->query("select * from clients $where ORDER BY $sidx $sord LIMIT $start , $limit");
        if ($query->num_rows() > 0){
            return $query->result_array();
        }
}

And here goes the controller code

$where = // calculated

$count = count($this->model->count($where));

        if( $count > 0 ) { $total_pages = ceil($count/$limit); } else { $total_pages = 0; }

        if ($page > $total_pages) $page = $total_pages; 


        $start = $limit * $page - $limit;


        $users = $this->model->limit($sidx,$sord,$start,$limit,$where);

.................

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.