Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations gkittelson on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

how to join these 2 tables?

Status
Not open for further replies.
keith23

keith23

Technical User
May 26, 2005
97
NL
Hi all . I wonder how i can join playlist table and files table so for each Rec_ID in playlist table i get corrspondent artist,title,album and ID from files table.(bold parts) My current query only produces IDs in a perticuler playlist .

SELECT * FROM playlist WHERE playlistname='$playlistname'

But i be happy if some one show me how i can produce artist,title,album and ID for each songs in playlist table by passing the query the playlistname. Thanks


Note ID in files table are the same as Rec_ID in playlist table.
files:
ID, filename, artist, title, album, track

playlist:

User_ID ,Rec_ID ,playlistname

playlists:
playlistname, User_ID. songsno



Code: 
<?php

$playlistname   = $HTTP_GET_VARS['playlistname'];

$user = "root";
$pw = "";
$db = "test2";
$mysql_access = mysql_connect("localhost", $user, $pw);
mysql_select_db($db, $mysql_access);

[B]$query  = "SELECT * FROM playlist WHERE playlistname='$playlistname'";[/B]
$result = mysql_query($query);



?>



<html>

<head>
<meta http-equiv="Content-Type"
content="text/html; charset=iso-8859-1">
<title>Untitled </title>
</head>

<body>

<table  width="95%" id="table1" bordercolorlight="#008ED2" style="border-style: dashed">
      			<form action="/members/ViewPlayList.asp" method=post >
					<input name="go" type="hidden" value="yes">
					<input name="list" type="hidden" value="2205">
 				 <tr>
    				<td width="5%">&nbsp;</td>
   					<td width="30%"><b>Song Name</b></td>
    				<td width="30%"><b>Singer - > Album</b></td>
    				<td width="10%"><b>Rate It</b></td>
    				<td width="5%"><b>Hits</b></td>
  				</tr>

<?

while($row = mysql_fetch_assoc($result))
{
    
 

?>
	 <tr bgcolor="#C0C0C0">
    				<td width="3%"><input type="checkbox" name="id" value=" [B]<?=intval( $row['Rec_ID'] );?>[/B] "></td>
   					<td width="30%"><a href="javascript:newWindow('../player/player.asp?id=<?=intval( $row['Rec_ID'] );?>')">[B]Title[/B]</a></b>&nbsp;</td>
    				<td width="30%"><a href="../Albums.asp?singerID=1380">[B]Artist[/B]</a> -  
    				<a href="../AlbumSongs.asp?AlbumId=1729&Album=Siya Mo&Singer=Ahmad Sha&SingerID=1380">
    				[B]Album[/B]
    				</a>
    				</td>
    				<td width="10%"><a href="javascript:newWindow('../RateSong.asp?SongID=28484')"><img border="0" src="[URL unfurl="true"]http://www.afghanmtv.com/images/RatIt.gif"[/URL] width="90" height="24" longdesc="Rate This Song" alt="Rate This Song"></a>&nbsp;</td>
    				<td width="5%">98&nbsp;</td>
  				</tr>

<?


} 


?>
 
Sort by date Sort by votes
There are several ways, but this should work...

SELECT * FROM playlist, files, playlists
WHERE files.ID = Rec_ID AND playlist.User_ID = playlists.User_ID

Mark

SELECT * FROM management WHERE clue > 1
> 0 rows returned

--ThinkGeek T-Shrit
 
Upvote 0 Downvote
many thanks kozusik for u reply. well i am looking for a query that i pass it playlistname from playlist table and it produces for me title,artis,album and id of those songs. There are 13 songs in current playlist table but when i run this query it produces for more then 13 records instead of just 13 records!! I be happy if some one help me with this.Thanks
 
Upvote 0 Downvote
Code: 
select F.ID
     , F.filename
     , F.artist
     , F.title
     , F.album
     , F.track
  from playlist as P
inner
  join files as F
    on F.ID = P.Rec_ID

r937.com | rudy.ca
 
Upvote 0 Downvote
r937 many thanks for u reply. I want similer query but not foll all songs in playlist table. I want to be able to pass the query with playlistname and it only displays ID,artist,title,album for those songs .Example in the pic i want to display ID,title,artist,album for playlistname dance

playlistname.jpg
 
Upvote 0 Downvote
You still need to stick the appropriate WHERE clause on the back!

________________________________________________________________
If you want to get the best response to a question, please check out FAQ222-2244 first.
'If we're supposed to work in Hex, why have we only got A fingers?'
Drive a Steam Roller
 
Upvote 0 Downvote
johnwm when i use this query inside my php i can not print their values inside html code(bold part). I get zero values. could u tell me how i can refrence them?

Code: 
[b]$query  = "select F.ID, F.filename, F.artist, F.title, F.album, F.track from playlist as P inner join files as F on F.ID = P.Rec_ID where playlistname='$playlistname'";[/b]

Code: 
while($row = mysql_fetch_assoc($result))
{
    

?>
	 <tr bgcolor="#C0C0C0">
    				<td width="3%"><input type="checkbox" name="id" value="<?=intval( $row['ID'] );?>"></td>
   					<td width="30%"><a
                        href="javascript:newWindow('[URL unfurl="true"]http://localhost/wimpy5recursive/mp3s/myWimpy.php?queryWhere=Id&queryValue=<?=intval([/URL] $row['ID'] );?>')">[b]<?=intval( $row['title'] );?>[/b]</a></b>&nbsp;</td>
    				<td width="30%"><a href="../Albums.asp?singerID=1380">[b]<?=intval( $row['artist'] );?> [/b] </a> -  
    				<a href="../AlbumSongs.asp?AlbumId=1729&Album=Siya Mo&Singer=Ahmad Sha&SingerID=1380">
    				[b]<?=intval( $row['album'] );?>[/b]
    				</a>
    				</td>
    				<td width="10%"><a href="javascript:newWindow('../RateSong.asp?SongID=28484')"><img border="0" src="[URL unfurl="true"]http://www.afghanmtv.com/images/RatIt.gif"[/URL] width="90" height="24" longdesc="Rate This Song" alt="Rate This Song"></a>&nbsp;</td>
    				<td width="5%">98&nbsp;</td>
  				</tr>

<?


}
 
Upvote 0 Downvote
Sorry I don't speak PHP, and this is the MySQL forum - try forum434

________________________________________________________________
If you want to get the best response to a question, please check out FAQ222-2244 first.
'If we're supposed to work in Hex, why have we only got A fingers?'
Drive a Steam Roller
 
Upvote 0 Downvote
Try this...
SELECT * FROM playlist, files
WHERE ID = Rec_ID AND playlistname='$playlistname'

or

SELECT * FROM playlist
LEFT OUTER JOIN files ON ID=Rec_ID
WHERE playlistname='$playlistname'

You can use this in your php to show you how to reference the results.
echo '<pre>';print_r($row);echo "</pre>";die;

From what I can gather, either of those should give you what you want.

Mark

SELECT * FROM management WHERE clue > 1
> 0 rows returned

--ThinkGeek T-Shrit
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

EdLentz
  • Locked
  • Question
Need to compare two tables and list the differences
Replies
3
Views
432
EdLentz
EdLentz
EdLentz
  • Locked
  • Question
Looking for suggestions on how to do this
Replies
4
Views
396
Chris Miller
Chris Miller
BobThornton
  • Locked
  • Question
Drop/Truncate tables based on a stored procedures value 1
Replies
6
Views
586
BobThornton
BobThornton
EdLentz
  • Locked
  • Question
How do I use a keyword in a query??
Replies
6
Views
540
EdLentz
EdLentz
Arthur Lewis
  • Locked
  • Question
How to effect a NOWAIT in the LOCK TABLES statement?
Replies
0
Views
132
Arthur Lewis
Arthur Lewis

Part and Inventory Search

Sponsor

Back
Top