Joining two tables (through a link), one which may yield multiple rows, together into one result.

Question

Lets say I've got a table listing car brands or models:

Cars:

Id | Brand
-----------
1  | BMW
2  | Audi
3  | Volvo

And I've also got another table which links features.
Link:

Id | carid | featureid
-----------------------
1  | 1     | 1
2  | 1     | 2
3  | 2     | 2
4  | 3     | 1
5  | 3     | 2
6  | 3     | 3

And I've got the table listing the features.
Features:

Id | Feature
-----------
1  | A/C
2  | 4WD
3  | Heated seats

I want to list these results on my front page like this:

BMW

• A/C
• 4WD

Audi

• 4WD

Volvo

• A/C
• 4WD
• Heated seats

What's the best/most efficient way of doing this (using PHP and MySQL)?

---

Edit: I think I'm going to redesign my Cars database to look like this instead:

Id | Brand   | Feature
----------------------
1  | BMW     | 1,2
2  | Audi    | 1
3  | Volvo   | 1,2,3
4  | Citröen | 

And then fetch Features beforehand and combine in PHP. I guess it's much easier, and I don't get a problem if I have a brand without a feature.

Answer 1

SELECT Cars.Brand As Brand, Features.Feature as Feature FROM Cars, Link, Features WHERE Cars.id = Link.carid AND Features.id = Link.featureid Order By Brand

This should be the query, then use mysql_fetch_array and print the results

<?php
  $result=mysql_query($query);
  $last='';
  while($row = mysql_fetch_array($result)){
     if($row['brand']!=$last) {
             //if the previous brand is the same don't print it out
             //otherwise save the new brand and print it out
             $last=$row['brand'];
             echo "<b>".$row['brand']."</b><br>";
             }
     echo $row['Feature'] . "<br>";    
  }
?>