php/mysql help needed

[hcm]

I don't know whether to post this in the MySQL forum or this one but I'll try here first. Hopefully it will make sense what I'm looking for.

I have 2 tables in a database: songs, categories.

Songs, columns: Title, Category, Filename
Categories, columns: ID, Name

The Category column in the Songs tables is a number that corresponds to the ID# in the Categories table.

Example:
(songs) Song A - 2 - songa.wav
(categories) 2 - CategoryX

So this:
$alllist = @mysql_query("SELECT Title, Filename, Category, Name FROM songs, categories WHERE Category=categories.ID ORDER BY Category");

Would (with the proper PHP syntax) produce something like this:
Song A - CategoryX - songa.wav

Since there are several songs in each category I want to list the category name with all of the song titles underneath, like this:

CategoryX:
Song A
Song B
Song C

CategoryZ:
Song A
Song B

What is the PHP syntax to do this??

All I can come up with is this -

while ($row3 = mysql_fetch_array($alllist)){
echo($row3["Name"] . "br /><a href=""\"" . $row3["Filename"] . "\">" . $row3["Title"] . "</a><br />");
}

which lists it as -

CategoryX:
Song A
CategoryX:
Song B
CategoryX:
Song C
CategoryZ:
Song A
CategoryZ:
Song B

This is probably easy but I'm new to PHP and MySQL and I don't have any good reference books (YET).

[lieut_data]

There is probably a more clever solution involving advanced JOIN's and what not, but this should work:

$categories = @mysql_query("SELECT Id, Name FROM categories WHERE ORDER BY Name" );


while ($category = mysql_fetch_array($categories))
{
   echo "<strong>", $category['Name'], "</strong>";

   $songs = mysql_query("SELECT Title, Filename, Category FROM songs WHERE Category={$category['Id']} ORDER BY Title");

   while ($song = mysql_fetch_array($songs)) 
   {
       echo $song["Name"];
       echo "<br />";
       echo "<a href=", $song["Filename"], ">", $song['Title'], "</a>";
       echo "<br />";
   }
} 

[hcm]

That works, except that it lists all of the categories, including ones that don't have any songs listed. How do I exclude categories unless they have at least one song title? I think I have to use HAVING somewhere in there?

Can anyone recommend a good PHP/MySQL book? All I have is the Kevin Yank book (1st edition) which doesn't include many examples.

[phptek]

hcm:

Check out my previous answer to an almost identical question.

Good luck