denla Posted April 8, 2011 Share Posted April 8, 2011 Hi guys, This is my first time posting here - im just getting into PHP - i got a question; I have two databases: profile(id, name, interests, dob, gender, join_date, email) interests(id, profile_id, interests) id being the primary key, and profile_id being the foreign key from profile. I want to script that returns profile information and all the matching interests (one user can have multiple interests). This is what i have so far, though it does not work, and i knew it wouldnt; function get_profile($id) { $connection = mysql_open(); $query = "SELECT * "; $query .= "FROM profiles, interests "; $query .= "WHERE profile.id=" . $id; $query .= " AND interests.profile_id=" . $id; $result = @ mysql_query($query, $connection); // Transform the result set to an array (for Smarty) $entries = array(); while ($row = mysql_fetch_array($result)) { $entries[] = $row; } mysql_close($connection) or show_error();; return $entries; } Can someone please advise on how this can be done? or do i need to have two query's one for each table ? Thank you in advance!! Link to comment https://forums.phpfreaks.com/topic/233061-select-data-from-two-tables/ Share on other sites More sharing options...
spiderwell Posted April 8, 2011 Share Posted April 8, 2011 you could use a JOIN query in SQL but that would give you multiple entries for profile id, personally i would do it it with 2 loops so loop through all profiles, and inside that loop, loop through all interests against the profile id function get_profile($id) { $connection = mysql_open(); $query = "SELECT * "; $query .= "FROM profiles"; $result = @ mysql_query($query, $connection); // Transform the result set to an array (for Smarty) $entries = array(); while ($row = mysql_fetch_array($result)) { $sql2 = "SELECT * FROM interests WHERE profile_id = " . $row['id'] . ";"; } mysql_close($connection) or show_error();; return $entries; } *DISCLAIMER* i havent checked this code but its just to show you what I meant Link to comment https://forums.phpfreaks.com/topic/233061-select-data-from-two-tables/#findComment-1198617 Share on other sites More sharing options...
denla Posted April 8, 2011 Author Share Posted April 8, 2011 thanks for your response I might make a 2nd function to obtain the interests information rather then trying to put it all into one function; get_profile($id) get_interests($id) might be easier? Link to comment https://forums.phpfreaks.com/topic/233061-select-data-from-two-tables/#findComment-1198628 Share on other sites More sharing options...
Recommended Posts
Archived
This topic is now archived and is closed to further replies.