problem with row numbers in query result

jicco · #1 (**permalink**) 10-12-04, 02:59

Hi guys,

Is it somehow possible to retrieve the position of a certain row in the results of a mysql query?

To be more expecific, I have a table containing products.
Now, this products belong to different product groups. The products are qualified by groups in a another table where I
use the product ID and the group ID.

Like this

Products
****************
id | name

1 product_a
2 product_b
3 product_c
4 product_d
5 product_e
6 product_f
7 product_g
8 product_h

Groups
****************
id | name

1 | group_1
2 | group_2

Products_grouped
****************
product_id | group_id

1 | 2
2 | 1
3 | 1
4 | 2
5 | 1
6 | 2
7 | 2
8 | 1

When I print some product I want to have navigation links
which will show the next or last product that belongs to the same group. Eg: if I print the product number 5, the "next" link will print the product number 8 and the "last" will print the product number 3. To achieve this I need to work with ID numbers. But how would be possible to get the last product and next product ID when they are not consecutive numbers? Im working with PHP.

Any help would be appreciated!

r937 · #2 (**permalink**) 10-12-04, 07:19

unless you're on mysql 4.1 and can use subqueries, i suggest you run separate queries

for next in group:

Code:

select min(product_id) 
  from Products_grouped
 where product_id >= 5
   and group_id = 1

where 5 and 1 are the values for the product you're currently sitting on

similar query for previous in group

Internet Services

Web Development

Digital Marketing

Consumer Tech