February 8th, 2014, 09:31 AM
Pagination with one query
Typical pagination will do something which determines the total number of records, and uses the desired per page and the current page to display the pertinent records and display something like "Page 3 of 12". Below is some example code. Is it possible to do this with one query? I know that SQL_CALC_FOUND_ROWS will provide the count if there wasn't a limit. Thanks
$stmt= db::db()->query('SELECT COUNT(*) FROM myTable');
$stmt = db::db()->query('SELECT * FROM myTable LIMIT '.($currentPage - 1) * $records_per_page.','.$records_per_page);
February 8th, 2014, 12:42 PM
I question the whole error-correction logic.
If I enter invalid data (like a non-numeric or a nonexistent page number), I want to be told, not silently corrected by a virtual nanny. I don't know your users, but I'm pretty sure most people do not appreciate this kind of forced blessing.
If the user requested page 13 from 12 pages, tell them. Something went wrong, so they should know and decide what to do next. Do not silently assume that they meant page 12. This can lead to a lot of confusion. The same is true for non-numeric input. A pageIndex=abc is an invalid reference, not “Let's just say you asked me for page 1”.
The classical LIMIT approach, despite being extremely popular, also has issues. Most websites simply do not work like a book. They change all the time, new entries get added, unwanted entries may get deleted. Static pagination doesn't work very well for that.
February 8th, 2014, 02:43 PM
I don't think you understand. It has nothing to do with error-correction logic. When the user requests records, I wish to provide those records in bite (not byte ) size pieces, and give one of the typical "1 2 3 ... 43 44 next" menus at the bottom.
Originally Posted by Jacques1
February 8th, 2014, 02:46 PM
After going to the link you posted, you appear to understand, however, I still do not understand what error-correction logic has to do with it.
February 8th, 2014, 05:44 PM
Um, what? The one and only reason for even doing the first query as opposed to using SQL_CALC_FOUND_ROWS is this line:
That is, all page numbers greater than the last page are automatically mapped to the last page -- which requires you to get the total number of pages before you do the actual query.
Im saying that this correction (what else do you call it?) together with the int casting on top of the script are bad, because they silently change the user input when you should actually show a proper error message.
But now it sounds like you never wanted this line of code? I'm confused. Either way, if the correction goes away, the first query is useless and can simply be deleted.
The rest depends on whether or not you want to stick to static pagination. Personally, I couldn't think of a user who would want to jump specifically to page 43 when the content of the pages isn't even constant.
February 8th, 2014, 08:29 PM
Could we start over?
All I want is a good approach to displaying the number of blocks of available records and displaying which block is being viewed (so I could show the "1,2,3.... 5 ... 12,13,14), and displaying the results of the viewed block.
http://www.mysqlperformanceblog.com/...lc_found_rows/ states that it is faster to perform a COUNT query before the query to display the results instead of using SQL_CALC_FOUND_ROWS. The blog is old and likely is no longer applicable, and I was planning on using one query only if possible and using SQL_CALC_FOUND_ROWS.
February 8th, 2014, 09:32 PM
Well, then what stops you from doing one query?
Don't fumble with optimizations before you have solid evidence that there's actually a performance issue.
$entriesPerPage = 10;
if ( !isset($_GET['page']) || (ctype_digit($_GET['page']) && $_GET['page'] >= 0) )
$page = isset($_GET['page']) ? $_GET['page'] : 1;
$entries = $db->query('
' . intval(($page - 1) * $entriesPerPage) . ', ' . intval($entriesPerPage) . '
$totalEntriesStmt = $db->query('
$totalEntries = $totalEntriesStmt->fetchColumn();
$totalPages = ceil($totalEntries / $entriesPerPage);
// display the page content
echo htmlEscape('There is no page ' . $_GET['page'] . '. Please choose a page number from the navigation.');
echo htmlEscape('Invalid page number.');
The problem of doing two separate queries should be obvious: If the entries change in between, the pagination may not match the actual number of entries.
Comments on this post