PHP - Display social statistics for each article

Today, almost every website has a like, share, tweet, google +1 buttons for each post on their home or category page, which slows down your page load time unless you are loading them asynchronously. For that reason, or from a design perspective, this tutorial will guide you on how to create a social statistics system which will allow you to display stats for each article on your home page without slowing the page load.

php social statistics

Social statistics - mysql table structure

This is a snippet of my articles table structure:

  • id - unique id of the article
  • title - article title
  • url - article url

CREATE TABLE `articles` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `title` varchar(100) COLLATE utf8_unicode_ci NOT NULL,
  `url` varchar(100) COLLATE utf8_unicode_ci NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

What we will do is create another table for the social statistics and import the article ids.

  • id - unique id of the article/post
  • fb_shares - number of facebook shares
  • fb_likes - number of facebook likes
  • g_plus - number of google +1
  • tweets - number of tweets

CREATE TABLE `social_stats` (
  `id` int(10) unsigned NOT NULL,
  `fb_shares` mediumint(8) unsigned DEFAULT '0',
  `fb_likes` mediumint(8) unsigned DEFAULT '0',
  `g_plus` mediumint(8) unsigned DEFAULT '0',
  `tweets` mediumint(8) unsigned DEFAULT '0',
  PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

To import the article ids you can use a simple mysql query:

INSERT INTO social_stats (id) SELECT id FROM articles;

PHP social scraper

I will reuse the same code from previous articles for Tweet url counter, Google plus counter and Facebook like counter and add them together.

Class SocialStats{

	function run(){
		$query = "SELECT id, url FROM articles";
		$get = mysql_query($query) or die(mysql_error() . ": " . $query);
		while(list($id, $url) = mysql_fetch_row($get)){
			$fb = $this->facebook_likes($url);
			$g_plus = $this->google_plus_counter($url);
			$tweets = $this->get_tweet_count($url);
			
			$query = " UPDATE social_stats SET ";
			$query .= " fb_shares = '" . mysql_real_escape_string($fb['shares']) . "', ";
			$query .= " fb_likes = '" . mysql_real_escape_string($fb['likes']) . "', ";
			$query .= " g_plus = '" . mysql_real_escape_string($g_plus) . "', ";
			$query .= " tweets = '" . mysql_real_escape_string($tweets) . "' ";
			$query .= " WHERE id = $id ";
			mysql_query($query) or die(mysql_error() . ": " . $query);
		}
	}

	private function facebook_likes($url){
		$query  = " SELECT url, normalized_url, share_count, like_count, ";
		$query .= " comment_count, total_count, comments_fbid, click_count ";
		$query .= " FROM link_stat WHERE url IN('$url')";

		$api_url ="https://api.facebook.com/method/fql.query?format=json&query=".urlencode($query);
		$response = file_get_contents($api_url);
		$response = json_decode($response, true);

		$fb_likes_shares['likes'] = isset($response[0]['like_count']) ? $response[0]['like_count'] : 0;
		$fb_likes_shares['shares'] = isset($response[0]['share_count']) ? $response[0]['share_count'] : 0;
		print_r($fb_likes_shares);
		return $fb_likes_shares;
	}


	private function google_plus_counter($url){
	 
		$ch = curl_init(); 
		curl_setopt($ch, CURLOPT_URL, "https://clients6.google.com/rpc");
		curl_setopt($ch, CURLOPT_POST, 1);
		curl_setopt($ch, CURLOPT_POSTFIELDS, '[{"method":"pos.plusones.get","id":"p", "params":{"nolog":true,"id":"' . $url . '","source":"widget","userId":"@viewer","groupId":"@self"}, "jsonrpc":"2.0","key":"p","apiVersion":"v1"}]');
		curl_setopt($ch, CURLOPT_SSL_VERIFYPEER, false);
		curl_setopt($ch, CURLOPT_RETURNTRANSFER, true);
		curl_setopt($ch, CURLOPT_HTTPHEADER, array('Content-type: application/json'));

		$response = curl_exec ($ch);
		curl_close ($ch);
		$response = json_decode($response, true);
		return isset($response[0]['result']['metadata']['globalCounts']['count']) ? $response[0]['result']['metadata']['globalCounts']['count'] : 0;
	}

	private function get_tweet_count($url){
		    $data = file_get_contents("http://urls.api.twitter.com/1/urls/count.json?url=" . $url);
		    $data = json_decode($data, true);
		    return isset($data['count']) ? $data['count'] : 0;
	}
}

$stats = new SocialStats;
$stats->run();

If you noticed, i used the second example for facebook like counter, because in the first example the count is from both likes and shares.

Next, we should add a cronjob that will execute the script every 6 hours. You can set it to less or more, depending on how frequently the articles are getting shares/likes/tweets.

Setting a cronjob

Fire up the terminal and write the following command:

#env EDITOR=nano crontab -e

This will open the crontab file in nano editor. Add the following line and save the file with CTRL+O. To exit, press CTRL+X.

0 */6 * * * php /var/www/social_stats.php

Video version on how to add a cronjob. Note: This is from another tutorial.

That's it. You can tweak this script for your own needs. Also, you can create a system which will distinct the older posts from the new ones, and update the statisticts more frequently for the new ones, in case you have 1000+ articles on your website.

- Posted by Ana to Php