JPark Posted June 12, 2009 Share Posted June 12, 2009 Let's say that I have a script which queries the db and returns 10 items at a time and then sets up a link to see the next page, etc. Here's the problem... if I have 43 items in the db, I will get 4 pages (items 0-9, 10-19, 20-29 and 30-39), I lose the last 4 items because there in less than 10 left. How do I fix that?? //number of records to be displayed per page $records_per_page = 5; //look for starting marker //if not available, assume 0 if (!$_GET['start']) $start=0; else $start = $_GET['start']; //open connection to MySQL server $connection= mysql_connect('host', 'password', 'user') or die ('Unable to Connect'); //select database mysql_select_db('db') or die ('Unable to select database'); // create and execute query to count available records $query= "SELECT COUNT(shirtType) FROM shirts WHERE sex='MEN' && shirtType='Ringer T-Shirt'"; $result= mysql_query($query) or die ('Error in query: $query.'.mysql_error()); //get the total number of rows $row= mysql_fetch_row($result); $total_records= $row[0]; $total_pages = ceil($total_entries / $entries_per_page); $offset = ($page_number - 1) * $entries_per_page; //if records exist if (($total_records > 0) && ($start < $total_records)) { // Retrieve all the men's shirt data from the "shirts" table $result = mysql_query("SELECT * FROM shirts WHERE sex='MEN' && shirtType='Ringer T-Shirt' LIMIT $start,$records_per_page") or die(mysql_error()); // store the record of the "shirts" table into $row $row = mysql_fetch_array($result) or die(mysql_error()); // Print out the contents of the entry while($row = mysql_fetch_array( $result )) { // Print out the contents of each row into a table echo "<a href ='". $row['url']."'><img src='". $row['imageUrl']. "' border='0'></a>"; echo "<br>"; } // set up the previous page link // this should appear on all pages except the first page // the start point for the preious page will be // the start point for this page // less the number of records per page //yes -- first page if ($start == 0) { echo "Previous Page <a href=" . $_SERVER['PHP_SELF'] . "?start=" . ($start+$records_per_page) . ">Next Page</a>"; } // // set up the "next page" link // this should appear on all pages except the last page // the start point for the next page // will be the end point for this page //yes -- middle pages if ((($start+$records_per_page) < $total_records) && ($start > 0)) { echo "<a href=" . $_SERVER['PHP_SELF'] . "?start=" . ($start-$records_per_page) . ">Previous Page</a> <a href=" . $_SERVER['PHP_SELF'] . "?start=" . ($start+$records_per_page) . ">Next Page</a>"; } // // yes -- last page if ((($start+$records_per_page) >= $total_records) && ($start > 0)){ echo "<a href=" . $_SERVER['PHP_SELF'] . "?start=" . ($start-$records_per_page) . ">Previous Page</a> Next Page"; } //$srpg= ($start+$records_per_page); //echo "<br>Start + Records per page= ".$srpg; //echo "<br />Total records= ".$total_records; // at ?start=15, $srpg = 20 and $total_records =21. I am missing the //last shirt (because there are less than 5 left?) } Thanks, Joe Quote Link to comment Share on other sites More sharing options...
jpratt Posted June 12, 2009 Share Posted June 12, 2009 Both these are easy to implement. One easier than the other: http://www.tonymarston.net/php-mysql/pagination.html Quote Link to comment Share on other sites More sharing options...
waynewex Posted June 12, 2009 Share Posted June 12, 2009 There's a pagination tutorial here that you should look at. Pay attention to the main logic of it. Quote Link to comment Share on other sites More sharing options...
JPark Posted June 14, 2009 Author Share Posted June 14, 2009 Thanks waynewex and jpratt! While both links are quite informative, I like the Crayon Violet tutorial better. Just my personal opinion... Thank you both for your help. It works like a charm. Joe Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.