MySQL Query: Join and Group

Question

I have a simple contest sign-up web form. Each contestant can check a box to be eligible to win one of 20 prizes. Upon submitting a form, a row is created in the "contestants" table. For each prize they have checked, a row in the "entries" table is created.

I am trying to make a "results" page that lists all prize names, and below each prize name all of the contestants who want to be eligible to win that prize. Prizes for which no contestants have signed up will have no contestants listed below their name.

How do I write an efficient MySQL query to easily produce such a page (using PHP)?

Tables

contestants

• id
• name
• email
• address
• city
• state

entries

• id
• contestant_id
• prize_id

prizes

• id
• name
• description

Answer 1

SELECT p.name AS prize, c.name AS contestant FROM contestants c
INNER JOIN entries e ON c.id = e.contestant_id
INNER JOIN prizes p ON e.prize_id = p.id
ORDER BY p.id

It will give you the result:

prize 1 | contestant 1.1
prize 1 | contestant 1.2
...
prize 1 | contestant 1.n
prize 2 | contestant 2.1
prize 2 | contestant 2.2
...
prize 2 | contestant 2.n
....
prize m | contestant m.n

If you want to have the list like this:

prize 1
    contestant 1.1
    contestant 1.2
    ...
    contestant 1.n  
prize 2
    contestant 2.1
    contestant 2.2
    ...
    contestant 2.n

You have to format the result in PHP.

It can be something like this:

$old_prize = '';

$result = mysql_query(<above query>);

while ($row = mysql_fetch_array($result, MYSQL_ASSOC)) {

    if($old_prize != $row["prize"]) {
        $old_prize = $row["prize"];
        echo "Prize: ".$row["prize"].'<br />'; 
    }

    echo $row["contestant"].'<br />;
}