Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 4 of 4
  1. #1
    New Coder
    Join Date
    Apr 2011
    Posts
    92
    Thanks
    26
    Thanked 0 Times in 0 Posts

    SELECT data from multiple tables

    In a php page script (friends.php) I need to query two tables. One is "friends" table and the other is "members" table.

    In a first query, I use:
    Code:
    $sql = mysql_query("SELECT * FROM friends WHERE (requesting_id=$id OR requested_id=$id) AND connect_type='1'");
    
    while($row = mysql_fetch_array($sql)) {
    	$friend_id1 = $row[requesting_id];
    	$friend_id2 = $row[requested_id];
    if($friend_id1 != $id){
    	$true_friend = $row[requesting_id];
    } else {
    	$true_friend = $row[requested_id];
    }
    To select all rows where two members are friends, which stores them into a variable $true_friend.

    Now, what I want to do, is grab the $true_friend's (id, firstname, and lastname) from my "members" table, so that I can display a list of the member's friends, using:

    Code:
    $outputList .= '
    		<table width="100%">
                    <tr>
                    <td width="13%" rowspan="2"><div style=" height:50px; overflow:hidden;"><a href="profile.php?id=' . $id . '" target="_self">' . $user_pic . '</a></div></td>
                    <td width="14%" class="style7"><div align="right">Name:</div></td>
                    <td width="73%"><a href="profile.php?id=' . $id . '" target="_self">' . $firstname . ' ' . $lastname . '</a> </td>
                    </tr>
    		</table>
    	        <hr />';
    So How would I set up the query for the member table, for each value of $true_friend...?

  • #2
    New Coder
    Join Date
    Nov 2010
    Posts
    43
    Thanks
    4
    Thanked 2 Times in 2 Posts
    Check: http://www.w3schools.com/sql/sql_join.asp

    Will explain it all, click the 'Next' bit at the bottom to read the other bits because there are different types of joins. I think this is what you're trying to achieve.

  • #3
    New Coder
    Join Date
    Apr 2011
    Posts
    92
    Thanks
    26
    Thanked 0 Times in 0 Posts
    Forget what I said here
    Last edited by Juniper747; 05-22-2011 at 04:07 PM. Reason: Forget what I said here

  • #4
    New Coder
    Join Date
    Apr 2011
    Posts
    92
    Thanks
    26
    Thanked 0 Times in 0 Posts
    I have two tables:

    'members' with columns id, firstname, lastname
    'friends' with columns requesting_id, requested_id, connect_type

    The main problem I am having is that my $id (of the current logged in member) can either be in the requesting_id column or requested_id column of the 'friends' table.

    Now, once I located all instances of this $id from the 'friends' table, I want to select the "other" id (either requested_id or requesting_id, which would be the friend of the logged-in member) and get their id, firstname, lastname from the 'members' table. So I can echo them out as a list of members who are friends with the current logged in member.

    I am just having trouble coming up with a JOIN query that would. I really appreciate your help.


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •