This should be simple but I am struggling.

 

I want to create a list of music that looks like this:

 

rock

album1

artist1

 

album2

artist2

 

blues

 

album3

artist3

 

album4

artist4

etc.

 

I have a table (music) with a key, and a column for each 'genre' 'album_name' and 'artist'.

 

I can't work out the PHP/mySQL code to make it list each genre only once as a heading. Also want to order it alphabetically but genre then artist name but that should be no problem.

 

Thanks for any help ...

I would think you need to have separate tables - one containing the genre info, one containing the album info and one containing the artist info (and one containing song info if ou go that far). And then keys in each one to link them.

 

This is because a genre will have many albums, an album could have many artists, artists many albums...

 

It's called normalisation and will make managing, querying and manipulating the data much easier for you.

 

for my example there will definently not be any artists entered twice. I have tried to get my head around normalisation and how to call results from more than one table but have not understood it yet. So would like to find a solution using the same table...

 

the closest I have got is this, but it prints the first genre only once and does not print the next genre. It then lists all artists and albums down the page...

 

$printed = FALSE; // Flag variable.

 

// Fetch each:

while ($messages = mysqli_fetch_array($r, MYSQLI_ASSOC)) {

 

// print the genre once

if (!$printed) {

echo "<h2>{$messages['genre']}</h2>\n ";

$printed = TRUE;

}

echo "{$messages['artist']} <br /> {$messages['album_name']} <br /><br /><br />\n";

}

I agree with wizely. Normalisation

 

This will work. But I would not use this myself.

$currentGenre = '';
while ($messages = mysqli_fetch_array($r, MYSQLI_ASSOC)) {
if ($currentGenre != $messages['genre']) {
	echo "<h2>{$messages['genre']}</h2>\n";
	$currentGenre = $messages['genre'];
}
echo "{$messages['artist']}<br />{$messages['album_name']}<br /><br />\n";
}

It just loops over each item, and keeps track of which is the current genre. When it finds one which is different, it will print it.

 

IMPORTANT: make sure your SQL query orders by genre! (example: SELECT * FROM your_table ORDER BY genre ASC) Otherwise, it will give you some pretty silly output.

Fab - I'll give that a try and let you know how I get on ...

Fab - I'll give that a try and let you know how I get on ...

And it's working well ... Top stuff.