MySQL query, with DISTINCT issue

[Hyperjase]

I've got a bit of a problem, I think I could do this with two queries but I would prefer to do it all as one, basically I have this code:

 

SELECT DISTINCT album FROM samdb.songlist WHERE album !='' GROUP BY album ORDER BY date_added DESC LIMIT 5

 

So that pulls all the unique album titles (except where it's empty), what I need to do now is pull the artist from the same table .... but with the DISTINCT ... it only works on one column (album) as far as I know ... what's the best way of pulling the artists for the unique album too?

 

Thanks,

 

Jason

[Barand]

SELECT DISTINCT album, artist FROM samdb.songlist WHERE album !='' ORDER BY date_added DESC LIMIT 5

 

You don't need GROUP BY and DISTINCT

[Hyperjase]

Thanks for that .. I understand how that works now, but have realised it won't work anyway.... on some albums there are multiple artists, which then shows each time for the album, whereas I just want it to show as "Various" ... but nevermind, it's fine the way it is.

 

I do have one other quick question though.

 

I want to be able to set the number shown using a drop down box, for the life of my I can't figure out how to use a $_POST in the MySQL query .. how do I do that?  Here's what I had...

 

$result = mysql_query("SELECT DISTINCT album FROM samdb.songlist WHERE album !='' GROUP BY album ORDER BY date_added DESC LIMIT '".$_POST['noalb']"'")

 

I think I have either too many "s or something odd ... but it doesn't want to know!

 

Thanks for your help,

 

Jason

[Barand]

safe way

$lim = intval($_POST['noalb']);  // ensure it's a number
$result = mysql_query("SELECT DISTINCT album 
          FROM samdb.songlist 
          WHERE album !='' 
          ORDER BY date_added DESC 
          LIMIT $lim ");