here's my table products_tb for products:
---------------------------------------
| product_id | prod_name | cat_id |
---------------------------------------
| 112 | Zumar | 3 |
| 131 | Xerox | 2 |
| 143 | Dan | 1 |
| 145 | Alan | 1 |
| 195 | Alice | 1 |
| 212 | Rolls | 1 |
| 215 | Wolber | 4 |
| 312 | Zero | 3 |
---------------------------------------
and i have another table for categories, products_categories:
-------------------------------------------
| cat_id | cat_order | cat_name |
-------------------------------------------
| 2 | 1 | Software |
| 4 | 2 | Hardware |
| 3 | 3 | Accessories |
| 1 | 4 | Whatever |
-------------------------------------------
my problem is that, given a product_id or product_name, I have to find the next and previous product, in alphabetical order, and considering that the next product has to be in the same category of the given product_name and ordered alphabetically by name,... and the categories are ordered by cat_order. the problem is that if the product is th last of category, i dont know how to jump to next avialble categories..
for instance:
if this is the list ordere as i said:
---------------------------------------
| 131 | Xerox | 2 |
| 215 | Wolber | 4 |
| 312 | Zero | 3 |
| 112 | Zumar | 3 |
| 145 | Alan | 1 |
| 195 | Alice | 1 |
| 143 | Dan | 1 |
| 212 | Rolls | 1 |
---------------------------------------
and if i push the ?product_id=145 (Alan) to my php page
I would like to get:
PREVIOUS = 112 - Zumar
NEXT = 195 - Alice
but i'm not able....
here my query for the NEXT but it works partially because it doesn find other thing outside one single categoriy...
SELECT *
from products_tb
left join products_categories on products_categories.cat_id = products_tb.cat_id
where
products_tb.prod_name < $name_var_given_from_GET
AND works_series_tb.works_series_id = $catid_var_given_from_GET
ORDER BY works_series_tb.works_series_ord , works_tb.works_name
desc limit 1
LET ME KNOW IF IT IS NOT CLEAR.