PHP / MySQL pagination issue

MEMOFLEX

Honorable
May 5, 2012
3
0
10,510
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.

[cpp]<?php # browse_books.php
// This page displays the available books (products).

// Set the page title and include the HTML header:
$page_title = 'Browse Books';
include ('includes/header.html');

require_once ('../mysqli_connect.php');

// Number of records to show per page:
$display = 15;

// Determine how many pages there are...
if (isset($_GET['p']) && is_numeric($_GET['p'])) { // Already been determined.

$pages = $_GET['p'];

} else { // Need to determine.

// Count the number of records:
$q = "SELECT COUNT (book_id) FROM books";
$r = @mysqli_query ($dbc, $q);
$row = @mysqli_fetch_array ($r, MYSQLI_NUM);
$records = $row[0];

// Calculate the number of pages...
if ($records > $display) { // More than 1 page.
$pages = ceil ($records/$display);
} else {
$pages = 1;
}

} // End of p IF.

// Determine where in the database to start returning results...
if (isset($_GET['s']) && is_numeric($_GET['s'])) {
$start = $_GET['s'];
} else {
$start = 0;
}

// Default query for this page:
$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";

// Are we looking at a particular author?
if (isset($_GET['aid']) && is_numeric($_GET['aid']) ) {
$aid = (int) $_GET['aid'];
if ($aid > 0) { // Overwrite the query:
$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";
}
}

// Display all the books, linked to URLs:
$r = mysqli_query ($dbc, $q);
while ($row = mysqli_fetch_array ($r, MYSQLI_ASSOC)) {


// Display each record:
echo "<div class=\"book_holder\">
<h1><a href=\"view_book.php?pid={$row['book_id']}\">{$row['book_name']}</a></h1>

<h2><a href=\"browse_books.php?aid={$row['author_id']}\">{$row['author']}</a></h2>

<p>";

$newstring = substr("{$row['description']}",0,100);
echo $newstring;

echo "....<a href=\"view_book.php?pid={$row['book_id']}\"> more info</a>";

echo "</p>

<h2>&pound;{$row['price']}</h2>

</div>";

} // End of while loop.


mysqli_free_result ($r);
mysqli_close($dbc);

// Make the links to other pages, if necessary.
if ($pages > 1) {

// Add some spacing and start a paragraph:
echo '<br /><p>';

// Determine what page the script is on:
$current_page = ($start/$display) + 1;

// If it's not the first page, make a Previous button:
if ($current_page != 1) {
echo '<a href="browse_books.php?s=' . ($start - $display) . '&p=' . $pages . '">Previous</a> ';
}

// Make all the numbered pages:
for ($i = 1; $i <= $pages; $i++) {
if ($i != $current_page) {
echo '<a href="browse_books.php?s=' . (($display * ($i - 1))) . '&p=' . $pages . '">' . $i . '</a> ';
} else {
echo $i . ' ';
}
} // End of FOR loop.

// If it's not the last page, make a Next button:
if ($current_page != $pages) {
echo '<a href="browse_books.php?s=' . ($start + $display) . '&p=' . $pages . '">Next</a>';
}

echo '</p>'; // Close the paragraph.

} // End of links section.


include ('includes/footer.html');
?>
[/cpp]

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

Thanks in advance