How to Show Tweets Archive in Wordpress using PHP and MySQL with Crontab Support?

The Tweeter is not good at displaying all tweets. I have more than 600 tweets so far (most of them are sent by PHP script at VPS). If you want to save a copy of all your historical tweets to your own database locally, you can of course request an archive at your twitter account [Settings – Account – Request Archive].

request-twitter-archive

How about that you can save all the tweets and display them in your blog (WordPress page)? The idea is great because the tweets can be seen as articles for SEO purposes. So, you would need to create a table in the wordpress database, for example let us name it `tweets`.

tweet-history-table

We have four columns, these are: tweet ID, text, date/time, retweet count. Of course, you can get more information from the twitter API statuses/user_timeline but we are just interested in these four.

Storing Locally

There is always a rate-limit for using Twitter APIs so that you cannot use them very frequently. However, the idea here is to have a PHP script that runs at crontab (every few hours) and retrieves the tweets and store them locally to the database.

We can use TwitterOAuth php library to interact with Twitter APIs:

// helloacm.com
// How to Show Tweets Archive in WordPress using PHP and MySQL with Crontab Support
require('TwitterOAuth-1/TwitterOAuth/TwitterOAuth.php');
require('TwitterOAuth-1/TwitterOAuth/Exception/TwitterException.php');
require('TwitterOAuth-1/config.php');  
use TwitterOAuth\TwitterOAuth;
date_default_timezone_set('UTC');
 
$config = array(
    'consumer_key'       => $twitter_apikey, // API key
    'consumer_secret'    => $twitter_apisecret, // API secret
    'oauth_token'        => $twitter_accesstoken, // not needed for app only
    'oauth_token_secret' => $twitter_accesstokensecret,
    'output_format'      => 'object'
);
$tw = new TwitterOAuth($config);

// helloacm.com
// How to Show Tweets Archive in WordPress using PHP and MySQL with Crontab Support
require('TwitterOAuth-1/TwitterOAuth/TwitterOAuth.php');
require('TwitterOAuth-1/TwitterOAuth/Exception/TwitterException.php');
require('TwitterOAuth-1/config.php');  
use TwitterOAuth\TwitterOAuth;
date_default_timezone_set('UTC');

$config = array(
    'consumer_key'       => $twitter_apikey, // API key
    'consumer_secret'    => $twitter_apisecret, // API secret
    'oauth_token'        => $twitter_accesstoken, // not needed for app only
    'oauth_token_secret' => $twitter_accesstokensecret,
    'output_format'      => 'object'
);
$tw = new TwitterOAuth($config);

Save above as twitter.php so that we can include them everytime if we want to access Twitter APIs.

// helloacm.com
// How to Show Tweets Archive in WordPress using PHP and MySQL with Crontab Support
  require("twitter.php"); 
 
  $params = array(
      'screen_name' => $twitter_screenname,
      'user_id' => $twitter_ownerid,
      'count' => 300,
      'include_rts' => 1,
      'exclude_replies' => false
  );
  $tweets = $tw->get('statuses/user_timeline', $params);
 
  mysql_query("SET NAMES utf8");
  if(!empty($tweets)) {
      foreach($tweets as $tweet) {
          $id = $tweet->id;
          $retweet_count = (integer)$tweet->retweet_count;
          $created_at = $tweet->created_at;
          $text = $tweet->text;
          $text = mysql_real_escape_string($text);
          $query = "select count(1) from `tweets` where `id` = '$id'";
          $result = mysql_query($query);
          $cnt = mysql_result($result, 0, 0);
          if ($cnt == 0) {
            $query = "insert into `tweets` set `id`='$id', `text`='$text', `created_at`='$created_at',`retweet_count`='$retweet_count'";            
          } else {
            $query = "update `tweets` set `id`='$id', `text`='$text', `created_at`='$created_at',`retweet_count`='$retweet_count' where `id`='$id'";            
          }
          mysql_query($query);
      }
  }

// helloacm.com
// How to Show Tweets Archive in WordPress using PHP and MySQL with Crontab Support
  require("twitter.php"); 

  $params = array(
      'screen_name' => $twitter_screenname,
      'user_id' => $twitter_ownerid,
      'count' => 300,
      'include_rts' => 1,
      'exclude_replies' => false
  );
  $tweets = $tw->get('statuses/user_timeline', $params);

  mysql_query("SET NAMES utf8");
  if(!empty($tweets)) {
      foreach($tweets as $tweet) {
          $id = $tweet->id;
          $retweet_count = (integer)$tweet->retweet_count;
          $created_at = $tweet->created_at;
          $text = $tweet->text;
          $text = mysql_real_escape_string($text);
          $query = "select count(1) from `tweets` where `id` = '$id'";
          $result = mysql_query($query);
          $cnt = mysql_result($result, 0, 0);
          if ($cnt == 0) {
            $query = "insert into `tweets` set `id`='$id', `text`='$text', `created_at`='$created_at',`retweet_count`='$retweet_count'";            
          } else {
            $query = "update `tweets` set `id`='$id', `text`='$text', `created_at`='$created_at',`retweet_count`='$retweet_count' where `id`='$id'";            
          }
          mysql_query($query);
      }
  }

The mysql_query(“SET NAMES utf8”) make sures the tweets are inserted correctly into the database. The mb_detect_encoding will verify that the tweets returned from APIs are actually UTF-8 encoded. The PHP script above will check for duplicates (based on the Twitter ID) so the first time a new tweet will be inserted but the second time, it will be just updated.

There is a maximum number of tweets you can get by one API call e.g. 300. If you have more tweets, you can always call several time the script to download all the tweets. However, we need to specify the max_id parameter, which will return tweets older than that ID (smaller IDs). So, we can:

// helloacm.com
// How to Show Tweets Archive in WordPress using PHP and MySQL with Crontab Support
  $query = "select min(`id`) from `tweets`";
  $result = mysql_query($query) or die(mysql_error());
  $cnt = mysql_result($result, 0, 0);    
 
  $params = array(
      'screen_name' => $twitter_screenname,
      'user_id' => $twitter_ownerid,
      'count' => 1000,
      'max_id' => $cnt,
      'include_rts' => 1,
      'exclude_replies' => false
  );

// helloacm.com
// How to Show Tweets Archive in WordPress using PHP and MySQL with Crontab Support
  $query = "select min(`id`) from `tweets`";
  $result = mysql_query($query) or die(mysql_error());
  $cnt = mysql_result($result, 0, 0);    

  $params = array(
      'screen_name' => $twitter_screenname,
      'user_id' => $twitter_ownerid,
      'count' => 1000,
      'max_id' => $cnt,
      'include_rts' => 1,
      'exclude_replies' => false
  );

The idea is to check for the oldest tweet (with the min id) and only return the tweets older than that, so if you run the scripts several time, you will import all the tweets.

Now, we have the data ready and we can make this script running at VPS server every few hours (frequency up to you) by using crontab -e command.

Display Tweets at WordPress Pages

Now, this page helloacm.com/tweets-history/ shows all the treets from the database. You would need a plugin to execute PHP/HTML inside a wordpress page. You can easily find such wordpress plugins.

Create a wordpress page and put the following:

[ include ]tweets.php[ / include ]

Then we need to put the PHP script into this tweets.php specified above.

<php
// helloacm.com
// How to Show Tweets Archive in WordPress using PHP and MySQL with Crontab Support
  global $wpdb;
  
  $query = "select count(1) from `tweets`";
  $total = $wpdb->get_var($query);
  
  $per = 50;
  $page = get_query_var('page');
  if (!$page) {
    $page = 1;
  }
  
  // pagination
  $totalpages = ceil($total / $per);
  if ($page > $totalpages) {
    $page = 1;
  }
  
  $lowerbound = ($page - 1) * $per;
  $upperbound = $lowerbound + $per;
  $lmt=" limit " . $lowerbound . "," . $per;
  
  $query = "select * from `tweets` order by `id` desc $lmt";
  
  $result = $wpdb->get_results($query);
  
  if ($result) {
    foreach ($result as $tweet) {
      $created_at = $tweet->created_at;
      $text = $tweet->text;
      $retweet_count = $tweet->retweet_count;
      // display tweets
      echo "$text - $created_at";
      if ($retweet_count > 0) {
        echo "($retweet_count Retweeted)";
      }
    }
  }  
  
  // show prev/next page links
  if ($page > 1) {
    echo "<a href='?page=".($page-1)."'>Newer Tweets</a> - ";
  }
  if ($page < $totalpages) {
    echo "<a href='?page=".($page+1)."'>Older Tweets</a>";
  }

<php
// helloacm.com
// How to Show Tweets Archive in WordPress using PHP and MySQL with Crontab Support
  global $wpdb;
  
  $query = "select count(1) from `tweets`";
  $total = $wpdb->get_var($query);
  
  $per = 50;
  $page = get_query_var('page');
  if (!$page) {
    $page = 1;
  }
  
  // pagination
  $totalpages = ceil($total / $per);
  if ($page > $totalpages) {
    $page = 1;
  }
  
  $lowerbound = ($page - 1) * $per;
  $upperbound = $lowerbound + $per;
  $lmt=" limit " . $lowerbound . "," . $per;
  
  $query = "select * from `tweets` order by `id` desc $lmt";
  
  $result = $wpdb->get_results($query);
  
  if ($result) {
    foreach ($result as $tweet) {
      $created_at = $tweet->created_at;
      $text = $tweet->text;
      $retweet_count = $tweet->retweet_count;
      // display tweets
      echo "$text - $created_at";
      if ($retweet_count > 0) {
        echo "($retweet_count Retweeted)";
      }
    }
  }  
  
  // show prev/next page links
  if ($page > 1) {
    echo "<a href='?page=".($page-1)."'>Newer Tweets</a> - ";
  }
  if ($page < $totalpages) {
    echo "<a href='?page=".($page+1)."'>Older Tweets</a>";
  }

We use get_query_var(‘page’) to get the query string so we know at what page we are viewing.

–EOF (The Ultimate Computing & Technology Blog) —

GD Star Rating
loading...

1166 words
Last Post: How to Create and Run Unit Tests in C# .NET - A Quick Tutorial
Next Post: Simple Matrix Mathematics Library for PHP (Matrix Det)

The Permanent URL is: How to Show Tweets Archive in WordPress using PHP and MySQL with Crontab Support?

Algorithms, Blockchain and Cloud

How to Show Tweets Archive in WordPress using PHP and MySQL with Crontab Support?

Storing Locally

Display Tweets at WordPress Pages

Leave a Reply

Storing Locally

Display Tweets at WordPress Pages

Related posts:

Leave a Reply