SitePoint Sponsor

User Tag List

Results 1 to 4 of 4
  1. #1
    SitePoint Member
    Join Date
    Feb 2003
    Location
    usa
    Posts
    21
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Question php/mysql help needed

    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).

  2. #2
    La la la la la bronze trophy lieut_data's Avatar
    Join Date
    Jun 2003
    Location
    Waterloo, ON
    Posts
    1,517
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    There is probably a more clever solution involving advanced JOIN's and what not, but this should work:

    PHP Code:
    $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 />";
       }

    My name is Steve, and I'm a super-villian.

  3. #3
    SitePoint Member
    Join Date
    Feb 2003
    Location
    usa
    Posts
    21
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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.

  4. #4
    SitePoint Addict phptek's Avatar
    Join Date
    Jun 2002
    Location
    Wellington, NZ
    Posts
    363
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    hcm:

    Check out my previous answer to an almost identical question.

    Good luck


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •