CODEWITHSUNDEEP
phpmysqlsqljoin
Aruns
Aruns

Reputation: 49

Fetch friend details from table help needed

Here is my table structure (fun_users)

id  first_name  last_name   email   passkey place   profession  self_des    profile_img user_type   active  last_login  reg_date    reported_banned

And my friendship table is 

id  user_id friend_id   status  date_request    from_ip

And here is the query im using to fetch details of logged in user friend

SELECT `fun_friends`.`id` as fid, `fun_users`.`id` as uid, `fun_users`.`first_name`, `fun_users`.`profile_img`, `fun_users`.`profession`, `fun_users`.`place` FROM (`fun_friends`) JOIN `fun_users` ON `fun_users`.`id`=`fun_friends`.`friend_id` WHERE (`fun_friends`.user_id= '".$_SESSION['user_row_id']."' AND  `fun_friends`.`status` =1) OR (`fun_friends`.friend_id= '".$_SESSION['user_row_id']."' AND  `fun_friends`.`status` =1)

The result is 

fid uid first_name       profile_img                       profession                             place
11  47  Agnii   thumbs/2013-03-311364721555.jpg       Software engineer                                 somewhere

The query returns the details of loggedin user not his friend details. can anyone help me ?

Upvotes: 0

Views: 53

Answers (1)

John Woo
John Woo

Reputation: 263893

The query below uses a subquery which gets all the friends for specific user. Table fun_users is joined against the subquery twice because there are two columns on the subquery which are dependent on it.

SELECT  a.id AS FID,
        IF(a.user_ID = 'user_row_id_HERE', c.id, b.id) AS UID,
        IF(a.user_ID = 'user_row_id_HERE', c.first_name, b.first_name) AS first_name,
        IF(a.user_ID = 'user_row_id_HERE', c.last_name, b.last_name) AS last_name,
        IF(a.user_ID = 'user_row_id_HERE', c.profile_img, b.profile_img) AS profile_img,
        IF(a.user_ID = 'user_row_id_HERE', c.profession, b.profession) AS profession,
        IF(a.user_ID = 'user_row_id_HERE', c.place, b.place) AS place
FROM
        (
            SELECT  id, user_ID, friend_ID
            FROM    friendship
            WHERE   'user_row_id_HERE' IN (user_ID, friend_ID) AND
                    status = 1
        ) a
        INNER JOIN fun_users b
            ON a.user_ID = b.id
        INNER JOIN fun_users c
            ON a.friend_ID = c.ID

So the question arises, what happens on this line?

IF(a.user_ID = 'user_row_id_HERE', c.id, b.id) AS UID

Basically, it test for the value of the user_ID from the subquery if it is equal to the current user. If it happens to be equal, the column from table fun_users c will be returned and vice versa.

To further gain more knowledge about joins, kindly visit the link below:

Upvotes: 1

Related Questions