www.webdeveloper.com
Results 1 to 1 of 1

Thread: SQL Joins to get User's Friends Photos

  1. #1
    Join Date
    Dec 2010
    Posts
    232

    SQL Joins to get User's Friends Photos

    In my db I have the following tables/fields:-

    users
    user_id, name, surname

    (This stores the user ids and their name and surname)

    relationships
    user_id, about_id, relationship

    (This is a self join table attached to the users table showing relationships between users. A relationship of 2 means the users are friends)

    photos
    photo_id, user_id, photo_extension

    (This contains all the photos. In this table user_id is used to determine what user uploaded the photo)

    users_photos
    user_id, photo_id, is_main

    (This detemines what users are in/tagged in photos. The is_main field is used to determine if the photo has been set as a main profile picture for a user)


    What I want now though is to be able to create a friends list for a logged in user where in each friend item the following is shown:-

    - the profile picture of the friend i.e. is_main = 1 in the users_photos table
    - each friends name and surname
    - I also need the user id of each of the friends too which won't be shown but still needs to be retrieved

    How do I achieve this using sql and joins?

    So far I have the following, if we are to use a logged in user_id of 1 for example:-

    PHP Code:

    SELECT users
    .user_idusers.nameusers.surnamephotos.photo_idphotos.photo_extension 
    FROM users 
    LEFT JOIN relationships 
    ON 
    (users.user_id=relationships.about_id
    LEFT JOIN users_photos 
    ON 
    users_photos.user_id relationships.about_id 
    LEFT JOIN photos 
    ON 
    photos.photo_id users_photos.photo_id 
    WHERE relationships.user_id AND relationship 
    However using the above returns the photo_id and photo_extension as NULL

    Can anyone please help me out in fixing this.
    Last edited by jimmyoneshot; 04-17-2011 at 06:23 AM.

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

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



Recent Articles