Hi, I am writing a plugin to display data from that mysql table. Table contains around 2mil records. I would like to paginate results. I searched the forum but unable to find the solution. Any example on how to do it would be appreciated. P.S. Please note that I do not need to paginate posts. I need to paginate data extracted from custom table.
You need to paginate through a result set. You would use LIMIT in your SQL query to find the First result and Last result of the current page. There are tons of tutorials on how to do this. I'm not sure why you couldn't find it. - Have a param named page which holds the value of the Current Page. - Multiple Current Page by Results Per Page to find Last Result Record of the Current Page. (Page 4 x 10 results per page = Page 4 ends with 40th record). - Subtract Last Result Record by Results Per Page to find the Starting Record of the Current Page. (40 - 10 = 30th Starting record). - Use LIMIT Starting Record, Results Per Page. It's a little bit more complicated to get perfect but that should give you an idea. I wrote a class to do it quite easily: <?php /************************************************************************************************** * ================================================================================================= * PHP mySQL Paging Class 1.0 * class_resultpaging.php * - DESCRIPTION * A PHP function that enables you to create page links for your database results. * Function will return formatted page links and mySQL LIMIT code. * ================================================================================================= * Author: Jay Juliano - jayjuliano@gmail.com * Created: 12/23/2008 * ================================================================================================= * Copyright 2008-2009 - NetStar Interactive LLC * ================================================================================================= * You are free to use, distribute, and modify this software under the terms of the * GNU General Public License. See http://www.gnu.org/copyleft/gpl.html for more details. **************************************************************************************************/ class ResultPaging { function getPageLinks($sql, $params = "") { $__ResultPaging_DisplayPages = 5; $__ResultPaging_ResultsPerPage = 10; $__ResultPaging_PageParam = "page"; if (!isset($sql)) die("Missing SQL for getPageLinks()\n"); $params = isset($params) ? $params : ""; if ($params && !preg_match("/&$/", $params)) $params .= "&"; $params = preg_replace("/^&/", "", $params); $params = preg_replace("/^\?/", "", $params); $page = ""; if (!isset($_GET[$__ResultPaging_PageParam]) || !is_numeric($_GET[$__ResultPaging_PageParam]) || $_GET[$__ResultPaging_PageParam] == 0) { $page = 1; } else { $page = $_GET[$__ResultPaging_PageParam]; } $total_results = mysql_num_rows(mysql_query($sql)); $total_pages = 0; if ($total_results) $total_pages = ceil($total_results / $__ResultPaging_ResultsPerPage); if ($page > $total_pages) $page = $total_pages; $starting_record = ($page * $__ResultPaging_ResultsPerPage) - $__ResultPaging_ResultsPerPage; if ($starting_record < 0) $starting_record = 0; $start_page = ""; $end_page = ""; if ($total_pages == 0) { $start_page = 0; $end_page = 0; } else if ($total_pages <= $__ResultPaging_DisplayPages) { $start_page = 1; $end_page = $total_pages; } else { $page_set = ceil($page / $__ResultPaging_DisplayPages) - 1; $start_page = ($page_set * $__ResultPaging_DisplayPages) + 1; if ($total_pages < $start_page + $__ResultPaging_DisplayPages - 1) { $end_page = $total_pages; } else { $end_page = ($start_page + $__ResultPaging_DisplayPages) - 1; } } $previous_pageset_end = $start_page - 1; $next_pageset_start = $end_page + 1; $previous_pageset_start = $previous_pageset_end - $__ResultPaging_DisplayPages + 1; $next_pageset_end = $next_pageset_start + $__ResultPaging_DisplayPages - 1; $previous_page = $page - 1; $next_page = $page + 1; $first_record = $starting_record + 1; $last_record = $first_record + $__ResultPaging_ResultsPerPage - 1; if ($total_results < $last_record) $last_record = $total_results; /************************************************************************************************** * Format Link Pages **************************************************************************************************/ $pagesHTML = "<div id=\"pagesHTML\" style=\"width: 100%; border-bottom: 1px solid #000000; font-family: verdana, arial; font-size: 8pt;\">"; if ($first_record == 1 && $total_results < 1) $first_record = 0; $pagesHTML .= "<div id=\"pagesHTMLListing\" style=\"width: 33%; float: left;\">Listing $first_record-$last_record of $total_results</div>"; $pagesHTML .= "<div id=\"pagesHTMLPages\" style=\"width: 33%; float: left;\">"; if ($total_results > 0) { if ($start_page > $__ResultPaging_DisplayPages) $pagesHTML .= "<a href=\"" . $_SERVER['PHP_SELF'] . "?" . $params . "page=1\" title=\"Page 1\" style=\"font-weight: bold;\">1</a> <a href=\"" . $_SERVER['PHP_SELF'] . "?" . $params . "page=$previous_pageset_end\" title=\"‹‹ Pages $previous_pageset_start-$previous_pageset_end\" style=\"font-weight: bold;\">‹‹</a> "; for ($i = $start_page; $i <= $end_page; $i++) { if ($i == $page) { $pagesHTML .= "<span style=\"font-weight: bold;\">$i</span> "; } else { $pagesHTML .= "<a href=\"" . $_SERVER['PHP_SELF'] . "?" . $params . "page=$i\" title=\"Page $i\" style=\"font-weight: bold;\">$i</a> "; } } if ($end_page != $total_pages) $pagesHTML .= "<a href=\"" . $_SERVER['PHP_SELF'] . "?" . $params . "page=$next_pageset_start\" title=\"Pages $next_pageset_start-$next_pageset_end ››\" style=\"font-weight: bold;\">››</a> of <a href=\"" . $_SERVER['PHP_SELF'] . "?" . $params . "page=$total_pages\" title=\"Page $total_pages\" style=\"font-weight: bold;\">$total_pages</a>"; } $pagesHTML .= "</div>"; $pagesHTML .= "<div id=\"pagesHTMLPageSwitches\" style=\"width: 33%; float: left; text-align: right;\">"; if (($page > 1 && $page <= $total_pages) || ($page < $total_pages)) { $pagesHTML .= " "; if ($page > 1 && $page <= $total_pages) $pagesHTML .= "<a href=\"" . $_SERVER['PHP_SELF'] . "?" . $params . "page=$previous_page\" title=\"‹ Previous Page $previous_page\" style=\"font-weight: bold;\">‹ Previous</a>"; if (($page > 1 && $page <= $total_pages) && ($page < $total_pages)) $pagesHTML .= " | "; if ($page < $total_pages) $pagesHTML .= "<a href=\"" . $_SERVER['PHP_SELF'] . "?" . $params . "page=$next_page\" title=\"Next Page $next_page ›\" style=\"font-weight: bold;\">Next ›</a>"; } $pagesHTML .= "</div>"; $pagesHTML .= "<div style=\"clear: left;\"></div>"; $pagesHTML .= "</div>"; $limit_sql = "LIMIT $starting_record, $__ResultPaging_ResultsPerPage"; return array($pagesHTML, $limit_sql); } } ?> PHP: You would use it as follows: include("class_resultpaging.php"); $p = new ResultPaging(); // Create an SQL statement WITHOUT trailing semi-colon $sql = "SELECT blah1, blah2, blah3 FROM TableName WHERE whatever = 'blahblahblah'"; $params = ""; // Incase you want to append additional parameters to the page links (ie blah=whatever&foo=bar) $pagelinkArray = $p->getPageLinks("$sql;", $params); // Take note of the semi-colon after $sql $pageLinks = $pagelinkArray[0]; // Rendered HTML Page links. $pageLimit = $pagelinkArray[1]; // The LIMIT code for your SQL query $result = mysql_query("$sql $pageLimit;"); // The original SQL with the rendered LIMIT code attached to bring up results for current page // ... PHP: If you want to use PDO you have to alter a couple lines of the class. The process is this: - You create the SQL query MINUS the LIMIT code. - Feed the SQL code to the class. - The Class executes query to get total results number. Then returns the proper LIMIT code as well as rendered HTML page links. The query param "page" is reserved to keep track of the current page number. The class also accepts additional params for page links.
Thank you for your detailed reply. I am using wpdb class to interface with wordpress database. http://codex.wordpress.org/Class_Reference/wpdb How would I use your code with wpdb class? For example: global $wpdb; $sql = "SELECT * FROM table1"; $results = $wpdb->get_results($sql); if(count($results) > 0) { foreach($results as $result) { echo "<tr> <td>".$result-> field1."</td><td>".$result->field2."</td><td>".$result->field3."</td> </tr>"; }
Since my class merely generates the LIMIT code and HTML Page links you would do something like this: include("class_resultpaging.php"); $p = new ResultPaging(); $params = ""; // Any additional params to add to page links $pagelinkArray = $p->getPageLinks("$sql;", $params); $pageLinks = $pagelinkArray[0]; $pageLimit = $pagelinkArray[1]; $sql = "SELECT * FROM table1 " . $pageLimit . ";"); global $wpdb; $results = $wpdb->get_results($sql); print $pageLinks; if(count($results) > 0) { foreach($results as $result) { echo "<tr> <td>".$result-> field1."</td><td>".$result->field2."</td><td>".$result->field3."</td> </tr>"; } } print $pageLinks; PHP: Try that then let me know.
Thank you for your help.You code is very clear. Onle one change. I think I should add: $sql = "SELECT * FROM table1"; before $pagelinkArray = $p->getPageLinks("$sql;", $params);
I messed up in the example. The original SQL should be above the getPageLinks().. I'll correct it in a follow up post. Did it work? Personally I would stay clear from using *. I would name my cols. Just a preference.
include("class_resultpaging.php"); $p = new ResultPaging(); $sql = "SELECT * FROM table1"; $params = ""; // Any additional params to add to page links $pagelinkArray = $p->getPageLinks("$sql;", $params); $pageLinks = $pagelinkArray[0]; $pageLimit = $pagelinkArray[1]; $sql = $sql . " " . $pageLimit . ";"); global $wpdb; $results = $wpdb->get_results($sql); print $pageLinks; if(count($results) > 0) { foreach($results as $result) { echo "<tr> <td>".$result-> field1."</td><td>".$result->field2."</td><td>".$result->field3."</td> </tr>"; } } print $pageLinks; PHP:
It's a simple fix. I'm sure it has something to do with my usage of PHP_SELF. Can you post a link to an example? I need to take a look at it then I'll change the class.
I just realized that paging links break( permalinks are enabled or not) if plugin is not displayed on the front page.(index.php) Thanks, Alex