Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 3 of 3
  1. #1
    New to the CF scene
    Join Date
    Apr 2006
    Posts
    3
    Thanks
    0
    Thanked 0 Times in 0 Posts

    little help with an odd (for me...) query...?

    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)

    Code:
    $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);

  • #2
    Senior Coder
    Join Date
    Jul 2005
    Location
    New York, NY
    Posts
    1,084
    Thanks
    4
    Thanked 19 Times in 19 Posts
    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?

  • #3
    Super Moderator guelphdad's Avatar
    Join Date
    Mar 2006
    Location
    St. Catharines, Ontario Canada
    Posts
    2,631
    Thanks
    4
    Thanked 147 Times in 138 Posts
    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:

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


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •