Results 1 to 2 of 2
  1. #1
    Join Date
    Oct 2004
    Posts
    1

    Unanswered: php/mysql row numbers in query result

    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!
    Last edited by jicco; 10-12-04 at 04:04.

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •