我无法弄清楚如何编写一个SQL查询来显示我想要的信息

Question

I've gotten my database set up for my website and I'm a little baffled on how I should write the SQL query to display the information how I want it.

Here's an image of how my database is setup and an example of how I want to display it:

Here's the SQL query I wrote to get the information (Im open to writing a better solution)

SELECT * FROM drinks_category, drinks_lookup, drinks
WHERE drinks.drink_id = drinks_lookup.drink_id
AND drinks_lookup.drinks_category_id = drinks_category.drinks_category_id

This gets me all the info but I'm not sure how to write the output to get it to show how i want.

Answer 1

One solution is to order by category:

SELECT * 
FROM drinks_category, drinks_lookup, drinks 
WHERE drinks.drink_id = drinks_lookup.drink_id 
AND drinks_lookup.drinks_category_id = drinks_category.drinks_category_id
ORDER BY drinks_category.drink_category_title

With that, you can loop in PHP, and output a new header whenever a new category appears. Something like this:

<?php
$last_category = 0;
foreach($data as $row) {
    if($row['drinks_category_id'] != $last_category) {
        echo '<h1>' . $row['drink_category_title'] . '</h1>';
    }
    echo '<div>' . $row['drinks_name'] . '</div>';
    $last_category = $row['drinks_category_id'];
}
?>

You should probably use nested html lists instead of the markup above, but this should get you started.