Jaynesh Posted July 7, 2011 Share Posted July 7, 2011 Hello I am currently working on a friend system. The basic functionality I'm going for is if a user posts something, it will only be visible to his friends and himself. Here is my table layout so far: Users (id, username, password, email) Posts (id, user_id, post) Friends (user_id, friend_id) I've managed to display the post to just the submitter alone. How would I go about displaying it to all the friends? Quote Link to comment Share on other sites More sharing options...
gizmola Posted July 7, 2011 Share Posted July 7, 2011 You join from friends to posts on posts.user_id = friends.user_id AND friends.friend_id = {id of current user} OR posts.user_id = {id of current user} . I don't know if you omitted it in your description but posts certainly needs a datetime or timestamp so you can order the posts in some fashion -- most recent first being the typical approach. Quote Link to comment Share on other sites More sharing options...
Jaynesh Posted July 7, 2011 Author Share Posted July 7, 2011 Hello I'm still a beginner so you might have to spoon feed me. This is the code I created from your response. $display = "JOIN * FROM Friends TO Posts ON Posts.user_id = Friends.user_id AND Friends.friend_id = $user OR posts.user_id = $user"; I don't think it is correct, because it doesn't work. Quote Link to comment Share on other sites More sharing options...
Jaynesh Posted July 7, 2011 Author Share Posted July 7, 2011 Here is an example of my User table and Friends table User id 1 2 3 4 username John Billy Jacob Tina password eqfnj1k3 123ejkn13 132kjenf 123ekn email email@john.com email@billy.com email@jacob.com email@tina.com Friends User_id 1 2 3 4 Friend_id 2 1 4 3 So John and Billy are friends and Jacob and Tina are friends. Quote Link to comment Share on other sites More sharing options...
gizmola Posted July 7, 2011 Share Posted July 7, 2011 I understood your structure when I answered the question. You are right that your syntax is wrong. Please google or read http://dev.mysql.com/doc/refman/5.0/en/join.html Something close to this should work SELECT p.* FROM Friends f JOIN (Post p) ON (p.user_id = f.friend_id AND f.user_id = $userid OR p.user_id = $userid) You also need phpMyadmin or command line mysql access to be able to test out queries in advance. Obviously you replace where you're going to have variables with a literal id# that you have in your test data so you can have a reasonable expectation that the query will work once it's coded in your php script. Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.