jebaird.com

Pagination is one of the simple features of a site if used right can be a great feature.  For something very simple it seems to be a resource hog.  For a while I have been looking for a way to paganate with out issuing two database queries.  After some quick searching I could not find what I was looking for, so I came up a method that uses a one query + a sub-query.

Quick Review

The method that I have seen the most, looks like some like this.

//check do we have a starting page?
if (isset($_GET['page'])) {
    $page = $_GET['page'];
} else {
    $page = 1;
} // end of if
//connect to the database
$link = mysql_connect('example.com:3307', 'username', 'password');
mysql_select_db('db_name',$link);
$query = "SELECT count(*) FROM table WHERE ...";
$result = mysql_query($query, $link);
$res = mysql_fetch_row($result);
$numRows = $res[0];

$maxItemsOnPage = 15;
$numberOfPages = ceil($numRows / $maxItemsOnPage);

//lets get the data for this page
$query = "SELECT * FROM table WHERE ....... LIMIT " . (($page - 1) * $maxItemsOnPage) 
."," . $maxItemsOnpage;
$result = mysql_query($query, $link);
//do something with the results


//show the links
if ($page < $numberOfpages) {
    echo '<a href="' . $_SERVER["PHP_SELF"] . '?page=' . ($page + 1) .
        '">Next page &gt;&gt;</a>';
}

if ($page > 1) {
    echo '<a href="' . $_SERVER["PHP_SELF"] . '?page=' . ($page - 1) .
        '">&lt;&lt; Prev page</a>';
}

One Query Method

The thing that I don't like about the method above is you have to use two queries.  Lets say it takes .23563 seconds for each query.  That's not a lot of time but over a period of time it adds up. So you use two queries every time the page is loaded. So .23563 * 2 = .47126 seconds right? Lets say your page gets viewed 25 times a day.  Take 25 * .47126 = 11.7815 seconds.  I would consider that a large amount of time for some thing very simple.  Here is my method.

//check do we have a starting page?
if (isset($_GET['page'])) {
    $page = $_GET['page'];
} else {
    $page = 1;
} // end of if

$maxItemsOnPage = 15;

//connect to the database
$link = mysql_connect('example.com:3307', 'username', 'password');
mysql_select_db('db_name',$link);
$query ="SELECT *,(SELECT COUNT(*) FROM blog_category as bc,blog as b
WHERE b.blog_blog_category_id=bc.blog_category_id AND
b.blog_status='published' ) as num_of_rows FROM blog_category as
bc,blog as b WHERE b.blog_blog_category_id=bc.blog_category_id AND
b.blog_status='published' ORDER BY b.blog_created DESC LIMIT" . (($page - 1) * $maxItemsOnPage) 
."," . $maxItemsOnpage;

$result = mysql_query($query, $link);

//we got the results now lets peek and see how many rows there are
$temp= mysql_fetch_row($res);

//assign the row count
$numberOfRows=$temp['num_of_rows'];

//reset the result set
mysql_data_seek($result,0);
//get the number of pages and be on our marry way!     
$numpages = ceil($numberOfRows / $maxItemsOnPage);

//do something with the results


//show the links
if ($page < $numberOfpages) {
    echo '<a href="' . $_SERVER["PHP_SELF"] . '?page=' . ($page + 1) .
        '">Next page &gt;&gt;</a>';
}

if ($page > 1) {
    echo '<a href="' . $_SERVER["PHP_SELF"] . '?page=' . ($page - 1) .
        '">&lt;&lt; Prev page</a>';
}

How it works

In this query we count the number of rows that fit our where clause using a sub query along with the limited results that we are only going to show on this page. I.E.:

(SELECT COUNT(*) FROM blog_category as bc,blog as b
WHERE b.blog_blog_category_id=bc.blog_category_id AND
b.blog_status='published' ) as num_of_rows

Then we peek ahead and get the first row and get the column named "num_of_rows".  Then we reset the query result with "mysql_data_seek($result,0)".  Poof that's it. 

There could be 2 downsides to this method.  One in every row in the result you have dupulacte data. And Two, Using "SELECT COUNT(*)" every time on tables that have lots of records could bog your server down.