Harvesting data from the link_stat table from facebook through fql query. The script is throttled due to api rate limits.

update_stats.php

<?php
/**
 * Author: Samúel Jón Gunnarsson <[email protected]>
 * Date: 21.10.14
 * Time: 13:41
 */
$debug = 0;
$base_path="http://my.url";
// Determine if we are running in command line or not.
if (php_sapi_name() == "cli") {
    // In cli-mode
    $commandline = TRUE;
} else {
    // Not in cli-mode
    $commandline = FALSE;
}

// Logic begins
try {
    // Establish database connection
    $db = new PDO('mysql:host=localhost;dbname=greeter_analyze_sjg;charset=utf8', 'root', '');
    $db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

    // Query the database for the data. Only fetch records that have not been updated.
    $stmt = $db->query('SELECT id,nid FROM blcg_module_fbstats where updated=0 limit 5');
    while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
        $node = $base_path . $row['nid'];
        // Construct the Facebook FQL Query
        $fql  = "SELECT url, normalized_url, share_count, like_count, comment_count, ";
        $fql .= "total_count, commentsbox_count, comments_fbid, click_count FROM ";
        $fql .= "link_stat WHERE url = '$node'";
        $apifql = 'https://api.facebook.com/method/fql.query?format=json&query=' . urlencode($fql);
        $json = file_get_contents($apifql);
        $obj = json_decode($json);
        // Debug information for diagnostic
        if ($debug) {
            // Print out debug information for the Json object returned by the FQL Query.
            echo "<h3>Facebook Request result</h3>";
            echo "<pre>" . var_dump($obj) . "</pre><br>";
        }
        /*
         * This is the format that is returned by the Facebook Query
         * 'url' => string 'http://my.url/'
         * 'normalized_url' => string 'http://my.url/'
         * 'share_count' => int 1
         * 'like_count' => int 26
         * 'comment_count' => int 13
         * 'total_count' => int 40
         * 'commentsbox_count' => int 0
         * 'comments_fbid' => int 646942185372474
         * 'click_count' => int 0
        */
        $share_count = $obj[0]->share_count;
        $like_count = $obj[0]->like_count;
        $comment_count = $obj[0]->comment_count;
        $total_count = $obj[0]->total_count;
        $commentsbox_count = $obj[0]->commentsbox_count;
        $click_count = $obj[0]->click_count;
        $updated = 1;
        $node_id = $row['nid'];

        try {
            // The table that i'm using for collecting stats is blcg_module_fbstats which 
            // contains colums for the statistical info from facebook.
            $stmt2 = $db->prepare("UPDATE blcg_module_fbstats SET share_count = :share_count, like_count = :like_count, comment_count = :comment_count, total_count = :total_count, commentsbox_count = :commentsbox_count, click_count = :click_count, updated = :updated WHERE nid = :node_id AND updated=0");
            $stmt2->bindValue(':share_count', "$share_count", PDO::PARAM_INT);
            $stmt2->bindValue(':like_count', "$like_count", PDO::PARAM_INT);
            $stmt2->bindValue(':comment_count', "$comment_count", PDO::PARAM_INT);
            $stmt2->bindValue(':total_count', "$total_count", PDO::PARAM_INT);
            $stmt2->bindValue(':commentsbox_count', "$commentsbox_count", PDO::PARAM_INT);
            $stmt2->bindValue(':click_count', "$click_count", PDO::PARAM_INT);
            $stmt2->bindValue(':updated', "$updated", PDO::PARAM_INT);
            $stmt2->bindValue(':node_id', "$node_id", PDO::PARAM_INT);
            $stmt2->execute();
            $affected_rows = $stmt2->rowCount();

            // Debug information for diagnostic
            if ($debug) {
                echo "<h3>Mysql Update result</h3>";
                var_dump($stmt2->debugDumpParams());
                echo "<pre>Affected Rows: " . $affected_rows . "</pre>";
            }
            if ($affected_rows > 0 && $commandline == 0 ) {
                echo "Updating Facebook Stats for node id: " . $node_id . "<br>";
            } else {
                echo "Updating Facebook Stats for node id: " . $node_id . "\n";
            }
            // Throttle the connection since facebook only allows 600 queries per every 600 seconds
            sleep(1);
        } catch (PDOException $pdoE) {
            echo $pdoE->getMessage() . '<br/>';
            echo "<h1>Exception</h1>";
            var_dump($pdoE);
        }
    } //finish iteration
// Catch connection errors.
} catch (PDOException $e) {
    echo 'ERROR: ' . $e->getMessage();
}