Search code examples
phpsqlfeed

How would I make a Twitter-like feed using SQL/PHP?

On the site I am building, users can follow others and post things. I am trying to compile a feed page that allows users to see all the posts in chronological order from people they follow (pretty much what Twitter does).

The SQL table for holding "posts" (entitled "posts") is set up with the columns:

  • "id" (autoincremented)
  • "material" (text content of post)
  • "whencreated" (when posted)
  • "authorid" (user that posted it)

The SQL table for holding following/followers (entitled "follow") contains the columns:

  • "id"(autoincremented)
  • "followerid" (id of follower)
  • "followingid" (id of who they are following)

I essentially need to know how to loop through a person's "following" ids and list out the posts by each following id in chronological order. Once again, the result is pretty much a clone of Twitter.

Thanks! Sorry if this is too specific.

Solution

  • Something like this maybe?

    SELECT posts.* FROM posts
INNER JOIN follow ON posts.authorid = follow.followingid
WHERE follow.followerid = ?
ORDER BY posts.whencreated DESC

    Where ? is the ID of the current user.

    I suggest you to have a look at these info pages about INNER JOIN and ORDER BY.

    This is only the SQL part, I'm going to leave the PHP part as an exercise.