Recently, I published a few posts on getting Facebook like count, Twitter followers count, FeedBurner Subscriber count, and Alexa Rank and Sites Linking in using simple PHP codes. Doing so has several advantages: replacing widgets (thereby improving page speeds) and displaying counts a simple formatted texts. It also provides the opportunity to combine subscriber / follower count to display a single count. I wanted to take this one step further and start recording these values in a MySQL database on a daily basis, thus building a history. There are a couple of major advantages (at least from my point of view) to doing this:
- I check my Facebook like count, Twitter followers count, FeedBurner subscribers count, Alexa rank, and Alexa sites linking in at least 2-3 times a week. Recording these values in a database allows me to visit a single page to check all these values (as shown in the picture below).
- One other major advantage is the ability to see how the visibility of your website is trending. For example, if you implement a giveaway for your readers if “Like” your Facebook fanpage, you can see what effect it had on your Facebook like count.
- Basic PHP and MySQL knowledge would greatly help (but not necessary)
- Review: Get Facebook like count using PHP
- Review: Get Twitter followers count using PHP
- Review: Get FeedBurner subscribers count using PHP
- Review: PHP Code to Get Alexa Rank and Site Linking
Creating a MySQL Table
First, you will have to create a table called
wp_social_stats in your MySQL database. This is easy if you already know a little bit of MySQL and you have access to your database through
phpMyAdmin or by other means. Remember that
wp_social_stats should be table within your main database. Structure of the table is shown in the picture below:
phpMyAdmin you can go to
SQL tab of your database and type in the following query to create the table in one go. Change
ENTER_YOUR_DATABASE_NAME to the name of your database.
PHP Code to Record Facebook, Twitter, FeedBurner, and Alexa Information
After you have created the
wp_social_stats table within your MySQL database, it is now time to get the PHP code ready to record the Facebook, Twitter, FeedBurner, and Alexa rank values. Create a file named
socialstats_record.php in the root folder of your site and copy the following contents to the file:
Download the full code using the download button below:
The following values have to be edited:
//Database details $db_host='ENTER_YOUR_DATABASE_HOST'; $db_database='ENTER_YOUR_DATABASE_NAME'; $db_username='ENTER_YOUR_DATABASE_USERNAME'; $db_password='ENTER_YOUR_DATABASE_PASSWORD'; //Get FeedBurner Subscriber Count $fburl="https://feedburner.google.com/api/awareness/1.0/GetFeedData?uri=http://feeds.feedburner.com/ENTER_YOUR_FEEDBURNER_FEED_NAME"; //Get Twitter Followers Count $twurl = "http://twitter.com/users/show/ENTER_YOUR_TWITTER_ID"; //Get Facebook Like Count $fpageID = 'ENTER_YOUR_FACEBOOK_PAGE_ID'; //Get Alexa Rank and Sites Linking in $source = file_get_contents('http://data.alexa.com/data?cli=10&dat=snbamz&url=ENTER_YOUR_DOMAIN_NAME');
To visit the page go to
http://yourdomain.com/socialstats_record.php (if you saved the file in your site’s root folder). From now on, every time the page is opened your social stats are recorded into the database.