Parsing INNER JOIN results

PHP
1

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:

$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

Nevermind, got it!
I ended up replacing the INNER JOIN query with GROUP_CONCAT()
See bellow for future info:

$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 "\
")
								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;');