How to Pull a Twitter Feed Into Your Site Using PHP, MySQL, and the Twitter API

These days, Twitter feeds are everywhere, it seems. There are a million widgets, gadgets, and doohickeys that let you pull your Twitter posts onto your site, but the problem with all of them is that they're tough to customize. Suppose you want to be able to grab from multiple Twitter feeds? Or omit any tweets that contain URLs or certain flagged words? With those widgets, it's tough to do. That's why I figured out how to gather my Tweets on my own. I imagine a few other people out there will want to do the same. Hopefully this tutorial will save them (you) some time. Enjoy. And, leave any related suggestions/code/questions in the comments at the bottom.

1. Create a MySQL table to store the tweets. The Twitter API has limits set on how often you can ping it. So, you'll need to store your tweets locally and update it periodically using a cron job. The first step in doing that is creating a place to store the data. Copy and paste the following MySQL code into phpMyAdmin (or whatever tool you use to connect to your MySQL server):

   CREATE TABLE `twitter` (
   `id` BIGINT UNSIGNED NOT NULL ,
   `screen_name` VARCHAR( 255 ) NOT NULL ,
   `time` BIGINT UNSIGNED NOT NULL ,
   `text` VARCHAR( 255 ) NOT NULL ,
   `hidden` CHAR( 1 ) NOT NULL ,
   PRIMARY KEY ( `id` ) 
   );

2. Create a cron job that grabs the tweets from the Twitter API and stores it in your table. Create a PHP file (named /cron/twitter-update.php in this example) with the following code:

   <?php
   
   require_once 'db-functions.inc.php' ; //custom database functions
   
   function saveTweets($screen_name) {
       global $link;
   
       $screen_name = dbEscape(strtolower(trim($screen_name)));
       if (!$screen_name) { echo "<p><strong>Error: No screen name declared.</strong></p>\n"; return false; }
   
       $row = dbGetRow("SELECT `id` FROM `twitter` WHERE `screen_name`='$screen_name' ORDER BY `id` DESC LIMIT 1");
       $last_id = $row['id'];
   
       $url = "http://api.twitter.com/1/statuses/user_timeline.xml?screen_name=$screen_name" ;
       if ($last_id) { $url .= "&since_id=$last_id" ; }
       $ch = curl_init($url);
       curl_setopt ($ch, CURLOPT_RETURNTRANSFER, TRUE);
       $xml = curl_exec ($ch);
       curl_close ($ch);
   
       $affected = 0;
       $twelement = new SimpleXMLElement($xml);
       foreach ($twelement->status as $status) {
           $text = dbEscape(trim($status->text));
           $time = strtotime($status->created_at);
           $id = $status->id;
           dbQuery("INSERT INTO `twitter` (`id`,`screen_name`,`time`,`text`,`hidden`) VALUES ('$id','$screen_name','$time','$text','n')");
           $affected = $affected + dbAffectedRows();
       }
   
       return "<p>".number_format($affected)." new tweets from $screen_name saved.</p>\n" ;
   }
   
   echo saveTweets('inkplant');
   
   ?>

3. Add the cron job to your crontab file. You need to let your server know how often to run this new file that you created. In the example below, it will run every 5 minutes. Go to your Unix shell and type crontab -e and then add the following line:

   */5 * * * * /usr/bin/php /cron/twitter-update.php

   Note: The settings your server uses to run cron jobs can vary a lot. Check with your server admin for instructions specific to your machine if the example above doesn't work.
4. Pull the databased content to your web page. Place the following code within the body of your PHP page where you'd like to display the Twitter feed. Of course, you can customize here as much as you want...

   <?php
   
   echo "<ul class=\"twitter_feed\">\n" ;
   $result = dbQuery("SELECT * FROM `twitter` WHERE `screen_name`='inkplant' AND `hidden` != 'y' ORDER BY `time` DESC LIMIT 10");
   while ($row = dbGetRow($result)) {
       $text = stripslashes($row['text']);
       $time = $row['time'];
       echo "  <li><span class=\"twitter_time\">".date('M j, Y, g:i a',$time)."</span> &middot; $text</li>\n" ;
   }
   echo "</ul>\n" ;
   
   ?>

This post was published on Sunday, August 22nd, 2010 by Robert James Reese in the following categories: MySQL, PHP, Twitter. Before using any of the code or other content in this post, you must read and agree to our Terms & Conditions.

• Web Development
• Network
• Code
• Tools