Code

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

Note: The Twitter REST API v1 – which this script calls – is no longer active. I haven't had a chance to upgrade this article to API v1.1 yet.

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" ;
    
    ?>


75 Comments

Hemen
February 17, 2011, 5:12 pm
How would you do this is you had multiple twitter feeds you wanted to capture?
Robert James Reese
February 18, 2011, 8:19 am
You would just add an extra call to the saveTweets function in your cron. For example:

echo saveTweets('twitterfeed1');
echo saveTweets('twitterfeed2');
thatguy
February 24, 2011, 12:37 pm
I am wondering if you know how to make it so it is an RSS feed?
Robert James Reese
February 24, 2011, 1:56 pm
Steps 1 through 3 would be the same. Then, in step 4, instead of printing the unordered list, you would print out the RSS feed. That's pretty straight-forward and there are a lot of good examples out there. Here's one I just found: http://www.webreference.com/authoring/languages/xml/rss/custom_feeds/
Josh
March 6, 2011, 8:37 am
How often will twitter allow you to run this script?
Robert James Reese
March 7, 2011, 8:56 am
Right now, the Twitter limits you to 150 calls per hour. More information on that can be found in their documentation.
May 30, 2011, 5:41 am
Would it be possible to use this script to pull tweets which have been tweeted to a #hashtag instead of tweets in my timeline?
Robert James Reese
May 30, 2011, 10:51 am
Yep, it's pretty easy to make the switch. Just change the $url that you are feeding to the cURL function to http://search.twitter.com/search.atom?q=%23yankees (as an example looking for #yankees). The search term needs to be URL encoded. If you're curious, here's more info on Twitter's search method.
May 31, 2011, 7:58 am
Thanks for the info Robert it's much appreciated. Sorry to be a pain but what I was hoping to be able to do is to pull tweets by anyone from a hashtag - This script just seems to pull my own?
Robert James Reese
May 31, 2011, 10:17 am
Michael, I apologize, I just glanced over that and didn't test it out. You're right, that function was still set only to grab from one screen name. Also, the response from Search was very different from the response from User Timeline. I've posted a followup article about getting all posts of a specific hashtag from Twitter with PHP here. Hope it helps.
Sam
June 18, 2011, 2:52 pm
Great Article! The only thing I was wondering about is how do I make it so only the latest 20 tweets are saved in the mysql db? I've got a lot of tweets and I don't want to save them all there…I just need the latest ones…how would you do that?
Robert James Reese
June 18, 2011, 3:22 pm
The tweets aren't going to take up that much space in your database, even if you leave them all there. Personally, I wouldn't worry about keeping the old ones around. Or, maybe just go in manually via phpMyAdmin every once in a while and clean out the old ones.

But, if you decide to make it automatic, I'd recommend just adding another MySQL query at the end of the saveTweets() function that deletes the previous tweets. That way, you make sure that you get the new ones before deleting the old.
Sam
June 18, 2011, 3:35 pm
Cool…I think I would still like to delete the old ones if possible. How would I go about deleting the old ones automatically then? Should I use id or time to filter through which ones to delete?
Robert James Reese
June 18, 2011, 3:41 pm
I'd use time as there is no guarantee that Twitter will keep their ID's numeric in the future. Something like this:

$save = array();
$result = dbQuery("SELECT `id` FROM `twitter` WHERE `hidden` != 'y' ORDER BY `time` DESC LIMIT 20");
while ($row = dbGetRow($result)) {
$save[] = $row['id'];
}
dbQuery("DELETE FROM `twitter` WHERE `id` NOT IN ('".implode('','',$save)."')");

Double check that before you use it. I just wrote it and haven't tested it.
Sam
June 18, 2011, 11:40 pm
For some reason the implode function was giving me some errors so I just put it on a separate line:

$save = array();
$result = dbQuery("SELECT `id` FROM `twitter` WHERE `screen_name`='$screenname' ORDER BY `time` DESC LIMIT 20");
while ($row = dbGetRow($result))
{
$save[] = $row['id'];
}
$savelist = implode(",", $save);
dbQuery("DELETE FROM `twitter` WHERE `screen_name`='$screenname' AND `id` NOT IN ('$savelist')");

*this one only saves the one latest tweet so it's good for periodic cleaning out of the db
Thanks for all your help!
Robert James Reese
June 19, 2011, 11:11 am
Cool. Glad you got it to work!
June 26, 2011, 12:57 pm
Hey,

I am getting this error.
Call to undefined function dbconnect()

I have tried a few different things but nothing seems to be working.

Thanks
Robert James Reese
June 26, 2011, 1:14 pm
The code is using a custom set of database functions that I wrote. You can view them here: http://www.inkplant.com/code/php-mysql-database-functions.php
Jason
June 27, 2011, 1:46 am
I am using that function file but it still is not working. It is in the cron sub folder along with this code. I may have put my information in incorrectly. I put the pass and such in both files.

Thanks in advance.
Robert James Reese
June 27, 2011, 8:49 am
If you're getting a undefined function error, that means that the include is not being loaded. Try changing your code to this (adding the ./ specifies that db-functions.inc.php is in the same folder as your code):

require_once './db-functions.inc.php';

And make sure that line is at the top of the code.
Jason
June 28, 2011, 1:26 am
I get the same error. The file code in php-mysql-database-functions.php has no reference to dbconnect. It is being included correctly becuse when i add a reference to dbconnect I stop receiving the error.

Thanks again in advance.
Robert
June 28, 2011, 9:49 am
Jason, my apologies. I forgot that I had uploaded an updated version of the database code that automatically connects now so the connect function is no longer necessary. I've removed the call to dbConnect from this post. Hopefully that will clear up the issue for you.
jason
June 29, 2011, 1:41 am
Thanks Robert,

Everything work great now.
Thanks for all your help.
July 14, 2011, 7:13 am
Hi, i am being really stupid (plus my php and mysql isnt fantastic) but what do you change input your screen name into the code?
Thanks very much!
Robert
July 14, 2011, 8:58 am
You just change it in the call to the function. For example:

echo saveTweets('teamcoco');
Andrew
August 12, 2011, 3:08 pm
Why can't you just pull the data from the API and display it on your website without storing it in a database?
Alison
August 13, 2011, 11:17 am
Hi, new to this. Great article. I have got the tweets onto a webpage. How can I get it to scroll round like the twitter widget when loop is selected? is that a javascript function?
September 22, 2011, 12:30 pm
Awesome,

Only change I made was to run the output through a few regular expressions to turn hastags, urls and @usernames into hyperlinks:

$text = preg_replace('/(https{0,1}://[w-./#?&=]*)/', '$1', $text);
$text = preg_replace('/@(w+)/', '@$1', $text);
$text = preg_replace('/s#(w+)/', ' #$1', $text);
Matt
October 15, 2011, 7:02 am
This is a nice script, but I wonder why only the last 20 tweets are stored and not all.

Has this something to do with the way of authing?
Robert James Reese
October 15, 2011, 10:34 am
The Twitter API doesn't return a user's whole timeline of tweets at once. You could grab it with multiple calls, if you wanted, though. Check out their documentation.
October 18, 2011, 7:01 am
Rather simple question here but what if I wanted to grab just the newest post. I wouldn't really need a database for that (normally I just store small bits in a txt file) but, to be honest, I'm not sure which parts of the code are accessing Twitter for the tweets and which are writing to the database.
Bob
October 26, 2011, 1:47 am
This probably seems really dumb but I can't name my php file with slashes.
Dwayne
November 10, 2011, 10:17 pm
How can I use this to gather multiple feed searchs and store them?

Right now I am using wget and running a cron job. But it is for one twitter feed search.

I am using this :

wget -O /home/myusername/public_html/yahooapi/wget-twitter.xml -q -t 1 http://search.twitter.com/search.atom?q=LSU

But I don't want to do a seperate cronjob for each search it would kill my shared hosting.

So it would be great if I can do many searches for 1 cron job.

Do u think this is possible?

Thanks
Martin
December 17, 2011, 11:27 pm
Where do you set the screen name in the twitter-update.php file? Is there a way to set this as a variable or from the url ie twitter-update.php?screen_name=@username

Thanks!
Robert James Reese
December 18, 2011, 9:48 am
Sure, at the bottom where you're calling the function, change it to:

$screen_name = trim(strip_tags($_GET['screen_name']));
echo saveTweets($screen_name);
January 16, 2012, 7:39 am
This works well, but it seems to create some bizzare characters in the database when there are some 'non standard' characters in the tweet, such as curly quotes or em dashes.

Any idea? Do they need to be escaped or anything?
January 17, 2012, 7:38 am
^ Just worked this out. So if anyone else is having the same problem, you need to ensure your database table is using UTF-8.
Mitchell
January 17, 2012, 3:04 pm
Hi Robert,

Great tutorial! I'm having a problem getting this running though.

Here are the errors that I'm getting:
Warning: SimpleXMLElement::__construct() [simplexmlelement.–construct]: Entity: line 85: parser error : Entity 'copy' not defined in /home/riktig/public_html/lanternclub.com/twitter_update.php on line 22

Warning: SimpleXMLElement::__construct() [simplexmlelement.–construct]: © 2011 Twitter in /home/riktig/public_html/lanternclub.com/twitter_update.php on line 22

Warning: SimpleXMLElement::__construct() [simplexmlelement.–construct]: ^ in /home/riktig/public_html/lanternclub.com/twitter_update.php on line 22

It seems to be having an issue with the SimpleXMLElement but I can't seem to figure this issue out.
James
January 25, 2012, 9:16 pm
Hi Rob,

Thanks for this - it was very useful.

Upon reading this I was wondering, is there a way of pulling all publicly available Tweets with a certain hashtag without using the Twitter API?
Freddy
March 1, 2012, 2:53 pm
Maybe this is a dumb question but, where can I find the file db-functions.inc.php?
Robert James Reese
March 1, 2012, 10:30 pm
I linked it in the code, but it's easy to miss:

http://www.inkplant.com/code/php-mysql-database-functions.php
John
March 11, 2012, 4:20 am
Hi, I really like this script, it's pretty great.

I was wondering how did Ryan Kelley get the hastags and mentions and links to turn into hyperlinks on the output?

I would like to do that too, I'm fairly new to PHP yet and I'd like to know where to put that code to do that.

$text = preg_replace('/(https{0,1}://[w-./#?&=]*)/', '$1', $text);
$text = preg_replace('/@(w+)/', '@$1', $text);
$text = preg_replace('/s#(w+)/', ' #$1', $text);


Thanks, hope you can help.
John
March 11, 2012, 3:23 pm
Nevermind guys, figured it out pretty quickly, gotta love the ease of PHP.

If anyone's wondering run the output $text through expressions after

$text = stripslashes($row['text']);

and you should be good.
John
March 11, 2012, 5:32 pm
Hmm, I'm curious, has anyone implemented pagination with this? What would be the simplest way to approach this? I'd like to have my feed paginated through PHP or Ajax.
John
March 12, 2012, 3:23 am
Nevermind again,

I ended up not using the pulldata to page content part of the script and used a much simpler one.

Thanks again for the twitter grabbing script.

You rock with the code.
Robert James Reese
March 12, 2012, 8:45 am
John, glad you were able to figure it out and thank you for your comments. Sorry I didn't have a chance to jump in and help – it's been busy around here.
Martin
April 5, 2012, 7:05 pm
Excellent, I've managed to suck in the tweets and spit them out on the site.

I'm having a right difficulty with the preg_replace to link the hashtags and links:(

My code looks like this:

$text = stripslashes($row['text']);
$time = $row['time'];
$text = preg_replace('/(https{0,1}://[w-./#?&=]*)/', '$1', $text);
$text = preg_replace('/@(w+)/', '@$1', $text);
$text = preg_replace('/s#(w+)/', ' #$1', $text);
echo " ".date('M j, Y, g:i a',$time)." $textn" ;

but i get the error:

Warning: preg_replace() [function.preg-replace]: Unknown modifier '/'...

I've tried escaping some of the slashes but I've got no idea what im doing with regex! Any help gratefully received!
Robert James Reese
April 5, 2012, 7:16 pm
This isn't perfect, but it will get you started at least:

$text = 'This is an example tweet by @cowboyhazel about #regex. For more, visit http://www.inkplant.com/code/';
$time = time();
$text = preg_replace('/(https{0,1}:\/\/[A-Za-z0-9.\/#?&=+-]*)/', '<a href="$1">$1</a>', $text);
$text = preg_replace('/@([A-Za-z0-9-]+)/', '<a href="https://twitter.com/#!/$1">@$1</a>', $text);
$text = preg_replace('/#([A-Za-z0-9-]+)/', '<a href="https://twitter.com/#!/search/%23$1">#$1</a>', $text);
echo date('M j, Y, g:i a',$time).' - '.$text ;
Martin
April 5, 2012, 7:26 pm
It's still giving me

Warning: preg_replace() [function.preg-replace]: Unknown modifier '/' in…

Do you take on freelance work?
Robert James Reese
April 5, 2012, 7:31 pm
Sorry, my slashes were getting stripped out. I edited the previous comment, try again now.

And, no, sorry, but I'm not currently taking on any new work. I'm swamped as it is.
Martin
April 5, 2012, 7:36 pm
What a guy! that worked a treat.

Sending you good vibes! sorry for posting twice, think it's something to do with the refreshing.
Robert James Reese
April 5, 2012, 7:47 pm
Glad it worked. And, yeah, I need to fix that duplicate post on refresh bug…
Augusto Delgado
April 11, 2012, 1:13 pm
Hi great code.
I have a minor issue when i run the twitter-update.php i get the following erors:
adelgado@mailer:~/public_html/twt# php twitter-update.php
PHP Notice: Undefined index: db_errors in /home/adelgado/public_html/twt/db-functions.inc.php on line 14
PHP Fatal error: Call to undefined function mysql_connect() in /home/adelgado/public_html/twt/db-functions.inc.php on line 24

I do have the db-functions.inc.php included int the twitter-update.php: e.g:

require_once './db-functions.inc.php' ; //custom database functions

any Ideas?
Robert James Reese
April 11, 2012, 10:32 pm
Sounds like you don't have MySQL at all. Try running a script with and see if you see MySQL listed in there. Maybe you're running a different database?
Augusto Delgado
April 12, 2012, 3:53 pm
Nevermind i forgot to restart a apache after loading the required components here is what it needed aside from apache, mysql and php: For ubuntu/debian:
apt-get install php5-curl
apt-get install php5-mysql
Martin
April 13, 2012, 11:36 am
Does anyone know what the code would look like to not post the tweets that start with "@"?

I'm thinking it would be good to post tweets but not include reply conversations.
Robert James Reese
April 13, 2012, 4:35 pm
One easy way would be to put this before your INSERT query:

if (substr($text,0,1) != '@') {
Martin
April 18, 2012, 7:22 pm
Doesn't that kill it at the mysql level?

I still want them to go in, just not pull out…
Robert James Reese
April 19, 2012, 8:36 am
Yeah, it would. But, you could use the same logic to have it save them with the 'hidden' column set to 'y' which would prevent them from showing up.
Phil
April 22, 2012, 2:45 pm
Great post, makes updating site and keeping track of feeds simple.

Not sure about "multiple calls" to get more than the last 20 tweets. The documentation says you should be able to add &count= to the end of the API call, so you get something like this:

user_timeline.xml?screen_name=$screen_name &count=200" ;

But that doesn't seem to be working.
Phil
April 23, 2012, 3:53 am
Figured it out, I'd run the script before adding the count, so it wasn't updating as it had already got the latest tweets.
Good karma to you
Scott
April 26, 2012, 1:54 pm
Hi Robert!
Great guide,
i was wondering about how i can alter the keyword that there is searched for?
I cannot find where to alter the screen-name.

Could you please reply to my email?
Thank you! :)
Scott
April 26, 2012, 2:17 pm
Alter the screen-name

Hi Robert, i found that you use the last "echo" line to alter the screen-name. Do you know how i would need to alter the code so that i could use an $screen_name to alter what value you search for? Thank you!
Phil
May 10, 2012, 5:37 am
@Scott: Did you try the first answer on here, you need to change 'inkplant' to be the screen_name you want. And for multiple screen_names:

You would just add an extra call to the saveTweets function in your cron. For example:

echo saveTweets('twitterfeed1');
echo saveTweets('twitterfeed2');
Phil
May 10, 2012, 6:48 am
I've added a retweet_count to the code:
$retweet_count = $status->retweet_count;
Which works fine to store the value the first time the query is run, but it needs to update when the query is run again. ON DUPLICATE KEY UPDATE doesn't seem to work (I think because of "&since_id=$last_id" meaning it doesn't check the older id).
Would an if…else work, or some other action before the if statement?
May 16, 2012, 2:44 pm
Robert,

Great script and it's amazing to see you posted this in 2010 and you still get so many questions!

I am utilizing your script and it's working great. I also augmented so that all my hashtags, usernames and links are hyperlinked.

Ultimately though what I am looking to do is to query numerous users…so that I populate my db based on any number of specific users I want to pull from Twitter.

I scanned through your comments above and I saw where you said to replace the hashtag function call with:
$screen_name = trim(strip_tags($_GET['screen_name']));
echo saveTweets($screen_name);

But is it possible to pull many screen names without seriously augmenting the script?

Thanks for your time.
Robert James Reese
May 16, 2012, 7:50 pm
Sure, just call the function multiple times:

echo saveTweets('user1');
echo saveTweets('user2');
May 17, 2012, 11:28 am
Robert,

Thanks, I figured out that I needed to build a loop, works perfect now.

One last question…at the end of the tweets I see a - then the persons username. Is that what Twitter is sending or is that your script? It does not display on twitter like that.

Thanks so much for this script, Really is great!
vachik ghadimian
May 31, 2012, 8:29 pm
Great article.
This is a different approach to the same thing. I am using prepared statements with PDO (recommended nowadays) and also creating an XML at the end of my script.It will output what has been retrieved. This is a work around if twitter is not always responding to your request.

http://pastebin.com/ENsYwz8L

let me know what you guys think!
Damien Doyle
July 21, 2012, 5:49 am
Thanks so much for this!
First time I have seen this thing explained clearly and furthermore, the first time i have been able to tweak the code myself to get results!
December 18, 2012, 1:34 pm
Is there a way to pull the twitter profile image and store it? I tried adding a string to the twitter-update.php but it doesnt store it..Any info would be nice..This is what mine looked like

dbQuery("INSERT INTO `twitter` (`id`,`screen_name`,`time`,`text`,`hidden`,`profile_image_url`) VALUES ('$id','$screen_name','$time','$text','n','$profile_image_url')");
January 17, 2013, 3:15 pm
Great man!

I use it to analyze on of the most popular radio stations in Austria as I have the impression that the play the same songs up to 10 times a day …
hassane mroue
February 15, 2013, 10:00 pm
Dear Gentlemen,
I need to pull #tag feeds from twitter with pics of each user who twitted with a specific hashtag. Also be able to edit modify and or delete. These data will be later posted
Phil
June 14, 2013, 9:23 am
Would it be possible to change the PHP now that Twitter has changed their API?
Kim
June 27, 2013, 10:54 am
Ditto to Phil. Twitter changed their API and I'm wondering how to change my code. Our developer is out of the picture, but I'm confident with editing our files if I know what to change.

Leave a Comment

Name
Email
Website
Comment
Name and email are required. Your email will not be published.

This post was published on August 22nd, 2010 and updated on March 13th, 2014 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.

Copyright © 2014, Ink Plant. All rights reserved.