Archiving Tweets: A Simple Way to Use PHP, MySQL, and the Twitter API to Save Stuff

NOTE: As of July 1st, TwapperKeeper now does nearly the same thing I am going to describe in this post. That’s ok—now you’ll understand the process and if you want to do it yourself instead of through a third-party app, you can. For mission-critical data (like dissertation research) you might want to do it yourself, not because there’s anything wrong with the TwapperKeeper folks (I don’t know them personally) but because, well, it’s your data that you need to be sure you can access.

Background
After the Twitter backchannel conversations during the Computers & Writing 2009 conference, Karl Stolley wrote a post asking everyone to save their tweets tagged with #cw09. His post provided instructions for the non-scripting way for archiving tweets: basically, to manually invoke and save the search results page, incrementing the page number until you hit the limit or no more results from your search. The outcome of this process would be a set of text files—still data, still stored, but not really in a format that can be quickly used. But some archiving is better than no archiving, of course.

As the Digital Humanities 2009 conference started and the conversations started flying fast and furious, I thought to myself (as did many others) “whoa! we have to save this stuff” knowing that Twitter only allows access to the last 1500 search results, and only for a 7 to 10 day period of time. That is to say, if the number of #dh09 tagged tweets hit 1500 or several days passed, we wouldn’t be able to get those tweets back from the depths of the Twitter databases. Someone set up a TwapperKeeper archive for #dh09, but at that time there wasn’t a way to export from TwapperKeeper. Following on the heels of #dh09 was THATCamp, and the Twitter backchannel nearly doubled that of #dh09. In other words, there was good stuff that simply had to be saved.

Mikhail Gershovich (@mikhailg) tweeted about TweetRipper, a quick little script that invokes PHP from the command line and yanks the date, user, and text for specifically-tagged tweets out to a text file. I grabbed the script, set up a database table, modified the script a bit (I kept their outside loop but just did different things in the middle), and started archiving tweets for both #cw09 and #dh09 (retroactively) and #thatcamp (going forward).

Below I discuss this script and how you can use it yourself to archive your own tweets. Or, you could just set up and use a TwapperKeeper now that they have export capabilities, but remember that if you control your data, then, well, you control your data.

These instructions require a server that has access to PHP and MySQL. Note that if you understand what’s going on conceptually, you can easily do this with any server-side scripting language and database.

Setting Up the Database Table
You can name your table and your fields anything you want, and you can save as much of the Twitter data from the API search method as you want. I saved everything except the profile_image_url. Call it an editorial decision.

“Everything” is the following: the tweet text, the Twitter ID and username of the user to whom the tweet was address (if an @reply), the Twitter ID and username of the person who sent the tweet, the Twitter ID of the tweet itself, the language encoding of the tweet, the source (Twitter client) used to send the tweet, and the time the tweet was created.

I created the following database table (note that the data type lengths indicated are not fine-tuned at all; in the interest of time I erred on the side of “leave a lot of room”):

CREATE TABLE tw_hashtag_search (
     id bigint(20) NOT NULL auto_increment,
     tw_hashtag varchar(32) default NULL,
     tw_id bigint(20) default NULL,
     tw_lang char(2) default NULL,
     tw_source varchar(255) default NULL,
     tw_text varchar(160) default NULL,
     tw_created_at varchar(64) default NULL,
     tw_to_user_id bigint(20) default NULL,
     tw_to_user varchar(32) default NULL,
     tw_from_user_id bigint(20) default NULL,
     tw_from_user varchar(32) default NULL,
     record_add_date datetime default NULL,
     PRIMARY KEY (id),
     UNIQUE KEY tw_id (tw_id)
);

The id field and the record_add_date field are for the ID of the record and the date the record was created in this table. In other words, independent of anything from Twitter. Also technically independent of anything from Twitter is the tw_hashtag field; that’s the field in which I store the hashtag used in the search—this table holds tweets tagged with #cw09, #dh09, and #thatcamp, and can hold tweets for any other hashtag search you want to run.

The other tw_* fields match up with data made available through the Twitter API (see above for list of “everything”). The tw_created_at field stores the date the tweet was created, and Twitter stores this as a string such as “Fri, 26 Jun 2009 12:56:06 +0000″. I decided to keep the raw data in this format. Note that you could use the PHP strtotime() function to change that data to a UNIX timestamp before you store it, or you could convert to a UNIX timestamp when you manipulate the data in however you eventually plan to do so. Personally, I like to archive data in its native format and then manipulate it when needed, but your mileage may vary.

Obtaining/Modifying the Script
This is a PHP script; like I said before, once you understand the logic involved you can easily replicate this in a scripting language of your choice if PHP isn’t your thing.

» Download the archive_tweets.zip archive

This zip archive contains the PHP script and a very basic README file. The PHP script is commented pretty clearly as to what is going on in it, but I’ll explain some things below.

Most importantly, the only change you have to make is to replace the database connection information with your own (hostname, username, password, database name). However, if you change use a different database table structure to store the tweets obviously you’ll need to modify the SQL query for record insertion so that it matches the table you’ve created.

Understanding Key Parts of the Script
The code itself is pretty clearly commented—there’s not a lot of magic to it—but below I will note some of the more interesting things involved.

This script is invoked via http; as I thought about the ways in which people would use it, and the access they might have to the server, this seemed the easiest thing to do. Note though that a simple change would allow it to be run on a command line (leave a comment if you want to know how). Since it’s set up to use http, we pass two arguments via a query string; these lines in the script give those $_GET variables some better names ($hashTag and $limit).

//sets up variable names from query string for internal use
$hashTag = $_GET['ht'];
$limit = $_GET['n'];

After these two lines there are two if clauses that check for the validity of the values. If there is no value for $hashTag then the script prints a message saying so and exits. If there is a value for $hashTag it continues, then checking for the number of tweets to return from the search. If there is no value for $limit or if the value given is not numeric, the script assumes 1500 (the Twitter limit), prints a message saying so, and continuess.

//checks for required values
if (empty($hashTag)) {
     echo "No hashtag used.<br/>";
     exit;
}
if ((empty($limit)) || (!is_numeric($limit))) {
     echo "No numeric limit provided; assuming 1500.<br/>";
     $limit = 1500;
}

The next six lines simply set up some variables to control the looping. Since this script uses the Twitter API to yank search results, and there are 100 results provided on each search “page,” this set of variables defines things like results limit, results per page, which page number to start with, and so on. Basically, what will happen is that the loop you will soon see will iterate for as many times as necessary to reach the number of tweets you will want to grab

After those variables comes the database connection information. In the second line below, replace HOSTNAME, USERNAME, PASSWORD, and DATABASE with your own connection information. Also note that this line (and the rest of the MySQL-related lines in the script) uses the mysqli_* family of functions. If you have to use the mysql_* functions just adjust accordingly (leave a comment if you are unsure what that means).

//connect to database; if you are using mysql_* family of functions, adjust accordingly throughout
$mysqli = @mysqli_connect("HOSTNAME", "USERNAME", "PASSWORD", "DATABASE");

After the database connection comes a SQL query to retrieve the maximum ID (from Twitter) of the tweets stored in the table for that hashtag. This will speed things up when we get to the loop-and-save process.

The next part of the script sets up the loop. As you can see by the comments in the script, the script will:

//Continue looping IF:
// - we have fewer results than requested
// - have not reached the end
// - have executed fewer than so many queries

Within the loop, the script gets the results of a query, and if there are results decodes the JSON and does something with the data. Namely, the script formats the data (where necessary) for inclusion in the database. In the example below you can see that we assign a variable name ($tw_text) to the element extracted from the JSON ($item->text) and we also use the addslashes() function to be sure that the data can be inserted cleanly into the database table.

//these 9 lines get 9 elements out of the JSON result and
//format them for inclusion in the database (where necessary)
$tw_id = $item->id;
$tw_lang = $item->iso_language_code;
$tw_source = addslashes($item->source);
$tw_text = addslashes($item->text);
$tw_created_at = $item->created_at;
$tw_to_user_id = $item->to_user_id;
$tw_to_user = $item->to_user;
$tw_from_user_id = $item->from_user_id;
$tw_from_user = $item->from_user;

But before issuing the SQL query to insert the record into the table, there’s a little if…else construct that checks the tweet ID against the maximum tweet ID stored in the table, for that hashtag. The idea here is that we don’t issue a query if we don’t have to (although since tw_id is set up as a unique field in the table, the query would simply fail and move on to the next one, but better to avoid that in the first place).

At both stages of the if…else test, the script prints a message that tells you what’s going on—either that a tweet is being added or that it is not.

if ($tw_id > $max_tw_id) {
     //if the tweet id is greater than the max id of the stored tweets for that hashtag...
     //print message that we are adding the tweet
     echo "adding tweet /".$tw_from_user."/status/".$tw_id."<br/>";
     //add tweet to database table
     @mysqli_query($mysqli, "insert into tw_hashtag_search values
       ('', '".$hashTag."', '".$tw_id."', '".$tw_lang."', '".$tw_source."',
       '".$tw_text."', '".$tw_created_at."', '".$tw_to_user_id."', '".$tw_to_user."',
       '".$tw_from_user_id."', '".$tw_from_user."', now())");
} else {
     //if the tweet id is less than the max id of the stored tweets...
     //print message that it has already been stored
     echo "tweet already stored: /".$tw_from_user."/status/".$tw_id."<br/>";
}

The script finishes up with closing the loop and, finally, printing a “done” message to the screen.

Usage
As I mentioned, this script is meant to be run via the web browser. The idea here was that people would not have access to the command line (as typical in virtual hosting/shared hosting environments) but they could put a script on their web server and bookmark it to run on-demand.

The URL you would use is as follows:

http://domain/path_to/archive_tweets.php?n=MAXNUMBER&ht=HASHTAG

Where domain and path_to should obviously be replaced with your own information. MAXNUMBER is some number of tweets to retrieve, less than 1500 (note that if you put a larger number in, the script will iterate but you won’t get anything from Twitter. HASHTAG is the search string (minus the hash, so cw09 instead of #cw09, for example).

*** Note! You could also add a cron job that invokes the script via cURL, or change the script slightly and add a cron job that invokes the script via the commandline. Leave a comment if you need to know how to do that. ***

The bookmarked-URL method is what I used, not only because it was just easier for me to say “oh hey, time to archive” and hit the script, but because I actually read the messages printed to the browser to see what was being added or how many tweets had already been added, and so on. That worked, because we’re talking about 2700 tweets over a two-day period (in the case of #thatcamp tweets), and not 5000 tweets per hour for the wildly popular trending topics (like #iranelection).

If you want to archive tweets for a trending topic, that would be the time to use a cron job to run the script automatically every 10-15 minutes or so (or more) unless you want to be beholden to your bookmarks all day long. But for the average person wanting to grab a snapshot of a search at a given moment or before the tweets expire, this method works just fine.

For more information on options, modifications, different formats (this script retrieves JSON results, could also grab atom results), or the Twitter API, leave a comment. I tried to make this as general and broadly useful as possible.

Close Bookmark and Share This Page

Save to Browser Favorites / Bookmarks

Ask backflip blinklist BlogBookmark Bloglines BlogMarks Blogsvine BuddyMarks BUMPzee! CiteULike co.mments Connotea del.icio.us

Digg diigo DotNetKicks DropJack dzone Facebook Fark Faves Feed Me Links Friendsite folkd.com Furl Google

Hugg Jamespot Jeqq Kaboodle kirtsy linkaGoGo LinkedIn LinksMarker Ma.gnolia Mister Wong Mixx MySpace MyWeb

Netvouz Newsvine oneview OnlyWire PlugIM Propeller Reddit Rojo Segnalo Shoutwire Simpy Slashdot Sphere

Sphinn Spurl Squidoo StumbleUpon Technorati ThisNext Twitter Webride Windows Live Worlds Movies Yahoo!

Email This to a Friend

Copy HTML: 

 If you like this then please subscribe to the RSS Feed.

Powered by Bookmarkify™

More »

July 7, 2009  Tags: coding, tech geeky  Posted in: Projects, Techie

3 Other Comments

Additional comments powered by BackType

• « Previous post
• Next post »