[RESOLVED] self labeling query

gtab

Hi, i'm sure it will be easy for most of you guys, but I need help with having a query label its results. What I mean is; say I have a table with 9 songs in it, with the fields "artist, song, and genre", and there is three different genres. Say there is three rock songs, three metal songs, and three blues songs. Now, I can easily have the query just list all the song names, with the band names, and the genres, but I would like it to make categories for each one. example.

Rock
Metallica - one
Metallica - Nothing else matters
Metallica - Enter Sandman

Metal
Avenged Sevenfold - Bat Country
song here
song here

Blues
song here
song here
song here

TobyRT

I was trying to do something like that the other day. See this post here:
http://www.phpbuilder.com/board/showthread.php?t=10310660

if you Group by genre it'll come out in order anyway, but won't give you that kind of title, replace my term 'author' with 'genre' and you shouldn't go too far wrong.

gtab

thanks, that is exactly what I was looking for. I wonder though. Is it possible to sort a result data twice? Could I do a query and sort it by genre so it seperates like that and displays each title, and then sort it so it does the same thing by each author in each genre? I tried putting the original code into the "do whatever here" section, and changing all the necessary information like changing genre to author and see if that works, but given my luck lately, it won't work. could you go ahead and post what you would do?

TobyRT

You should be able to use the same bit of code, just make sure you sort by artist too:

http://dev.mysql.com/doc/refman/5.0/en/sorting-rows.html

Make sure your php if() statements run in the same way as your SQL sorting. ie if you sort by genre then artist in SQL, make sure you apply those if() statements in the same way.

Best of luck!

gtab

<?php

$db = mysql_connect("localhost", "root", "pass");

mysql_select_db("database",$db) or die ('Did not select Database');

$query = "SELECT * FROM songs ORDER BY genre, artist ASC";

$result = mysql_query($query);

$prev_genre = ''; // Initialize to something that wouldn't match the DB data

while ($row = mysql_fetch_assoc($result)) {

if ($prev_genre != $row['genre']) { 
    echo "<b>{$row['genre']}</b><br>"; // Display name once per genre 
    $prev_genre = $row['genre'];   // Set to current genre 
}

if ($prev_artist != $row['artist']) { 
    echo "{$row['artist']}<br>"; // Display name once per artist 
    $prev_artist = $row['artist'];   // Set to current artist
} 

echo "{$row['song']}<br>";

}

thanks for your help. It worked just fine. this is the code i ended up using in case anyone needed it as well.