Prev/Next page for Category's and Product Detail page

[KH316]

I'm new to this forum and haven't searched it nearly enough, so the solution to my query may already be here. Any pointers to such a topical thread(s) is greatly appreciated.

Here is the where the pages in question are being experimented with:

http://www.crsociety.info/misc/index.php

I'm trying to create Prev. Page | Next Page links on both:

The Category page (index to prev or next category page, if any exist, each of which contains a list of items)

The Product details page (index to prev or next product page, if any exist, in the order in which they are listed in the database (doesn't matter which category they belong to)).

The database was pre-sorted prior to being uploaded into MySQL. I would rather not sort dynamically. I also do not want to add any more fields (columns) to the database, though a programmer friend noted that I may have to add a unique id (auto increment) in MySQL.

Here are the fields in the database table book_items:

NAME varchar(255) not null default '',
NAME_clean varchar(255) not null default '',
CATEGORY varchar(50) not null default '',
CATEGORY_clean varchar(50) not null default '',
AUTHOR varchar(50) not null default '',
SIP varchar(50) not null default '',
ASIN varchar(50) not null default '',
Book_Description mediumtext not null default '',
Book_Description2 mediumtext not null default '',
Book_Description3 mediumtext not null default '',
Review1 mediumtext not null default '',
Review2 mediumtext not null default '',
Review3 mediumtext not null default '',
Review4 mediumtext not null default '',
Review5 mediumtext not null default ''

"ASIN" is an alphanumerical string (product code) unique to each item.

In an attempt to create the PREV | NEXT page links, a programmer friend suggested:

==========

To make prev / next links, when you build the link for the item, you also need to include (in the link) the information needed to make the link to the prev and next item. Then the page that displays the item also has the information needed to display the prev / next links. For example, if when generating the index page (list of all categories) output is:
A
B
C
D

Rather than just generating
href="...?q=cat&name=A"
href="...?q=cat&name=B"
href="...?q=cat&name=C"
href="...?q=cat&name=D"

You need to generate
href="...?q=cat&name=A&next=B"
href="...?q=cat&name=B&prev=A&next=C"
href="...?q=cat&name=C&prev=B&next=D"
href="...?q=cat&name=D&prev=C"

This requires modifying the
while($row = mysql_fetch_array($result)) {

} loop so that information for all three links (normal, prev, next) are available and can be used to make the link.

But this approach ran into a serious glitch, as friend comments:

==========

It works ok to generate the initial prev/next links. But then if the user clicks on one of those links to go to the next or prev item. There is no way to generate the prev/next links on that page because the info is not included in the url variables.

I think the best approach is add in the unique id (auto increment)....

Can anyone provide an alternative to this strategy -- one that works?!

Thx,
KH

[Houdini]

Here is a little tutorial on prev-next Pagination .

[Blulagoon]

If you did have a unique id in there, you could also remove the review 1-5 columns and replace them with a review table, which had a unique review_id, the book_id and review as the text field. Then it wouldn't matter how many reviews a book had. You could even store the reviewers name in there as well if that was of value.

You might also benefit with doing that for the 3 book_description columns as well. You'd then do a join to the main table using book_id to get the data out of the various tables.

HTH - Blu

[KH316]

Houdini wrote:

Here is a little tutorial on prev-next Pagination .

This is very similar to other Pagination tutorials found on myriad internet sites and books (my copy of PHP and MySQL, 2nd Ed, Larry Ullman, has something very similar). Although this approach may be adaptable to the product detail page (no luck on this end so far), where it doesn't matter which NEXT or PREV page you go to, as long as there is one, it won't AFAICS work on the Category page.

[KH316]

Blulagoon wrote:

If you did have a unique id in there, you could also remove the review 1-5 columns and replace them with a review table, which had a unique review_id, the book_id and review as the text field. Then it wouldn't matter how many reviews a book had. You could even store the reviewers name in there as well if that was of value.

You might also benefit with doing that for the 3 book_description columns as well. You'd then do a join to the main table using book_id to get the data out of the various tables.

HTH - Blu

As noted in the original query, I don't really want to modify the MySQL database too much. Simply don't have time, given the 100's of databases and 1000's of products I have to work with. Hopefully, this issue can be resolved in the script somehow -- and that script would have to work efficiently, too (i.e. create requested page almost as quickly as my current static-page approach).

Thx for your feedback.

[Roger_Ramjet]

This pagination is best done with sessions, but I guess from your friend's code that you are not using them.

You are going to have to modify your query as well as the while loop that processes the results.

Prev/Next links can contains anything so long as you can use it to determine the query LIMIT clause parameters. Without sessions it is best to store the offset for the required page - you can calculate that at the same time that you are building the limit clause for the current page, and deciding whether there should even be a prev or next link.

// set number to print per page and base offset
$perpage = 5;
$off = 0;
$prev = NULL;
$next = NULL;

// base query
$base = "SELECT CATEGORY_clean AS cat FROM book_items 
     GROPUP BY cat
     ORDER BY cat";

// test for offset in get vars
if (isset($_GET['off'])) {
   $off = $_GET['off'];
}

// limit clause, always query for one more than per page so you know if there is a next page or not
$rows = $perpage + 1;
$limit = " LIMIT $off,$rows";

// build query
$sql = $base . $limit;
// run it
$result = mysql_query($sql) or die ($sql . '<br />' . mysql_error());

if (!result) {
   echo "query error";
   break;
}
// count returned rows and calculate next, remember offset is base zero
$count = mysql_num_rows($result);
if ($count > $perpage) {
   $next = "http:\\mydomain.com\cats.php?off=" . $off + $perpage;
} 
// calculate prev
if ($off != 0) {
   $prev= "http:\\mydomain.com\cats.php?off=" .  $off - $perpage;
}

// html to set up page, now for prev/next links

if ($prev) {
   echo '<a href="' . $prev . '">';
}
if ($next) {
   echo '<a href="' . $next . '">';
}

// now output the list for this page
for ($i = 1; $i <= $perpage; $i++) {
// html for category
}

Should just about do it.

Just by passing the offset for the current page in the url get vars you can do everything.

[KH316]

Roger Ramjet,

Thx for posting the code. I haven't yet tried it because I noticed this...

// set number to print per page and base offset
$perpage = 5;

...in the first two lines. I don't want to print a certain predetermined number of links per page. For the main index page (the one that prints a list of all categories), that is determined by:

function get_index_page_content() {
  global $program_name;
  $sql = "select distinct category, category_clean from book_items order by CATEGORY";
  $result = mysql_query($sql) 
    or die("Could not do: $sql, because: " . mysql_error());
  $item_list = "";
  while($row = mysql_fetch_array($result)) {
    $category = $row['category'];
    $category_clean = $row['category_clean'];
    $item_list .= "<li><a href=\"$program_name?q=cat&name=$category_clean\">$category</a><br><br>\n";

  }
  $content = "<p>Select one of the following categories:</p>\n" .
      "<ul>\n$item_list</ul>";
  return($content);
}

On the Category page (the one that prints a list of all items in a category), that number is determined by the number of items that category contains:

/*Generating the category page*/

function get_category_page($category_clean) {
  global $program_name;
  $sql = "select name, name_clean, asin, category from book_items where category_clean = '$category_clean'";
  $result = mysql_query($sql) 
    or die("Could not do: $sql, because: " . mysql_error());
  $item_list = "";

  while($row = mysql_fetch_array($result)) {  

    $name = $row['name'];
    $asin = $row['asin'];
	$name_clean = $row['name_clean'];
    $category = $row['category'];
    $item_list .= "<li><a href=\"$program_name?q=detail&asin=$asin&$name_clean\">$name</a><br><br>\n";
  }  

  $content = "<p>Listed below you will find links to all books in the category " .
         "<em>$category</em> in alphabetical order. Click the link for a detailed " .
         "description of each item.</p>\n" .
         "<ul>\n$item_list</ul>".
		 "<p><a href=\"index.php\" title=\"Home page\"><b>Master list of all book categories</b></a></p>";

  return(array($content, $title));

}

I hope I read your code correctly.

-KH

[Roger_Ramjet]

Well, I read your post where you said "when generating the index page (list of all categories)" and looked at your page and assumed you wanted to paginate the categories list.

Now, my code will work equally as well for the items in a category list, or any page with a list of query results that you want to paginate. Just change the query, and the code that generates the html display accordingly.

I put the per-page in as a var so that you can set or change it as you like. It is the number of list items to display ON EACH PAGE, not the number returned by a query. It is a presentation thing so users don't have to scroll down an endless page and you set it according to the space each item will take up on a page. In some cases it would be a list of 5 multi-line items, in others it could be a table with 20 or 25 rows. Entirely up to you. It is the pagination that I am dealing with, not the contents themselves. You choose the per-page limit according to your data and page layout.

[KH316]

Roger Ramjet wrote:

I put the per-page in as a var so that you can set or change it as you like. It is the number of list items to display ON EACH PAGE, not the number returned by a query. It is a presentation thing so users don't have to scroll down an endless page and you set it according to the space each item will take up on a page. In some cases it would be a list of 5 multi-line items, in others it could be a table with 20 or 25 rows. Entirely up to you. It is the pagination that I am dealing with, not the contents themselves. You choose the per-page limit according to your data and page layout.

Thx for your reply.

If you look at the the test site noted earlier, you'll note that the list of books on the sub-index page (aka the single category page) is simply all books in that category. There may be one title there or there may be several. Becasue I already know there are not an overwhelmingly large number of items (book titles in this case) per category, I simply want the script to list them all. And that's what it does. The question is: how can one index on the Category page (Next Category | Prev. Category) and on the product details page (Next Item | Prev. Item).

-KH

[Roger_Ramjet]

Ah! Now that's a whole other question. It amounts to: how to return an entry with the prev and next entries in 1 query.

Now, there are always lots of solutions, but the one with the lowest common denominator is through a UNION query.

To display the list of items in a category, you have that category for input into the query. Likewise for titles in a category you have the title. OR the primary key.
Now, to get a record, and the ones before and after it sorted on any column, you use this query

"SELECT * FROM table WHERE column < $input ORDER BY column DESC LIMIT 1
UNION
SELECT * FROM table WHERE column = $input
UNION
SELECT * FROM table WHERE column > $input ORDER BY column LIMIT 1"

So the user clicks a link to category 'Data-Acquisition' and you substitute that GET var for $input, table and column names as well, in that query and you should get a 3 row resultset. Record 1 is the prev category, 2 is the clicked cat and 3 the next. Make your links out of them for the prev/next pages. Do likewise for the titles pages.
If your titles can be classified under more than one category then you will have to include the category as well as the title in the url params.

Remember to check for there being a prev/next page to go to. Best way is to count them, then when there is less than 3, first row == $input then no prev, else no next.

[Roger_Ramjet]

The most important thing to remember about this particular query form is that it only works correctly on a UNIQUE column.

You can extend it to work with a non-unique column by using grouping, but then you should have broken the data out into another table in the first place and be using that for the query.

That said, here is how you write the grouped query for eg prev/this/next category

"SELECT column FROM table WHERE column<$input ORDER BY column DESC GROUP BY column LIMIT 1
UNION
SELECT column FROM table WHERE column=$input GROUP BY column LIMIT 1
UNION
SELECT column FROM table WHERE column>$input ORDER BY column GROUP BY column LIMIT 1 ";

[KH316]

Roger Ramjet,

Thx for your detailed reply. I will not have time to work on this project for about 10-14 days in due to business-related travel. If you have any more suggestions, please continue to post them. I'll return to this thread in a week or two.

Thanks again for troubleshooting!