Get All Twitter Posts of a Specific Hashtag in PHP
I got a response to my article about pulling a Twitter feed into your site with PHP asking how to pull all tweets for a given #hashtag. Here, I have published an example of how to do so... It's not saved into a database, you'll have to do that yourself, but this should be enough to get you started. You can see the code below in action here, searching for the hashtag #yankees.
<?php
$pagetitle = "Pull Twitter Hashtags";
require 'header.inc';
function getTweets($hash_tag) {
$url = 'http://search.twitter.com/search.atom?q='.urlencode($hash_tag) ;
echo "<p>Connecting to <strong>$url</strong> ...</p>";
$ch = curl_init($url);
curl_setopt ($ch, CURLOPT_RETURNTRANSFER, TRUE);
$xml = curl_exec ($ch);
curl_close ($ch);
//If you want to see the response from Twitter, uncomment this next part out:
//echo "<p>Response:</p>";
//echo "<pre>".htmlspecialchars($xml)."</pre>";
$affected = 0;
$twelement = new SimpleXMLElement($xml);
foreach ($twelement->entry as $entry) {
$text = trim($entry->title);
$author = trim($entry->author->name);
$time = strtotime($entry->published);
$id = $entry->id;
echo "<p>Tweet from ".$author.": <strong>".$text."</strong> <em>Posted ".date('n/j/y g:i a',$time)."</em></p>";
}
return true ;
}
getTweets('#yankees');
echo "<hr /><p>This page is an example for the article <a href=\"http://www.inkplant.com/code/get-twitter-posts-by-hashtag.php\">Get All Twitter Posts of a Specific Hashtag in PHP</a>.</p>" ;
require 'footer.inc';
?>There are a lot of options with the Twitter Search method. You can find out more by checking out their documentation.
This post was published on Tuesday, May 31st, 2011 by Robert James Reese in the following categories: PHP, Twitter. Before using any of the code or other content in this post, you must read and agree to our Terms & Conditions.
41 Comments
Sorry to be an absolute pain and ask you so many questions but I'm a complete newbie when it comes to writing PHP Code.
I have managed to merge the code code for pulling hashtags (above) with your code to pull a feed and write to a database but I keep getting a DB Error when I run the script although the script does write ONE record to my new empty database.
I've pasted my code below and would welcome any hints as to where I have gone wrong if you have the time to have a look at it please.
–- Start Paste –-
require_once 'db-functions.inc.php' ; //custom database functions
function saveTweets($hash_tag) {
$link = dbConnect('db user','db password','localhost','db name');
if (!$hash_tag) { echo "Error: No hash tag declared.n"; return false; }
$row = dbGetRow("SELECT `id` FROM `doncaster` WHERE `author`='$author' ORDER BY `id` DESC LIMIT 1");
$last_id = $row['id'];
$url = 'http://search.twitter.com/search.atom?q='.urlencode($hash_tag);
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->entry as $entry) {
$text = trim($entry->title);
$author = trim($entry->author->name);
$time = strtotime($entry->published);
$id = $entry->id;
dbQuery("INSERT INTO `doncaster` (`id`,`author`,`time`,`text`,`hidden`) VALUES ('$id','$author','$time','$text','n')");
$affected = $affected + dbAffectedRows();
}
return "".number_format($affected)." new tweets from $hash_tag saved.n" ;
}
echo saveTweets('#doncaster');
–- Finish Paste –-
Thank you in advance for any guidance you may be able to offer.
Michael :o)
No worries. It's cool to know that someone is actually reading these things. :-)
What was the database error you got? I have a hunch that there was a single quote in one of the values you were trying to insert. It's important for that reason (and more importantly for security) to always escape the strings before inserting them. The PHP function for this is mysql_real_escape_string() but that's really long so I shortened it to dbEscape() in my database functions that I see you're using. So, try this:
dbQuery("INSERT INTO `doncaster` (`id`,`author`,`time`,`text`,`hidden`) VALUES ('".dbEscape($id)."','".dbEscape($author)."','$time','".dbEscape($text)."','n')");
Does that work?
I've just updated the line that you've included above but still getting the same thing.
The script is currently installed on my 'test bed' - You can access it at: http://rover.doncaster.tk/rover3.php
The database error simply reads: "Error: Database error!"
Thank you so much for your assistance with this it's very much appreciated :-)
Two things to try to track it down:
Add "global $debug;" to your dbError function (I've updated it here to reflect that.)
And then, at the top of rover3.php add "$debug = true;" That should result in the actual database error being returned. When you get this ironed out, you'll want to remove that so that actual MySQL errors are never shown to your users (again for security reasons).
OK I've updated and the error message showing reads:
"Error: Database error!
Query: INSERT INTO `doncaster` (`id`,`author`,`time`,`text`,`hidden`) VALUES ('tag:search.twitter.com,2005:75926803759378432','andrewisaacs (Andrew Isaacs)','1306937335','RT @DMWmagazine: OK people. The all important announcement… who won the signed Rovers shirt?! Find out here on Friday! #doncasterisgreat #drfc','n')
Error: Duplicate entry '0' for key 1"
dbQuery("ALTER TABLE `doncaster` CHANGE `id` `id` VARCHAR( 255 ) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL");
After you run that once, delete it or comment it out.
After doing that, you should also update your query to this so that duplicates are ignored instead of throwing an error: dbQuery("INSERT IGNORE INTO `doncaster` (`id`,`author`,`time`,`text`,`hidden`) VALUES ('".dbEscape($id)."','".dbEscape($author)."','$time','".dbEscape($text)."','n')");
In the original post, the API call should not have returned duplicates, but in this new call, it might. The IGNORE tells MySQL not to throw an error when that happens.
OK on checking my DB I now have new posts with ID's that look like this:
"tag:search.twitter.com,2005:75927564367040513"
and Authors that look like this:
"MODEmagazineuk (MODE Magazine)"
Just a couple of small things which would be good to know for the future:
1/ If I wanted to pull from more than one hash tag what would I need to add to my script to do so?
2/ Is there a way I can clean up the ID's so as they are just the numeric part or do I need to leave the test string on them?
echo saveTweets('#doncaster');
echo saveTweets('#yankees');
2.) Yes, you could, but I'd just leave them alone for simplicity's sake. Look into preg_replace() if you decide to. Here's an example:
$id = preg_replace('/[^0-9]/','',$id);
My database name is _tweets, and the table name is twitter.
My code looks like:
entry as $entry) {
$text = trim($entry->title);
$author = trim($entry->author->name);
$time = strtotime($entry->published);
$id = $entry->id;
dbQuery("INSERT IGNORE INTO `twitter` (`id`,`author`,`time`,`text`,`hidden`) VALUES ('".dbEscape($id)."','".dbEscape($author)."','$time','".dbEscape($text)."','n')");
$affected = $affected + dbAffectedRows();
}
return "".number_format($affected)." new tweets from $hash_tag saved.n" ;
}
echo saveTweets('#NP');
?>
-END PASTE-
The errors I'm receiving are:
Warning: mysql_connect() [function.mysql-connect]: Access denied for user 'user'@'localhost' (using password: YES) in /home/ombian/public_html/db-functions.inc.php on line 33
Warning: mysql_select_db(): supplied argument is not a valid MySQL-Link resource in /home/ombian/public_html/db-functions.inc.php on line 35
There was a problem connecting to our database.
-END PASTE-
I copy and pasted your db-functions page, and just saved it with the .inc.php on the end. I'm not sure if that's the correct way to do it.
I appreciate all your help, this is an awesome website.
Those errors sound like you simply have the wrong MySQL username/password combo. Make sure to update the $db array (at the top of db-functions.inc.php) with the appropriate info for your server.
Now I am getting the error:
Fatal error: Call to undefined function dbconnect() in /home/ombian/public_html/tweetsToDatabase.php on line 14
I don't see a dbConnect function in db-connect, is that a custom function Michael made?
Thanks again.
I'll comment again (hopefully saying I had success!) when I take out that dbconnect line.
Database error!
There was a problem getting information from our database.
That looks like it's coming from db-functions.php. Any thoughts?
Thanks again.
You're awesome, thanks. I'll definitely recommend this site any change I get.
Thank you for your tutorial, I have managed to get this working really well!
I have an additional question:
When I search for a specific hashtag, I would like the script not to save the entire tweet in one colum in mysql, but put the tweet (which would have to be presented by users by a strict format, let's say: '#hashtag number text') in different columns, which would contain 'number' and 'text'. Any guess how I can break up the tweet, or tell mysql how to break up the tweet?
It's possible to split it within either MySQL or PHP, but I'd recommend splitting it in PHP, it's easier that way. Look up the strpos and substr functions on php.net.
Thanks,
I found out now that php will be fine, exploding will works as well I guess
My boss wants me to create a MY Sql database that can be used to retrieve specific twitter users or those using a specific hashtag. Please let me know how I can start working on it.
Very much a newbie to this - this page was very helpful. Thank you!
Does your above code work completely independently of the Twitter API?
Thanks,
James
Cheers!
And Sam, add &rpp=2 in the URL variable.
It looks like your code is only getting the latest tweets, how would I get a greater number of tweets, like from a week ago?
Thanks.
I only have a limited number of cron jobs available on my server, is it possible to run the user name and hash tag scripts on the same page, given they share variables? (I guess the question is how to clear and reuse the variables between scripts.)
i have one question with you…
i need all #tag responds in the same page..
but currently every refresh i'm getting only 15 responds on page. how can i get the all responds from the starting, also i need to count the total counts of responds , thanks in advance, and once again thanks for your above codings
Any idea how i can just pull the twitter id and images to be displayed? instead of just a twitter feed?
I want to use your program to find latest tweets for query that user enters in html textbox. Wen i simply copy pasted you code n run on apache server i got following error:
Warning: require(header.inc) [function.require]: failed to open stream: No such file or directory in C:webstweeter.php on line 3
Fatal error: require() [function.require]: Failed opening required 'header.inc' (include_path='.;C:phppear') in C:webstweeter.php on line 3
Plz help.Its urgent.
Thanks in advance.
Thanks for all the help..
Can someone get older #tagged tweets, like a month - 2months ago?
Please help
Leave a Comment