#1
  1. User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Feb 2005
    Location
    UK
    Posts
    471
    Rep Power
    76

    Complicated SQL ideas?


    Hi All,
    I'm currently in a dilemma with some SQL that is called from my PHP Script.

    Here's the idea:
    I have 3 tables in a database, one full of users - tblusers, one full of user's friends - tblfriends, and one full of 'shouts' (messages) - tblshouts

    The users is simple: id, username, password..
    User's friends has 2 fields: userId, friendId (the idea being that a search for a usersId can produce all the friends)
    and finally is shouts which has 2 fields: userId, shout. (so everytime a user 'shouts' it is entered here).

    Now is the confusing SQL, what I want to do is get a list of the users friends from the friend table, based on the id in the users table (this part is easy). I then need to get the last 15 shouts made by users in the results of the friends table search.

    A basic SQL type thing would be: SELECT * FROM tblshouts WHERE userId = (results from SELECT * FROM tblfriends WHERE userId = 'Id from tblusers')

    Is there a way of doing this with some kind of PHP loop/complicated SQL?

    Thanks for any help,
    Sephers.
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Intermediate (1500 - 1999 posts)

    Join Date
    Jun 2007
    Posts
    1,515
    Rep Power
    1427
    Pretty close. This might work:
    sql Code:
    SELECT * FROM tblshouts 
      WHERE userId IN (SELECT friendId FROM tblfriends WHERE userId = 'Id from tblusers') 
      ORDER BY date_column DESC LIMIT 1,15

    I'd rather use a join, though.

    Anyway: This isn't really a php related question and might be more suitable in the forum for your dbms.

    Regards, Jens

    Comments on this post

    • sephers181 agrees
  4. #3
  5. User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Feb 2005
    Location
    UK
    Posts
    471
    Rep Power
    76
    Thanks very much for the quick reply, just what I needed
  6. #4
  7. Super Moderator
    Devshed Specialist (4000 - 4499 posts)

    Join Date
    Jul 2003
    Location
    London, UK
    Posts
    4,004
    Rep Power
    2794
    Would that not mean that you can only assign one user to a friend?

    [EDIT] I misread your post. Ignore the previous.
    Last edited by Winters; January 8th, 2010 at 03:40 PM.
    [PHP] | [Perl] | [Python] | [Java] != [JavaScript] | [XML] | [C] | [C++] | [LUA] | [MySQL] | [FirebirdSQL] | [PostgreSQL] | [HTML] | [XHTML] | [CSS]

IMN logo majestic logo threadwatch logo seochat tools logo