开发者

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

开发者 https://www.devze.com 2022-12-22 18:13 出处:网络
Lets say I\'ve got a table listing car brands or models: Cars: Id | Brand ----------- 1| BMW 2| Audi 3| Volvo

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

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.


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>";    
  }
?>
0

精彩评论

暂无评论...
验证码 换一张
取 消