limitphp Posted January 5, 2009 Share Posted January 5, 2009 If I had a simple query like this: <?php "SELECT * FROM vote, songs WHERE vote.ArtistID=$artistID AND songs.songID=vote.songID $resultSongs = mysql_query($querySongs) or die (mysql_error()); ?> and I defined a variable like this: while ($rowSongs = mysql_fetch_assoc($resultSongs)) { $songName = $rowSongs['songName']; } If both vote and songs tables had a field named songName, which table does it pull from to assign $songName a value? I'm just curious...thanks. Quote Link to comment https://forums.phpfreaks.com/topic/139559-question-about-query-pulling-from-several-tables/ Share on other sites More sharing options...
premiso Posted January 5, 2009 Share Posted January 5, 2009 You need to declare at is tablename.songName, what I would suggest doing is changing the column name as such: $sql = "SELECT songs.*, vote.*, vote.songName as vtSongName, songs.songName as snSongName FROM vote, songs WHERE vote.ArtistID=$artistID AND songs.songID=vote.songID"; I am not sure if that works or not, I would actually define all columns you are using instead of using * but then you can access either one with vt or sn in front of "songname" EDIT: Thinking about it, why do you have SongName in both tables? The vote table should link to the song table via the songid, you should not duplicate data like that... Quote Link to comment https://forums.phpfreaks.com/topic/139559-question-about-query-pulling-from-several-tables/#findComment-730040 Share on other sites More sharing options...
limitphp Posted January 5, 2009 Author Share Posted January 5, 2009 I see. As of right now, its working with the *. I'm not sure which table its pulling from as the songName in each table is the same. It doesn't really matter which one it pulls from I guess, but I was just curious. I guess I can add the short vt and sn. You guys are the experts, not me. Thanks for the info. Quote Link to comment https://forums.phpfreaks.com/topic/139559-question-about-query-pulling-from-several-tables/#findComment-730044 Share on other sites More sharing options...
premiso Posted January 5, 2009 Share Posted January 5, 2009 I see. As of right now, its working with the *. I'm not sure which table its pulling from as the songName in each table is the same. It doesn't really matter which one it pulls from I guess, but I was just curious. I guess I can add the short vt and sn. You guys are the experts, not me. Thanks for the info. I would actually re-think your vote table. Do you really need songName in there if it links to the song table? No not really, you are just duplicating data which is not necessary... Quote Link to comment https://forums.phpfreaks.com/topic/139559-question-about-query-pulling-from-several-tables/#findComment-730052 Share on other sites More sharing options...
limitphp Posted January 6, 2009 Author Share Posted January 6, 2009 Actually, you know what...I don't know what I was thinking. songName is only in the songs table. songID is in both tables. But I think it has to be. Quote Link to comment https://forums.phpfreaks.com/topic/139559-question-about-query-pulling-from-several-tables/#findComment-730854 Share on other sites More sharing options...
premiso Posted January 6, 2009 Share Posted January 6, 2009 Actually, you know what...I don't know what I was thinking. songName is only in the songs table. songID is in both tables. But I think it has to be. songID does cause it is a foreign key which links the 2 tables together. As long as songname is not duplicated you are golden. Quote Link to comment https://forums.phpfreaks.com/topic/139559-question-about-query-pulling-from-several-tables/#findComment-730855 Share on other sites More sharing options...
limitphp Posted January 6, 2009 Author Share Posted January 6, 2009 Actually, you know what...I don't know what I was thinking. songName is only in the songs table. songID is in both tables. But I think it has to be. songID does cause it is a foreign key which links the 2 tables together. As long as songname is not duplicated you are golden. Is there a way to connect the foreign keys in the two tables, so mysql knows they are linked? Kind of like how ms access did it, in the relationship view...where you see all the tables and you connect the arrows to primary and foreign keys? Quote Link to comment https://forums.phpfreaks.com/topic/139559-question-about-query-pulling-from-several-tables/#findComment-731117 Share on other sites More sharing options...
premiso Posted January 6, 2009 Share Posted January 6, 2009 Actually, you know what...I don't know what I was thinking. songName is only in the songs table. songID is in both tables. But I think it has to be. songID does cause it is a foreign key which links the 2 tables together. As long as songname is not duplicated you are golden. Is there a way to connect the foreign keys in the two tables, so mysql knows they are linked? Kind of like how ms access did it, in the relationship view...where you see all the tables and you connect the arrows to primary and foreign keys? Not that I know of. It would be nice so you just delete one record and could have it trinkle down, but yea. As far as I know, nope. You can find some tutorials that mimic this behavior but yea... http://www.google.com/search?hl=en&q=mysql+relationships+foreign+key+linked+tables&btnG=Search http://www.weberdev.com/ViewArticle/Managing-Foreign-Key-Relationships-In-MySQL-Using-SQLyog Quote Link to comment https://forums.phpfreaks.com/topic/139559-question-about-query-pulling-from-several-tables/#findComment-731119 Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.