Friend system help

[Jaynesh]

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?

[gizmola]

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.

[Jaynesh]

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.

[Jaynesh]

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.

[gizmola]

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.