SitePoint Sponsor

User Tag List

Results 1 to 2 of 2
  1. #1
    SitePoint Enthusiast
    Join Date
    Oct 2009
    Location
    Byron Bay, NSW, Australia
    Posts
    54
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Parsing INNER JOIN results

    I've got products that have many different pickup locations associated to them. I'm trying to display each product with a list of all its associated pickup locations.

    I've got 3 tables
    - products (products)
    - product_pickup (The lookup table)
    - pickup_locations (pickup locations)

    I'm running this SQL query to get my results:
    PHP Code:
    $result mysqli_query($link'SELECT * FROM products INNER JOIN product_pickup ON products.id = product_pickup.product_id INNER JOIN pickup_locations ON product_pickup.pickup_id = pickup_locations.id'); 
    Now I can display all the results by fetching array but basically it displays each row for each association with a pickup location. So I'm wondering what would be the most efficient way to display each product on it's own line and then having a dropdown box listing all the pickup locations associated to the product.

    i.e Instead of displaying like this:
    White water rafting - 29 jim street
    White water rafting - 93 legian street
    Surfing - 29 jim street
    Surfing - block 1 regan road

    I want to display like this:
    White water rafting - 29 jim street, 93 legian street
    Surfing - 29 jim street, block 1 regan road

  2. #2
    SitePoint Enthusiast
    Join Date
    Oct 2009
    Location
    Byron Bay, NSW, Australia
    Posts
    54
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Nevermind, got it!
    I ended up replacing the INNER JOIN query with GROUP_CONCAT()
    See bellow for future info:
    PHP Code:
    $result mysqli_query($link'SELECT products.id, products.title, products.cost, products.extra_cost, products.extra_cost_note, products.saving_note, products.picture, GROUP_CONCAT(pickup_locations.location separator "\n")
                                    FROM products, product_pickup, pickup_locations
                                    WHERE product_pickup.product_id = products.id AND product_pickup.pickup_id = pickup_locations.id
                                    GROUP BY products.id
                                    ORDER BY products.title;'
    ); 


Bookmarks

Posting Permissions

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