Going to Ross University is a very demanding endeavor partially due to the fragmentation of the student body after the island. In an attempt to unite everyone in a common place and allow flow of information between all the students we decided to create a Facebook group. This group currently houses 1200+ people and allows people to discuss rotations and ask/answer questions about sites they are going to/have done. This worked great until we realized after a while posts are “hidden” and repeat posts occur. No problem, Facebook has a search option. Unfortunately, this only allows you to go back 4 months maximum.

I tried to get everyone to switch to a forum but it seems these days forums are unnatural and no longer preferred. I decided to export all of the Facebook group data and then write my own search using my database then link back to the Facebook post (using the IDs). In theory, this seemed like a great idea however it turns out the Facebook API has the same limitation be it FQL, Graph API, and the deprecated REST API. However, I found this out after writing my own PHP script to export the group data into my own MySQL database.

Now I have decided to crontab the script so any new data will be forever preserved. Later I will write a front end to the data itself and possibly a means to keep the two in sync. Included below are the MySQL table structures as well as the PHP code.

SQL

Table: FB_Users

CREATE TABLE `fb_users` (
  `user_id` int(12) NOT NULL COMMENT 'User ID',
  `user_name` varchar(100) NOT NULL COMMENT 'User Full Name',
  UNIQUE KEY `user_id` (`user_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

Table: FB_Posts

CREATE TABLE `fb_posts` (
  `post_id` bigint(18) NOT NULL,
  `user_id` int(12) NOT NULL,
  `post_body` longtext NOT NULL,
  `post_time` datetime NOT NULL,
  `num_comments` smallint(6) NOT NULL,
  UNIQUE KEY `post_id` (`post_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

Table: FB_Comments

CREATE TABLE `fb_comments` (
  `comment_id` bigint(18) NOT NULL,
  `post_id` bigint(18) NOT NULL,
  `user_id` int(12) NOT NULL,
  `comment_body` longtext NOT NULL,
  `comment_time` datetime NOT NULL,
  UNIQUE KEY `comment_id` (`comment_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

PHP

File: index.php

<?php
//Require FB SDK. Available at https://github.com/facebook/php-sdk
require 'facebook.php';

// Configuration
$per_page = 500;
$group_id = 'insert_group_name_or_number';
$mysql_host = 'insert_host';
$mysql_user = 'insert_user';
$mysql_pass = 'insert_password';
$mysql_db = 'insert_database';

$facebook = new Facebook(array(
  'appId'  => 'insert_app_id',
  'secret' => 'insert_app_secret',
));

//Get user name if it exists
$user = $facebook->getUser();

//Check if user is valid
if ($user) {
  try {
    $user_profile = $facebook->api('/me');
  } catch (FacebookApiException $e) {
    error_log($e);
    $user = null;
  }
}

//If logged in then generate the data or generate a login link
if ($user) { $ross = $facebook->api('/'.$group_id.'/feed?limit='.$per_page); $paging = true; } else { $loginUrl = $facebook->getLoginUrl(array(
                       'scope' => 'user_groups,friends_groups,read_stream'
                       )); echo "<a href='".$loginUrl."'>Login with Facebook"; $paging = false; }

//Connect to the database
$link = mysql_connect($mysql_host, $mysql_user, $mysql_pass)
    or die('Could not connect: ' . mysql_error());

//Set database for use
mysql_select_db($mysql_db) or die('Could not select database');

//If logged in then begin to import data
while ($paging) {
  if (count($ross["data"]) == 0) { $paging = false; }
  echo "The total data points in this set are: ".count($ross["data"])."<br>";

  foreach ($ross["data"] as $post) {

    list(,$post_id) = explode("_",$post["id"]);      
    $user_id = $post["from"]["id"];
    $user_name = $post["from"]["name"];
    $post_body = $post["message"];
    $post_time = $post["created_time"];
    $num_comments = $post["comments"]["count"];
    $query = "INSERT IGNORE INTO fb_users VALUES ($user_id,'".mysql_real_escape_string ($user_name)."')";
    $result = mysql_query($query) or die('Query failed: ' . mysql_error());
    $query = "INSERT IGNORE INTO fb_posts VALUES ($post_id,$user_id,'".mysql_real_escape_string ($post_body)."','$post_time','$num_comments')";
    $result = mysql_query($query) or die('Query failed: ' . mysql_error());

    if ($num_comments) {
      foreach ($post["comments"]["data"] as $comment) {    
        list(,,$comment_id) = explode("_",$comment["id"]);      
        $user_id = $comment["from"]["id"];
        $user_name = $comment["from"]["name"];
        $comment_body = $comment["message"];
        $comment_time = $comment["created_time"];
        $query = "INSERT IGNORE INTO fb_users VALUES ($user_id,'".mysql_real_escape_string ($user_name)."')";
        $result = mysql_query($query) or die('Query failed: ' . mysql_error());
        $query = "INSERT IGNORE INTO fb_comments VALUES ($comment_id,$post_id,$user_id,'".mysql_real_escape_string ($comment_body)."','$comment_time')";
        $result = mysql_query($query) or die('Query failed: ' . mysql_error());
      }
    }
  } 
  $next = explode("/",$ross["paging"]["next"]);
  $ross = $facebook->api("/".$group_id."/".$next[4]);
  echo "Next URL will be: /".$group_id."/".$next[4]."<br><br>";

}

?>

EDIT: The problem with exporting all posts has been resolved. I will post the code soon however the project is listed on the projects page.


0 Comments

Leave a Reply

Avatar placeholder

Your email address will not be published. Required fields are marked *