Verification: a143cc29221c9be0

News with php and mysql

The MySQL

The relational model for the feed is composed of two tables. One table functions as an activity log; in fact, it's named activity_log. The other table is newsfeed. These tables are nearly identical.

The schema for the log is activity_log(uid INT(11), activity ENUM, activity_id INT(11), title TEXT, date TIMESTAMP)

...and the schema for the feed is newsfeed(uid INT(11), poster_uid INT(11), activity ENUM, activity_id INT(11), title TEXT, date TIMESTAMP).

Any time a user does something relevant to the news feed, for example asking a question, it will get logged to the activity log immediately.


Generating the news feeds

Then every X minutes (5 minutes at the moment, will change to 15-30 minutes later), I run a cron job that executes the script below. This script loops through all of the users in the database, finds all the activities for all of that user's friends, and then writes those activities to the news feed.

At the moment, the SQL that culls the activity (called in ActivityLog::getUsersActivity()) has a LIMIT 100 imposed for performance* reasons. *Not that I know what I'm talking about.

getAllUsers();
foreach($usersArray as $userArray) {

  $uid = $userArray['uid'];

  // Get the user's friends
  $friendsJSON = $friend->getFriends($uid);
  $friendsArray = json_decode($friendsJSON, true);

  // Get the activity of each friend
  foreach($friendsArray as $friendArray) {
    $array = $activityLog->getUsersActivity($friendArray['fid2']);

    // Only write if the user has activity
    if(!empty($array)) {

      // Add each piece of activity to the news feed
      foreach($array as $news) {
        $newsFeed->addNews($uid, $friendArray['fid2'], $news['activity'], $news['activity_id'], $news['title'], $news['time']);
      }
    }
  }
}

Displaying the news feeds

In the client code, when fetching the user's news feed, I do something like:

$feedArray = $newsFeed->getUsersFeedWithLimitAndOffset($uid, 25, 0);

foreach($feedArray as $feedItem) {

// Use a switch to determine the activity type here, and display based on type
// e.g. User Name asked A Question
// where "A Question" == $feedItem['title'];

}