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&file=artist&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
