View Full Version : Multi table query (PHP)
08-17-2011, 12:44 PM
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?
EDIT: If you need any more information just say :)
08-17-2011, 02:36 PM
I have this
"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
"SELECT * FROM members AS t1 JOIN friendships AS t2 ON t1.user_id = t2.friendID";
08-17-2011, 03:39 PM
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.
08-17-2011, 03:49 PM
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
SELECT * FROM members AS t1 JOIN friendships AS t2 ON t1.user_id = t2.friendID WHERE t2.userID = '".$_SESSION['userID']."' AND t2.accepted = '1'
08-17-2011, 03:55 PM
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.
08-17-2011, 04:46 PM
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.
08-17-2011, 06:33 PM
I decided i was gonna show unaccepted users, after a bit of tweaking your code Fou-lu worked, this is what I used
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