[PHP-DB] help on mulit query
Hi I have three tables set out below: xp_artist: artist_id , artist _name xp_sings: artist_id , songs_id xp_songs: songs_id , song_name , lyrics I can not figure out how to query and return the results for a query like: select song_name FROM xp_artist,xp_sings,xp_songs WHERE xp_artist.artist_id = xp_sings.artist_id AND xp_sings.song_id = xp_songs.songs_id AND artist_name LIKE 'b%';
Re: [PHP-DB] help on mulit query
You seem to be doing it fine as far as I can see, except for song_id which should be songs_id in the query... Bogdan Barry Rumsey wrote: Hi I have three tables set out below: xp_artist: artist_id , artist _name xp_sings: artist_id , songs_id xp_songs: songs_id , song_name , lyrics I can not figure out how to query and return the results for a query like: select song_name FROM xp_artist,xp_sings,xp_songs WHERE xp_artist.artist_id = xp_sings.artist_id AND xp_sings.song_id = xp_songs.songs_id AND artist_name LIKE 'b%'; -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] To contact the list administrators, e-mail: [EMAIL PROTECTED]
Re: [PHP-DB] help on mulit query
I did not see that ( I must be blind hehe ). One other question , I have the lyrics also stored in the database, how can I create a link so that when the query is returned they can click on song name and the lyrics will be viewed? - Original Message - From: Bogdan Stancescu [EMAIL PROTECTED] To: Barry Rumsey [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Sent: Monday, January 07, 2002 1:51 PM Subject: Re: [PHP-DB] help on mulit query You seem to be doing it fine as far as I can see, except for song_id which should be songs_id in the query... Bogdan Barry Rumsey wrote: Hi I have three tables set out below: xp_artist: artist_id , artist _name xp_sings: artist_id , songs_id xp_songs: songs_id , song_name , lyrics I can not figure out how to query and return the results for a query like: select song_name FROM xp_artist,xp_sings,xp_songs WHERE xp_artist.artist_id = xp_sings.artist_id AND xp_sings.song_id = xp_songs.songs_id AND artist_name LIKE 'b%'; -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] To contact the list administrators, e-mail: [EMAIL PROTECTED] -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] To contact the list administrators, e-mail: [EMAIL PROTECTED]
Re: [PHP-DB] help on mulit query
Your query looks fine, except that it should be xp_sings.songs_id. What you are trying to resolve is a many to many relationship where many artists can sing the same song, hence you have created the table xp_sings. This is also known as a pivot table. What problem are you having? Have you echoed your SQL statement? Tried it at the console? Miles Thompson At 01:40 PM 1/7/2002 +1300, Barry Rumsey wrote: Hi I have three tables set out below: xp_artist: artist_id , artist _name xp_sings: artist_id , songs_id xp_songs: songs_id , song_name , lyrics I can not figure out how to query and return the results for a query like: select song_name FROM xp_artist,xp_sings,xp_songs WHERE xp_artist.artist_id = xp_sings.artist_id AND xp_sings.song_id = xp_songs.songs_id AND artist_name LIKE 'b%'; -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] To contact the list administrators, e-mail: [EMAIL PROTECTED]
Re: [PHP-DB] help on mulit query
Barry,
Execute your query, fetching song_id as well as song_name, returning the
result handle to $result (or any name you're comfortable with) and present
your song titles like so ..
while ($myrow = mysql_fetch_array($result))
{
printf(a href=\%s?songs_id=%s\%s /a \n, $LYRIC_URL,
$myrow[songs_id], $myrow[song_name]);
}
This assumes that songs_id is the key for the lyrics in the lyrics table.
Presenting this neatly in a table is left up to you! Have fun.
Miles Thompson
At 02:13 PM 1/7/2002 +1300, Barry Rumsey wrote:
I did not see that ( I must be blind hehe ). One other question , I have the
lyrics also stored in the database, how can I create a link so that when the
query is returned they can click on song name and the lyrics will be viewed?
- Original Message -
From: Bogdan Stancescu [EMAIL PROTECTED]
To: Barry Rumsey [EMAIL PROTECTED]
Cc: [EMAIL PROTECTED]
Sent: Monday, January 07, 2002 1:51 PM
Subject: Re: [PHP-DB] help on mulit query
You seem to be doing it fine as far as I can see, except for song_id
which should be songs_id in the query...
Bogdan
Barry Rumsey wrote:
Hi
I have three tables set out below:
xp_artist: artist_id , artist _name
xp_sings: artist_id , songs_id
xp_songs: songs_id , song_name , lyrics
I can not figure out how to query and return the results for a query
like:
select song_name FROM xp_artist,xp_sings,xp_songs WHERE
xp_artist.artist_id = xp_sings.artist_id AND xp_sings.song_id =
xp_songs.songs_id AND artist_name LIKE 'b%';
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
To contact the list administrators, e-mail: [EMAIL PROTECTED]
Re: [PHP-DB] help on mulit query
Just echo a link of the form a href=\lyrics.php?song_id=$song_id\Click for lyrics/a and use the incoming song_id in lyrics.php to get it from the database. Bogdan Barry Rumsey wrote: I did not see that ( I must be blind hehe ). One other question , I have the lyrics also stored in the database, how can I create a link so that when the query is returned they can click on song name and the lyrics will be viewed? -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] To contact the list administrators, e-mail: [EMAIL PROTECTED]
