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 7 of 7
  1. #1
    Regular Coder
    Join Date
    Jul 2010
    Location
    Sheffield
    Posts
    824
    Thanks
    93
    Thanked 18 Times in 18 Posts

    Multi table query (PHP)

    I have two tables, members and friendships. In members there are 3 fields, `userID` and `name`, `age` , and in friendships there are 2 called `userID` and `friendID`.

    If my userID is 1 and my friends is 2 friendships table would look like

    `userID` - `friendID`
    1 - 2

    I need to run a query to select all from friendships where my userID is in userID then display the names and age of all my friends.

    I could do this using two query but surely theres a way to do it using 1? If i can could someone who me an example of how id do it?

    Thanks

    EDIT: If you need any more information just say
    Last edited by tomharto; 08-17-2011 at 02:04 PM.

  • #2
    Regular Coder
    Join Date
    Jul 2010
    Location
    Sheffield
    Posts
    824
    Thanks
    93
    Thanked 18 Times in 18 Posts
    UPDATE:

    I have this

    PHP Code:
    "SELECT t1.username, t1.user_id, t2.userID, t2.friendID, t2.accepted FROM members AS t1 JOIN friendships AS t2 ON t1.user_id = t2.friendID"
    What almost works except i need to add WHERE t1.user_id = '".$_SESSION['userID']." AND t2.accepted = '1' to it but i cant get the right place for the WHERE. Where abouts would i put it?

    Also, if i wanted to select all fields from both tables would i just put
    PHP Code:
    "SELECT * FROM members AS t1 JOIN friendships AS t2 ON t1.user_id = t2.friendID"

  • #3
    God Emperor Fou-Lu's Avatar
    Join Date
    Sep 2002
    Location
    Saskatoon, Saskatchewan
    Posts
    16,987
    Thanks
    4
    Thanked 2,660 Times in 2,629 Posts
    Try this:
    Code:
    SELECT p.userID, p.name, p.age, f.userid AS fuserid, f.name AS fname, f.age AS fage
    FROM members p
    LEFT JOIN friendships fs ON (p.userID = fs.userID)
    INNER JOIN members f ON (fs.friendID = f.userID)
    WHERE p.userid = $_SESSION['userID'] AND fs.accepted = 1
    You're second post structure and field combinations do not match what you're original definition is. I added the userid condition assuming that userID is still correct, and the accepted assuming its a part of friendships.

  • #4
    Regular Coder
    Join Date
    Jul 2010
    Location
    Sheffield
    Posts
    824
    Thanks
    93
    Thanked 18 Times in 18 Posts
    Yeah i was kinda typing and trying at once and i realized somethings were different to the original post.

    Could you explain a little about the different in joins? e.g left, inner etc.

    Also where you put 'friendships fs' does that do the same as 'friendships AS fs'?

    Now ive thought about it more i need to select everything from members so would that be 'SELECT p.*'?

    Ill try your code a little later on when i get access to an FTP again cause no doubt it will be better than to query i managed to get together
    Code:
    SELECT * FROM members AS t1 JOIN friendships AS t2 ON t1.user_id = t2.friendID WHERE t2.userID = '".$_SESSION['userID']."' AND t2.accepted = '1'

  • #5
    Super Moderator guelphdad's Avatar
    Join Date
    Mar 2006
    Location
    St. Catharines, Ontario Canada
    Posts
    2,634
    Thanks
    4
    Thanked 148 Times in 139 Posts
    1) Don't use SELECT *, actually name the columns you need. Your queries will be much clearer down the road because of it

    2) when giving an alias name the AS word is optional so yourtable AS yt or yourtable yt are interchangeable

    3) an INNER JOIN matches rows in both tables. a LEFT OUTER JOIN returns all rows from the first table and matching rows from the right table, plus null rows where there are no matches.

    If you have a list of all students in a class and want to see what marks they got on a test you can use an INNER JOIN for the two tables. But if Johnny was away for that test he won't be returned in the list of results if you use an INNER JOIN because there is no row for him in the marks table. if you want to see all students and their marks or the fact that they are missing a mark on the test, then use a LEFT OUTER JOIN with the student table first. So Johnny's row will show up but you will see a NULL next to his test score.

    There are other joins but you won't run into them as often. You can read up on CROSS JOIN, FULL JOIN (which mysql doesn't support) among others. Note that RIGHT OUTER JOIN is the same as a LEFT OUTER JOIN but all the rows from the right hand table are returned. Choose one of LEFT or RIGHT and don't mix the two as it is difficult to figure out why some rows are being returned and not others when you do so. I read left to right so choose to use a LEFT JOIN myself. Note that OUTER is an optional word but using it is helpful to remind you it is an outer join.

  • Users who have thanked guelphdad for this post:

    tomharto (08-17-2011)

  • #6
    Regular Coder
    Join Date
    Jul 2010
    Location
    Sheffield
    Posts
    824
    Thanks
    93
    Thanked 18 Times in 18 Posts
    Okay thanks , hopefully ill be able to figure out query fou-lu put, i dont like using code i dont understand, if it goes wrong later on im screwed if i dont know what it does.

  • #7
    Regular Coder
    Join Date
    Jul 2010
    Location
    Sheffield
    Posts
    824
    Thanks
    93
    Thanked 18 Times in 18 Posts
    I decided i was gonna show unaccepted users, after a bit of tweaking your code Fou-lu worked, this is what I used

    Code:
    SELECT p.user_id, p.username, f.user_id AS fuserid, f.username AS fname, fs.accepted
    FROM members p
    LEFT JOIN friendships fs ON (p.user_id = fs.userID)
    INNER JOIN members f ON (fs.friendID = f.user_id)
    WHERE p.user_id = ".$_SESSION['userID']."
    At first it kept echoing my username until i realised i needed to echo fname not username :P


  •  

    Posting Permissions

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