SELECT - data from two tables

[denla]

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!!

 

 

[spiderwell]

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

[denla]

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?