...

View Full Version : little help with an odd (for me...) query...?



glennn.php
08-24-2006, 04:22 AM
i have a simple table with pics, names and mp3s. ONE name and pic PER artist, sometimes multiple mp3s... the query i've managed to put together creates a table row for each, obviously, but i'd rather query for each unique pic and name and retrieve each mp3 (and ultimately print them in the right cell like "$title" is now).

could someone please lend me a hand with a query that would do this? i'd be forever grateful...

thanks loads,
g

("fk_artist" and "id_artist" are the two fields unique to each artist)



$target_qid = mysql_query("SELECT name_artist, picId, title_music FROM fp_music INNER JOIN fp_artists ON fp_music.fk_artist = fp_artists.id_artist");

$variable_string_audio.= "<table width=\"326\" border=\"0\" cellspacing=\"0\" cellpadding=\"3\"><tr><td height=\"5\" valign=\"top\"></td></tr>";
while($target_row = mysql_fetch_array($target_qid))
{
$pic = trim($target_row[1]);
$pic = ereg_replace(" ", "%20", $pic);
$name = ucwords($target_row[0]);
$title = $target_row["title_music"];

$variable_string_audio.= "<tr><td width=\"100\" height=\"100\" valign=\"top\"><a href=\"player3.php\" target=\"main\"><img src=../stars/".$pic." border=\"0\"></a></td>";
$variable_string_audio.= "<td class=\"style2\" valign=\"top\" width=\"216\" height=\"10\">";
$variable_string_audio.= "<a href=\"player3.php\" target=\"main\" class=\"style1\">".$name."</a>" . "<br><br>";
$variable_string_audio.= $title;
$variable_string_audio.= "</td></tr>";
$variable_string_audio.= "<tr><td height=\"1\" colspan=\"2\" align=\"center\"><img src=\"../imgs/gray.gif\" height=\"1\" width=\"250\"></a></td>";
}
$variable_string_audio.= "</table>";

print($variable_string_audio);

Beagle
08-24-2006, 04:17 PM
I'm not clear on what you want and what you're currently getting. What do you want to query by? Do you want a way to grab all the mp3s for a specific artist? For all artists at once?

If you do what you're doing now, you should be getting every mp3 and attached to each row that has an mp3 title in it, you also get the artist and the artist's pic. What is it you want to change?

guelphdad
08-24-2006, 04:33 PM
do you only want the pic and artist listed once and all the mp3s for that artist listed together?

something like

Black Sabbath, pic of skull, The Warning, Johnny Blade, War Pigs

as opposed to:
Black Sabbath, pic of skull, The Warning
Black Sabbath, pic of skull, Johnny Blade
Black Sabbath, pic of skull, War Pigs

If so you merely need to use GROUP_CONCAT on the field holding the mp3s
and GROUP BY the other fields.

something like this:



select
artist,
picture,
group_concat(mp3field)
from yourtable
group by
artist,
picture



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum