Re: Need a Help!
| From: | tg-php at gryffyndevelopment dot com | Date: | Mon, 12 Dec 2005 20:44:04 +0000 |
| Subject: | Re: Need a Help! | ||
| Groups: | php.db | ||
| Request: | Send a blank email to php-db+get-40855@lists.php.net to get a copy of this message | ||
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.