Re: Need a Help!

[<tg-php@xxxxxxxxxxxxxxxxxxxxxx>]

Couple of things you can do:

1. Drop the song ID and only get the artist information  "SELECT distinct(Artist) from songtable".  It doesn't look like your SELECT statement needs a song, but you include the song ID as $id anyway. Any reason for that or can you drop it so you only get artist?

2. Pre-parse the results of your current query so you only get one artist and/or compile a list of song ID's while you're at it.  Instead of doing your <option> in the database query result WHILE statement, do it outside:

while (results) {
  $artistinfo[$artist][] = $id;
}

Then:

echo "<select name='blah'>";
foreach ($artistinfo as $artist => $songsarr) {
  echo "<optgroup label='$artist'>";
  foreach ($songsarr as $songid) {
    echo "<option value='$id'>$id or whatever</option>";
  }
  echo "</optgroup>";
}
echo "</select>";

3. Use a different DB structure.  I prefer this structure myself:

Table ARTISTS:
ArtistID
ArtistName
OtherArtistInfo

Table SONGS:
SongID
ArtistID
OtherSongInfo

Or, instead of having ArtistID, if more than one artist may be linked to a song, you can do:
Table xrefArtistsSongs:
ArtistID
SongID

Not sure why you'd want to do that for a song unless you're counting covers of songs and want all artists who've covered it to point to the same SongID data.

This way you can:

SELECT * from ARTISTS

for your ARTISTS <select> box

Then once an artist is selected, do a:

SELECT * from SONGS where ARTISTID = $artistid


Just some ideas.

Forgive the pseudo-code and mixed capitalizations.. I think you get the idea I'm trying to convey.

-TG

= = = Original message = = =

I have table and insert data using song_id, that means one artist can have
many song_ids. my question is how can I query distinct artist with his/her
song_id while I will not get duplicate data like duplicate artist?
my code is like this

$content .="<form id=\"form1\" method=\"post\" action=\"\">
  <select name=\"Quick\" onchange=\"MM_jumpMenu('parent',this,0)\">
    <option value=\"#\">Select Artist</option>";


    $result= $db->sql_query("SELECT distinct(artist), id FROM
".$prefix."_lyrics order by artist asc");
    if ($db->sql_numrows($result)) 
         while($row = $db->sql_fetchrow($result)) 
                 extract($row);
                 $content .="<option value='modules.php
?name=$module_name&amp;file=artist&amp;c_id=$id'>$artist</option>";
   
        

$content .="</select>
</form>";

that code is fine except it gives me duplicate artist, so I want get rid off
that duplicate. any help


___________________________________________________________
Sent by ePrompter, the premier email notification software.
Free download at http://www.ePrompter.com.

-- 
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php