Non-VB query...

[steadicamop]

I'm trying to knock up a relatively simple script for accessing a database to pull out the Artist and Title of an album ... but there is a small issue of the fact that these are stored in tables which have 1 entry for each track (so it could effectively be 15 entries for the same Artist/Album). I've been trying to figure out how to pull both of these, but only once for unique results (I did use DISTINCT but it only apparently works on one column).

Here is the code I have at present:

PHP Code:

$result = mysql_query("SELECT album, artist FROM samdb.songlist ORDER BY date_added DESC LIMIT 10") 
or die(mysql_error());
echo "<table>";
echo "<tr><td colspan='2' align='center'>Ten Latest Albums</td></tr>";
// keeps getting the next row until there are no more to get
while($row = mysql_fetch_array( $result )) {
    // Print out the contents of each row into a table
    echo "<tr><td>"; 
    echo $row['artist'];
    echo "</td><td>"; 
    echo " - ".$row['album'];
    echo "</td></tr>"; 
} 
echo "</table>"; 


I found this example from another site which I've used ... it's definately not tidy, but I think is close enough for what I need to do!

What I basically need it to do is display one album title, even if there are 15 entries in the table, then if it finds any blank entries, discount them.

Hope this made some sense!

Jase

[Eikinskjaldi]

put a group by in there.

SELECT album, artist, max(date_added) as 'date_added' FROM samdb.songlist group by album,artist ORDER BY date_added DESC LIMIT 10")

Not sure how there can be blank entires though.

That's a badly un-normalised data structure.
artist_table
artist_id name birthdate favourite_color

album_table
artist_id album_id album_name other_album_data

track table
album_id track_name track_length

[steadicamop]

Originally Posted by Eikinskjaldi

put a group by in there.

SELECT album, artist, max(date_added) as 'date_added' FROM samdb.songlist group by album,artist ORDER BY date_added DESC LIMIT 10")

Not sure how there can be blank entires though.

That's a badly un-normalised data structure.
artist_table
artist_id name birthdate favourite_color

album_table
artist_id album_id album_name other_album_data

track table
album_id track_name track_length

Thanks, but that doesn't appear to do what I need it to ... it pulls the tracks from the album too -- I want it to just show the Artist of the Album (could be Various) and then the Album title -- just once for each album, not for each track, if you follow what I mean?

I do agree it's badly formatted in the database, I'm working on tidying this lot up!

Thanks for your help,

Jason

[steadicamop]

Ok, I've moved away from that issue now, there's no point trying to do all of that as I released some albums have multiple artists.

Next issue though ....

PHP Code:

$myvar = str_replace(array('+','Ad','CD','BE'), array('%','\Ad','\CD',''), urlencode($row['album'])); 


I've found some combination of letters are actually ascii hex codes for other characters -- which means if it suddenly finds the characters "Ad" it inserts a dash. I've breifly combatted this with adding a \ before it .. which seems to work, but is there an easier way of doing this? So any letter isn't taken as ascii hex?

Cheers!

Jason