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开发者_如何学JAVA/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.
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>";
}
?>
精彩评论