mySQL next / previous query

[aktiv]

ok say i have a table with 4 products in it, 3 of the products are in the hat category and 1 is assigned to the shirt category.

The ids are as follows:

id---------catid------prodname
1--------- 1--------- hat1
4--------- 2--------- shirt
8--------- 1--------- hat2
12-------- 1--------- hat3

each product is presented individually with a previous and next button(s) at the bottom of the page to flip from product to product...however only showing the next or previous product that are related in category.

Question is how do I get from id 1 to id 8. Would I use a less than or greater than based on the category passed on the url variable.

For example the variable to call product Id 1 would be http://abc.com/products.php?prod=1&cat=1.

Hope this makes sense. Thanks!:thumbsup:

[oracleguy]

You can use the limit function to achieve this.

Instead of the category and product ID in the URL, you could use the category and page number.

So your query might be something like:
SELECT id, prodname FROM products WHERE catid=<category id in url> ORDER BY id LIMIT <page number in url>,1

You can of course choose something else to order the query by if you want, like the name. Depends on your needs. But using the LIMIT clause, you can basically increment through a result set.

There are some other solutions to this problem but it is the first one that came to mind. If there is a better way I'm sure some of the other regulars will be along to say.

[Old Pedant]

Alternatively, you could always use LIMIT 1 and have your URL look something like this:

http://abc.com/products.php?prod=1&cat=1&dir=next
or
http://abc.com/products.php?prod=1&cat=1&dir=prev

And then (pseudo-code):

If dir==next
SELECT id, prodname FROM products
WHERE catid = [cat from url]
AND prodid > [prod from url]
ORDER BY id LIMIT 1
Else
SELECT id, prodname FROM products
WHERE catid = [cat from url]
AND prodid < [prod from url]
ORDER BY id DESC LIMIT 1

[aktiv]

Hi Guys

Thanks for your help.... I actually with with a less than more than query based on the catid, thanks again for your help.