MySQL query question

[mike199026]

I have 3 DB tables

1. users(userid etc)
2. friends(friendid,userid,friendsuserid)
3. updates(updateid, userid, update)

Basically I’ve coded a script that lets you update your profile with activity(like a status) and able to become friends with one another(using friends table, userid + friendsuserid = they friends)

However, when it comes to the home page displaying these updates, I can display mine(user logged in) totally fine, but the friends updates dont show.

I’m having problems with it as it seems a lot of querys need to happen, to select the user logged in the friends table and get their friends userid… then its another select query to select all the statuses with the user logged in id and the friends id. then another select query to select the information from all the users profile to display their name alongside the status.

At the moment, I can only think of looping the query and putting id’s in an array, then putting the array into a select query for the friends updates and displaying them… is there a better way? code is a mess lol and im getting confused with the amount of querys(basically 2 of each for the friends and for the user logged in) must be an easier way lol

[astonecipher]

A loop would still be used for the output, but it sounded like you are not using a join to get everyone’s information.

[mike199026]

A loop would still be used for the output, but it sounded like you are not using a join to get everyone’s information.

I’m using this query now [php]SELECT u.*
FROM updates u INNER JOIN
(SELECT friendsuserid as userid FROM friends WHERE userid = [youruserid]
UNION
SELECT userid FROM friends WHERE friendsuserid = [youruserid]
)f
ON u.userid = f.userid[/php] however that just gets me the updates from me and my friends. Now I need the info from the “users” table that corrosponds to the updates. like userid=2 and friendsuserid=4 need info from both their profile so i can output their names and userid(in a link to link to their profile)

Plus i need an explaination how the querys work etc

[astonecipher]

Plus i need an explaination how the querys work etc

So, this is a school thing…

Is the friends table a linking table?

Write out what you want to select, from all of the tables, then write out what tables hold those columns. Inner joins join only when there is a value, outer joins, join regardless if there is a value.

[mike199026]

So, this is a school thing…

Is the friends table a linking table?

Write out what you want to select, from all of the tables, then write out what tables hold those columns. Inner joins join only when there is a value, outer joins, join regardless if there is a value.

No not a school thing

I’ve managed to mess with command line and get everything i want except now it only comes up with users im friends with and not me… want both

[php]SELECT s.status,u.name,f.friendsuserid FROM statusupdates s JOIN users u on s.userid=u.userid JOIN friends f on u.userid=f.friendsuserid;[/php]

[astonecipher]

This is where a union would come in. So you will combine the queries, one for the friend values and another for yours.

SELECT s.status, u.name, f.friendsuserid FROM statusupdates s JOIN users u ON s.userid = u.userid JOIN friends f ON u.userid = f.friendsuserid UNION SELECT s.status, u.name, f.friendsuserid FROM statusupdates s JOIN users u ON s.userid = u.userid JOIN friends f ON u.userid = f.friendsuserid;

[mike199026]

Brilliant! now how would i get the updates of only the $_SESSION[‘userid’] the user logged in and the user logged in friends?

Tried WHERE but doesn’t work

[astonecipher]

You need the where clause in both queries. Are you only after those that are logged in?

[mike199026]

You need the where clause in both queries. Are you only after those that are logged in?

Tried it in both, nothing.

Yes, say if i login… i’ve got it to put the user logged in in a session variable called ‘userid’ then i want the query to show that users(logged in user) and the related friends

[astonecipher]

What does the query look like?

[mike199026]

What does the query look like?

I’m using the SQL you gave me, just after it displaying updates of logged in user and friends

[astonecipher]

SELECT s.status, u.name, f.friendsuserid FROM statusupdates s JOIN users u ON s.userid = u.userid JOIN friends f ON u.userid = f.friendsuserid WHERE u.userID = ? UNION SELECT s.status, u.name, f.friendsuserid FROM statusupdates s JOIN users u ON s.userid = u.userid JOIN friends f ON u.userid = f.friendsuserid; WHERE f.friendsuserid = ?

I would imagine it would be something like that. The session only holds the logged in userID, correct?

[mike199026]

SELECT s.status, u.name, f.friendsuserid FROM statusupdates s JOIN users u ON s.userid = u.userid JOIN friends f ON u.userid = f.friendsuserid WHERE u.userID = ? UNION SELECT s.status, u.name, f.friendsuserid FROM statusupdates s JOIN users u ON s.userid = u.userid JOIN friends f ON u.userid = f.friendsuserid; WHERE f.friendsuserid = ?

I would imagine it would be something like that. The session only holds the logged in userID, correct?

yes thats correct the session only holds value of logged in user, but putting that query into navicat produces the same result regardless of the where clause value(only got 6 records in user table and i put “90” in the where clause and produces the same result as a value of a real userid