next and previous buttons

Anon

A client has requested that I add next and previous buttons to their website catalog but Im not sure what the best method is to make this happen.

My biggest problem is that there is a products table with all the products and there is multiple categories to group the products.

Some how I need to query the data for a selected category, determine what product the user is currently looking at and then pull out the previous entry and next entry. I would like to use the productid field but since the categories may not be grouped together in the database its not as simple as moving to the next record

First idea: query the database of a category into an array, determine what product the user is on and move back one and forward one with in the array.

Second idea: Determine what product the user is looking at and then search for the next and previous record displaying the 2 closest.

Im not sure how either one of these ideas would be coded or if it is even the right way of doing something like this.

Any ideas?

Anon

there must be a way to do this with one select statement (like a join.) If you show us your data structure and desired query, I'm sure we'll be able to figure something out.

The way most people do a next and preveious is by changing the limit number.

SELECT * FROM table LIMIT 2,1
will show you the second record. The next button simply needs to increment that number by one (LIMIT 3,1)
Of course you don't want to give a prompt for prev if the number is zero and you don't want to give a prompt for next if the number is equal to the total records.

Anon

Database Table (products) Fields
rec, websku, product, description, features, canprice, usprice, picture, picture2, picture3, picture4, picture5, category, available

So something like this?

SELECT * FROM products WHERE category=\"tables\" LIMIT 2,1

Anon

Is it possible to do a select for a product and then find the next available record with the same category?

SELECT recno, category FROM products WHERE recno=3

Next Product
SELECT recno FROM products WHERE recno > 3
(use the first returned row)

Previous Product
SELECT recno FROM products WHERE recno < 3
(use the last returned row)