Sign in with
Sign up | Sign in
Your question

PHP / MySQL pagination issue

Last response: in Applications
Share
January 22, 2013 3:33:04 PM

Hi guys and girls. Need a little help resolving a pagination issue.

Currently building a book store for a university project and everything is going to plan until I came up with a real head scratcher (for me at least ;) ).

I want to limit the amount of records pulled from the database (ecommerce) to maybe 10 or 15 per page which I have managed to do but the second half of my script which deals with creating additional pages to display the results that exceed the $display limit does not display anything and its starting to drive me a little mad.

I the site is on a local server so a direct link cannot be provided but will post the code for the page below.

  1. <?php # browse_books.php
  2. // This page displays the available books (products).
  3.  
  4. // Set the page title and include the HTML header:
  5. $page_title = 'Browse Books';
  6. include ('includes/header.html');
  7.  
  8. require_once ('../mysqli_connect.php');
  9.  
  10. // Number of records to show per page:
  11. $display = 15;
  12.  
  13. // Determine how many pages there are...
  14. if (isset($_GET['p']) && is_numeric($_GET['p'])) { // Already been determined.
  15.  
  16. $pages = $_GET['p'];
  17.  
  18. } else { // Need to determine.
  19.  
  20. // Count the number of records:
  21. $q = "SELECT COUNT (book_id) FROM books";
  22. $r = @mysqli_query ($dbc, $q);
  23. $row = @mysqli_fetch_array ($r, MYSQLI_NUM);
  24. $records = $row[0];
  25.  
  26. // Calculate the number of pages...
  27. if ($records > $display) { // More than 1 page.
  28. $pages = ceil ($records/$display);
  29. } else {
  30. $pages = 1;
  31. }
  32.  
  33. } // End of p IF.
  34.  
  35. // Determine where in the database to start returning results...
  36. if (isset($_GET['s']) && is_numeric($_GET['s'])) {
  37. $start = $_GET['s'];
  38. } else {
  39. $start = 0;
  40. }
  41.  
  42. // Default query for this page:
  43. $q = "SELECT authors.author_id, CONCAT_WS(' ', first_name, middle_name, last_name) AS author, book_name, price, description, book_id FROM authors, books WHERE authors.author_id = books.author_id ORDER BY authors.last_name ASC, books.book_name ASC LIMIT $start, $display";
  44.  
  45. // Are we looking at a particular author?
  46. if (isset($_GET['aid']) && is_numeric($_GET['aid']) ) {
  47. $aid = (int) $_GET['aid'];
  48. if ($aid > 0) { // Overwrite the query:
  49. $q = "SELECT authors.author_id, CONCAT_WS(' ', first_name, middle_name, last_name) AS author, book_name, price, description, book_id FROM authors, books WHERE authors.author_id = books.author_id AND books.author_id = $aid ORDER BY books.book_name";
  50. }
  51. }
  52.  
  53. // Display all the books, linked to URLs:
  54. $r = mysqli_query ($dbc, $q);
  55. while ($row = mysqli_fetch_array ($r, MYSQLI_ASSOC)) {
  56.  
  57.  
  58. // Display each record:
  59. echo "<div class=\"book_holder\">
  60. <h1><a href=\"view_book.php?pid={$row['book_id']}\">{$row['book_name']}</a></h1>
  61.  
  62. <h2><a href=\"browse_books.php?aid={$row['author_id']}\">{$row['author']}</a></h2>
  63.  
  64. <p>";
  65.  
  66. $newstring = substr("{$row['description']}",0,100);
  67. echo $newstring;
  68.  
  69. echo "....<a href=\"view_book.php?pid={$row['book_id']}\"> more info</a>";
  70.  
  71. echo "</p>
  72.  
  73. <h2>£{$row['price']}</h2>
  74.  
  75. </div>";
  76.  
  77. } // End of while loop.
  78.  
  79.  
  80. mysqli_free_result ($r);
  81. mysqli_close($dbc);
  82.  
  83. // Make the links to other pages, if necessary.
  84. if ($pages > 1) {
  85.  
  86. // Add some spacing and start a paragraph:
  87. echo '<br /><p>';
  88.  
  89. // Determine what page the script is on:
  90. $current_page = ($start/$display) + 1;
  91.  
  92. // If it's not the first page, make a Previous button:
  93. if ($current_page != 1) {
  94. echo '<a href="browse_books.php?s=' . ($start - $display) . '&p=' . $pages . '">Previous</a> ';
  95. }
  96.  
  97. // Make all the numbered pages:
  98. for ($i = 1; $i <= $pages; $i++) {
  99. if ($i != $current_page) {
  100. echo '<a href="browse_books.php?s=' . (($display * ($i - 1))) . '&p=' . $pages . '">' . $i . '</a> ';
  101. } else {
  102. echo $i . ' ';
  103. }
  104. } // End of FOR loop.
  105.  
  106. // If it's not the last page, make a Next button:
  107. if ($current_page != $pages) {
  108. echo '<a href="browse_books.php?s=' . ($start + $display) . '&p=' . $pages . '">Next</a>';
  109. }
  110.  
  111. echo '</p>'; // Close the paragraph.
  112.  
  113. } // End of links section.
  114.  
  115.  
  116. include ('includes/footer.html');
  117. ?>


I am sure it is something simple but have developed code blindness from looking at it for too long.

Thanks in advance
January 22, 2013 3:41:52 PM

Have worked out that there is something wrong with the $pages variable as regardless it always returns value of 1.
m
0
l
!