php - Using an array in an SQL query

Question

Okay, so I want to have a news feed on my website. I have 3 tables named Users, Follow, and Posts. Basic user data goes into the Users table, who is following who is stored in the Follow table, and whatever the user posts goes into Posts. Now, I know how to select every post from a database table and limit it using the WHERE clause. But how would I write my query to select all all of the posts from only user's they are following, and display them by DESC date? Thanks.

Answer 1

Here's a general layout for you, make three tables like you mentioned, I've outlined below just as an example

[TABLES]

• users
• followers
• posts

In the users table you should have at least columns like userid (auto incremented value / primary key).

The followers table should have something like userid which would match to the users table userid, a followid column which would also have the id # for the follower from the users table.

Then for your posts table you would want to have userid too so when each post is made it would have the id from users table.

Then you would need to do something like:

SELECT p.*
FROM posts AS p
WHERE p.userid IN (SELECT followid FROM followers WHERE userid = ###)
ORDER BY p.date DESC

Now it really depends on how you are getting the users id to figure this out. If your passing the users id using a session after they logged in similar to something like Facebook, then you could change userid = ### to something like userid = ".$_SESSION['userid']." But again it really depends on how you pass the users id but the above should at least get you somewhat started.

Make sure to put indexes on the userid, followid columns so that when the table becomes larger it will do the joins quickly.