Last Updated: February 25, 2016
·
3.75K
· relequestual

Get total count for use with pagination for MySQL

I'm creating some pagination and I need to know the total number of rows returned without the per page limit. So I replace the Limit and convert the Select * to Select '' and encapsulate that in a Select Count.

I know this won't work with more complex queries, but for fairly simple queries with a single Select, this seems to work OK. Using another query to count is faster than using SQLCALCFOUND_ROWS as other answers on SO have suggested.

If you have any suggestions, do fork the gist (https://gist.github.com/Relequestual/5583353)

(This used within Codeigniter, but could easily be adapted to use in whatever you use)

$last_query = $this->db->last_query();
$pattern = '/(.*)limit (.*)/i';
$new_query = preg_replace ($pattern, '', $last_query);

$pattern = '/(.*)SELECT *(.*)/';
$new_query = preg_replace ($pattern, 'SELECT \'\'', $new_query);

$new_query = 'Select Count(*) AS res From (' . $new_query . ') v__dynamic;';

$query = $this->db->query($new_query);
$result = $query->row();

(This is my first post on coderwall so be nice =] )