As @ryan_uk just said, you use LIMIT -- thing is you already have LIMIT in there... Which LIMIT can take two numbers. You feed it just one value it pulls x results from the start of the result. You feed it two numbers it will start at X and pull Y records... so the LIMIT 0, 15 you have in the query starts at record zero and pulls 15 results. You want records 15 to 29 pass LIMIT 15,15 -- for record 30..44 you'd pass LIMIT 30,15 -- so on and so forth. Pagination is just a simple matter of figuring out how many there would be total -- so do the same query put pull count(*) with no LIMIT instead of a normal result set. So to grab the info needed: /* the next line turns off prepare emulation, allowing 'lazy' execution to pass LIMIT values properly. Otherwise you'd HAVE to use bindParam and force typecasting while passing the variable. */ $db->setAttribute(PDO::ATTR_EMULATE_PREPARES, false); // where to start from, how many to fetch $count = 15; $start = 30; $stmt = $db->prepare('COUNT (*) FROM members'); $total= $stmt->fetchColumn(); $stmt = $db->prepare('SELECT * FROM members LIMIT :start, :count'); $stmt->execute(array( ':start' => $start, ':count' => $count )); Code (markup): DISPLAYING pagination is something a LOT of developers screw up. myBB for example STILL cannot display pagination right, often messing up after posts are deleted from a thread or the oh so common 'shows one more page than there are posts'. I use the following routine in my systems to display pagination; as simple as something like this should be I put a lot of time and effort into it to make sure it was relatively bulletproof. function theme_pagination($urlRoot,$current,$perPage,$totalArticles) { if ($totalArticles>$perPage) { $lastPage = floor(($totalArticles - 1) / $perPage); $currentPage = floor($current / $perPage); $urlRoot .= ((strpos($urlRoot,'?') === false) ? '?' : '&') . 'start='; echo ' <div class="pagination"> Pages: <ul>'; if ( ($lastPage > 0) && ($currentPage > 0) ) echo ' <li><a href="', $urlRoot, '0">First</a></li> <li><a href="', $urlRoot, $currentPage - 1, '" title="previous">«</a></li>'; if ($lastPage > 9) { $counter = ($currentPage < 6) ? 0 : $currentPage - 5; $endPage = $counter + 10; if ($endPage > $lastPage) $endPage = $lastPage; } else { $counter = 0; $endPage = $lastPage; } while ($counter <= $endPage) { echo ' <li>', ( ($noAnchor = ($counter == $currentPage)) ? '<span>' : '<a href="' . $urlRoot . $counter . '">' ), ++$counter, ( $noAnchor ? '</span>' : '</a>' ), '</li>'; } if ( ($lastPage > 0) && ($currentPage < $lastPage) ) echo ' <li><a href="', $urlRoot, $currentPage+1, '" title="next">»</a></li> <li><a href="', $urlRoot, $lastPage, '">Last</a></li>'; echo ' </ul> <!-- .pagination --></div>'; } } // theme_pagination Code (markup): Which you'd call when you want to show pagination thus: theme_pagination('http://yourURL', $start, $count, $total); Code (markup): Remember that $total needs to be the overall total, not just how many you are showing. This will calculate the current page and how many pages are there for you. It also displays 11 pages forward/back, auto-centering current if possible (so showing 5 before and 5 after), provides all the hooks needed to style it consistently, and first, back, forward and last buttons. Some simple CSS to make that pretty would go like this: .pagination ul, .pagination li { list-style:none; display:inline; margin:0; padding:0; } .pagination a, .pagination span { display:inline-block; padding:0.2em 0.4em; text-align:center; vertical-align:middle; text-decoration:none; color:#000; background:#EEE; border:2px solid #000; -webkit-border-radius:0.5em; border-radius:0.5em; } .pagination span { border-color:#888; background:#FFF; } .pagination a:active, .pagination a:focus, .pagination a:hover { color:#DEF; background:#44F; } Code (markup): Hope this helps.
Oh, that routine also passes start=pagenumber as getdata, so to calculate $start based on that you'd want to do: $start = $_GET['start'] * $count; Where $count is how many to show per page. -- edit -- Actually, this would be better: $start = isset($_GET['start']) ? $_GET['start'] * $count : 0; That way it starts at zero if 'start' isn't set, otherwise go to that page.
You know, there's nothing like looking at your own code from six or seven years ago and thinking "You know, I think I can do better now"... but as I often say if you aren't disgusted with your own code of just a year ago, you're probably in the wrong business. Here's a bit better a rewrite: function theme_pagination($urlRoot,$current,$perPage,$totalArticles) { if ($totalArticles > $perPage) { $urlRoot .= ((strpos($urlRoot,'?') === false) ? '?' : '&') . 'start='; echo ' <div class="pagination"> Pages: <ul>'; if (($currentPage = floor($current / $perPage)) > 0) echo ' <li><a href="', $urlRoot, '0">First</a></li> <li><a href="', $urlRoot, $currentPage - 1, '" title="previous">«</a></li>'; if (($lastPage = floor(($totalArticles - 1) / $perPage)) > 9) { $counter = ($currentPage < 6) ? 0 : $currentPage - 5; if (($endPage = $counter + 10) > $lastPage) { $endPage = $lastPage; $counter = $endPage - 10; } } else { $counter = 0; $endPage = $lastPage; } while ($counter <= $endPage) { echo ' <li>', ( ($noAnchor = ($counter == $currentPage)) ? '<span>' : '<a href="' . $urlRoot . $counter . '">' ), ++$counter, ( $noAnchor ? '</span>' : '</a>' ), '</li>'; } if ($currentPage < $lastPage) echo ' <li><a href="', $urlRoot, ++$currentPage, '" title="next">»</a></li> <li><a href="', $urlRoot, $lastPage, '">Last</a></li>'; echo ' </ul> <!-- .pagination --></div>'; } } // theme_pagination Code (markup): A little simpler, removes a couple redundant checks, and makes sure that if there are 11 or more pages, it ALWAYS shows 11 pages. (It wasn't doing that at the end of the list, just the beginning! DOH!) I made a simple self-tester test.php <?php $count = 15; $start = isset($_GET['start']) ? $_GET['start'] * $count : 0; require_once('template.php'); theme_header('PHP Pagination Test Page'); theme_pagination( $_SERVER['PHP_SELF'],$start,$count,$count * 15); theme_footer(); ?> Code (markup): Which I put a live copy of here: http://www.cutcodedown.com/methods/pagination/ With .phps files to make source viewing simple.
It's vulnerable to XSS (at least on Firefox): http://www.cutcodedown.com/methods/pagination/test.php/"><script>alert(1);</script>
My bad, dumbass forgot to sanitize it in the simple demo. $fileRoot = substr( $_SERVER['SCRIPT_FILENAME'], strrpos($_SERVER['SCRIPT_FILENAME'],'/') ); $urlRoot = substr($_SERVER['PHP_SELF'], 0, strpos($_SERVER['PHP_SELF'], $fileRoot) + strlen($fileRoot) ); Code (markup): Should do the trick. Problem isn't in the actual pagination code, but in the demo itself. All fixed on the site... well, except that doing so screws up paths to the CSS (big deal). One of those few times I would possible advocate the use of BASE. -- edit -- In fact I just modified it to that, defining URL_ROOT and BASE_ROOT in the template header, and setting BASE accordingly. Now not only is the end of it chopped off, doing so doesn't screw up the root looking for things like CSS and scripting either.