Web Analytics Made Easy -
StatCounter MySQL query question - CodingForum

Announcement

Collapse
No announcement yet.

MySQL query question

Collapse
X
 
  • Filter
  • Time
  • Show
Clear All
new posts

  • MySQL query question

    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

    Query for displaying logged in user and friends updates

    Code:
    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
    However, i would like an explaination to how this works please. Plus I still need a way to select username from the users table to display their name and a userid link to their profile along with the status. can that be done using same query?
Working...
X