How We Built GottaLoveBacon.com Using Twitter, PHP And YQL
I love Yahoo’s Development Tools, especially their Yahoo Query Language (YQL). That combined with other services such as Twitter, make it easy to aggregate data to other website.
What is YQL?
Web apps and web services multiply like rabbits. They’re all fun to play with (like rabbits) and fun to integrate into other projects (unlike rabbits). But learning a new API every other day isn’t feasible or fun. And that’s the problem the Yahoo Query Language (YQL) is out to solve.
Think of YQL as the API for the web, the one API to rule them all. It’s not a hard one to learn, so let’s get you up to speed right now!
In this example, I’m going to show you how to use PHP, YQL, and Twitter to build a website like “GottaLoveBacon..com”
Let’s Get Started
To start, we want to set up a database:
CREATE TABLE `tweets` (
`id` bigint(24) NOT NULL,
`data` mediumtext NOT NULL,
`created_at` varchar(150) NOT NULL,
PRIMARY KEY (`id`),
KEY `created_at` (`created_at`),
FULLTEXT KEY `data` (`data`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
Now, here’s the YQL query that you use:
use 'http://www.icanhaslayout.com/twitter.search.xml' as twitter.search;
select * from twitter.search where q='#bacon';
If you go to the YQL console and run this query, it will return the 50 most recent search results.
The Code
Now, set up a functions.php file, to keep your functions in:
<?php
$mysqlserver = "localhost";
$mysqluser = "change me";
$mysqlpass = "change me";
$mysqldb = "gottalovebacon";
function time_passed($t1, $t2){
if($t1 > $t2){
$time1 = $t2;
$time2 = $t1;
}else{
$time1 = $t1;
$time2 = $t2;
}
$diff = array('years' => 0,'months' => 0,'weeks' => 0,'days' => 0,'hours' => 0,'minutes' => 0,'seconds' =>0);
$units = array('years','months','weeks','days','hours','minutes','seconds');
foreach($units as $unit){
while(true){
$next = strtotime("+1 $unit", $time1);
if($next < $time2){
$time1 = $next;
$diff[$unit]++;
}else{
break;
}
}
}
return($diff);
}
function getstuff($url){
$curl_handle = curl_init();
curl_setopt($curl_handle, CURLOPT_URL, $url);
curl_setopt($curl_handle, CURLOPT_CONNECTTIMEOUT, 2);
curl_setopt($curl_handle, CURLOPT_RETURNTRANSFER, 1);
$buffer = curl_exec($curl_handle);
curl_close($curl_handle);
if (empty($buffer)){
return 'Error retrieving data, please try later.';
} else {
return $buffer;
}
}
From here, we are going to run this query in PHP, and store the results in the MySQL table. Save this file as index.php:
<?php
require_once("functions.php");
$perpage = 50;
$dbh = mysql_connect($mysqlserver,$mysqluser,$mysqlpass);
mysql_select_db($mysqldb,$dbh);
$page = (isset($_GET['page']) ? $_GET['page'] : 1);
$searchphrase = "#bacon";
$root = 'http://query.yahooapis.com/v1/public/yql?q=';
$yql = "use 'http://www.icanhaslayout.com/twitter.search.xml' as twitter.search; select * from twitter.search where q='".$searchphrase."';";
$url = $root . urlencode($yql) . '&format=json';
$search = getstuff($url);
$search = json_decode($search);
$search = $search->query->results;
foreach($search->entry as $d){
$id = $d->id;
$id = explode(":",$id);
$d->id = end($id);
$info = serialize($d);
$sql = "INSERT INTO tweets SET id='{$d->id}',data='{$info}',created_at='{$d->published}';";
mysql_query($sql);
}
$sql = "SELECT count(*) as cnt FROM tweets ORDER BY id DESC";
$qry = mysql_query($sql,$dbh);
$row = mysql_fetch_assoc($qry);
$total = $row['cnt'];
$start = ($page - 1) * $perpage;
$end = min( ($start + $perpage), $total );
?>
<div class="tweets">
<?php
$sql = "SELECT * FROM tweets ORDER BY id DESC LIMIT ".$start.",".$end;
$qry = mysql_query($sql,$dbh);
while($row = mysql_fetch_assoc($qry)){
$d->id = $row['id'];
$d = unserialize($row['data']);
$diff = time_passed(strtotime($d->published), strtotime('now'));
$units = 0;
$published = array();
foreach($diff as $unit => $value){
if($value != 0 && $units < 2){
if($value === 1){
$unit = substr($unit, 0, -1);
}
$published[]= $value . ' ' .$unit;
++$units;
}
}
$published = implode(', ',$published);
$published .= ' ago';
$d->profile_image_url = $d->link[1]->href;
$d->from_user = $d->author->uri;
$d->from_user = explode("/",$d->from_user);
$d->from_user = end($d->from_user);
?>
<div class="tweet">
<div class="twitter_avatar"><img src="<?php echo $d->profile_image_url; ?>" alt="" /></div>
<p><?php echo preg_replace('/(^|\s)@(\w+)/','\1<a href="http://twitter.com/\2">@\2</a>', $d->title);
?>
<em>by</em> <a href="http://twitter.com/<?php echo $d->from_user; ?>"><?php echo $d->from_user;
?></a>
<?php echo $published; ?> <em>from</em> <?php echo html_entity_decode($d->source); ?> |
<a href="http://twitter.com/<?=$d->from_user?>/status/<?=$d->id?>">view</a></p>
</div>
<?php
}
?>
</div>
<ul class="pagination">
<?php
if($page == 0){$page = 1;}
for($i = 1;$i <= ceil($total/$perpage);$i++){ ?>
<li class="<?=($i == $page ? "active" : null)?>"><a title="Goto page <?=$i?>" href="index.php?page=<?=$i?>"><?=$i?></a></li>
<?php } ?>
</ul>
<?php
mysql_close($dbh);
?>
And that's it. You can change the #bacon to anything you want it to search for, and you can also play with the other twitter queries to look at doing searches via YQL, or even combine web services to add more features, like geolocation, but this is about 10 minutes work to do this so far, and it seemed a fun, cool topic to throw out to you guys.
Comments
About Author
Add Yours
YOU