January 8th, 2010, 01:53 PM
Complicated SQL ideas?
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,
January 8th, 2010, 02:00 PM
Pretty close. This might work:
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.
Comments on this post
January 8th, 2010, 02:09 PM
Thanks very much for the quick reply, just what I needed
January 8th, 2010, 02:37 PM
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 02:40 PM.