如何从数据库中选择所有帖子？

Question

In database in table user I have three columns:

id
name
friends

In column friends are names of people who are friends with person whose name is stored in column name. Column friends looks like this:

friendname1,friendname2,friendname3,friendname4

Each of those friends have it's own row where name is equal to their name.

I also have another table called post where I have four columns:

id
name_posted
post
visible

What I would like now, is to select all posts from table post where name_posted is equal to name of the logged in user or any of his friends which are stored in column friends in table user.

Name of the logged in user is stored in variable $user.

For selecting only posts from logged in user I can use this:

$all_posts = mysqli_query($connect_db, "SELECT * FROM post WHERE name_posted='$user' AND visible='yes'");

but I don't know how to include to select also posts from his friends. Something like Facebook have, when you log in and you see your posts plus your friends posts. I don't know how they created that. Sorry for long post, I just wanted to give you detailed description.

Answer 1

Maybe with two querys, first select friends something like this:

SELECT * FROM user WHERE name='$user' 

You then have all his friends in string like this if I understood correctly :

friend1,friend2,friend3...

Explode $row['friends'] -> explode(',',$row['friends']); to get all friends names in array and then you can do another select in foreach loop to get all posts from friends and display it the way you like or you can even better do IN in query

select * from posts where name_posted IN ($row['friends'])

this is the other way, which would be longer

foreach($friendarray as $k=>$friend){
    ...
      mysqli_query($connect_db,
          "SELECT * from post where name_posted='$friend' AND visible='yes'");
    ...
}

and also the query you already have to get own posts. Don't forget to escape all values and stuff...

You could also join two tables but I cant write that query from my mind , would have to sit down and try it with real data but that would be ultimate solution for you.

Don't know if I hit it right but shout if you need help