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 =] )