An API for the Web: Learning YQL
The Yahoo Query Language is a great tool that’s guaranteed to speed up your web development time. The more complex your project, the more time YQL will save you. So, is it a framework, an application, a beverage? Today, you’ll find out what it is and how to use it!
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!
How do I use it?
Yahoo has put together a pretty nice console for us to flex our muscles with YQL. Load up that console, and let’s explore it.
In the right sidebar, you can choose a “table” in the “database”; a sample query will show up in the statement box at the top. To the right of the statement box, you can see what the corresponsing REST query would. Below, you have the data returned from the query; you can receive data in either XML or JSON.
So, let’s try a query!
select * from flickr.photos.interestingness(20)
Here’s one of the sample queries; This will return twenty images from the Flickr’s interestingness group. The results of the query look like this:
Let’s try another one.
select * from feed where url='http://rss.news.yahoo.com/rss/topstories'
This query returns each of the recent items in a feed, in this case, the Yahoo News Top Stories. Of course, we could handle it ourselves, but this will be quicker and easier.
You’ll notice that both these queries are for Yahoo sites; out of the box YQL only offers table for Yahoo properties. But they have made it extendable, and many people have written other tables. To get those, click the “Show Community Tables” in the sidebar. Now we can leverage everything from Netflix to the New York Times, from GitHub to Instapaper.
So how can we use YQL in our own projects? Most often, you’ll implement it using cURL, but we could do it in JavaScript as well. Let’s look at the cURL now, and what we get from it.
Let’s take that flickr interestingness query we just looked at; here’s what we do:
$query = 'select * from flickr.photos.interestingness(20)';
// insert the query into the full URL
$url = 'http://query.yahooapis.com/v1/public/yql?format=json&q=' . urlencode($query);
// set up the cURL
$c = curl_init();
curl_setopt($c, CURLOPT_URL, $url);
curl_setopt($c, CURLOPT_RETURNTRANSFER, 1);
curl_setopt($c, CURLOPT_SSL_VERIFYPEER, false);
curl_setopt($c, CURLOPT_SSL_VERIFYHOST, false);
// execute the cURL
$rawdata = curl_exec($c);
curl_close($c);
// Convert the returned JSON to a PHP object
$data = json_decode($rawdata);
// Show us the data
echo '<pre>';
print_r($data);
echo '</pre>';
It’s not too complicated; if you’ve not familiar with cURL, check out Burak Guzel’s great tut here on Nettuts earlier this month. We assign the cURL return value to $rawdata and then convert it to PHP. Calling the print_r function gives us these results.
As you can see, our $data object has one property: query. That property parents all the tasty bits. You can see from the $data->query->count that we received 20 objects, matching our query. But it’s $data->query->results that we’re really interested in; that’s where our data is. The results object has one array in it, called photos.
Armed with this information, we could display the twenty latest interesting photos from flickr (we would use the url http://www.flickr.com/photos/$owner/$id, taking those variable from each photo object.)
I should note here that not all queries will display their results in the same way; some aren’t quite as developer friendly as this one. It’s a good idea to use the YQL console (or just print_r) to check out the results format before proceeding.
So you’ve got an idea of what YQL is and how you can use it. Now, let’s use YQL in a small project!
Tuts+ Tweets
Let’s build a Twitter panel that will show the latest tweets from each of the Tuts+ sites’ Twitter accounts. We’ll start by going to the YQL console and looking at our options. Make sure you’re viewing the community tables. Under the Twitter section, choose twitter.user.profile (which will include the latest tweet), or type this query into the statement box:
select * from twitter.user.profile where id="nettuts"
As we can see from the results in the tree view, the object we’ll get back isn’t formatted quite as nicely as the Flickr ones; however, we’ll make it!
Let’s begin by replacing the Flickr query in the above example with this one. Here’s what we get:
What’s wrong? Since the twitter datatable isn’t one of Yahoo’s built-in tables, we need to tell YQL to use the community tables as well. How do we do that? We’ll add a the key/value env=store://datatables.org/alltableswithkeys to our base URL; now the $url variable should look like this:
$url = 'http://query.yahooapis.com/v1/public/yql?format=json&env=store%3A%2F%2Fdatatables.org%2Falltableswithkeys&q=' . urlencode($query);
Now if we try it …
We’ve got the twitter data!
Now that we’re successfully getting Nettuts’ twitter profile, let’s consider the others. We need to get the profiles of the following accounts:
- Psdtuts
- Vectortuts
- Audiotuts
- Aetuts
- Activetuts
- Cgtuts
- Phototuts
- Tutsplus
So do we need to do eight more cURLs to YQL to get all the data we need? Thankfully, YQL has our back here; we can use this:
SELECT * FROM query.multi where queries="QUERIES GO HERE"
Armed with this knowledge, we’re ready to build our widget. We’ll begin with an array of the twitter queries:
$twitter = array (
'tutsplus' => 'SELECT * FROM twitter.user.profile WHERE id=\'tutsplus\'',
'nettuts' => 'SELECT * FROM twitter.user.profile WHERE id=\'nettuts\'',
'phototuts' => 'SELECT * FROM twitter.user.profile WHERE id=\'phototuts\'',
'audiotuts' => 'SELECT * FROM twitter.user.profile WHERE id=\'audiotuts\'',
'psdtuts' => 'SELECT * FROM twitter.user.profile WHERE id=\'psdtuts\'',
'aetuts' => 'SELECT * FROM twitter.user.profile WHERE id=\'aetuts\'',
'cgtuts' => 'SELECT * FROM twitter.user.profile WHERE id=\'cgtutsplus\'',
'vectortuts' => 'SELECT * FROM twitter.user.profile WHERE id=\'vectortuts\'',
'activetuts' => 'SELECT * FROM twitter.user.profile WHERE id=\'activetuts\''
);
Let’s create our full query now:
$query ='SELECT * FROM query.multi where queries="' . implode(';', $twitter) . '"';
Since it’s getting a bit complicated, we’ll put the root URL in its own variable, and then put everything together. Note that I added diagnostics=false to the root URL; this prevents YQL from returning a bit of extra data with our results. Why? It will just make it easier when we inspect the results in a moment.
$root = 'http://query.yahooapis.com/v1/public/yql?format=json&env=store%3A%2F%2Fdatatables.org%2Falltableswithkeys&diagnostics=false';
$url = $root . '&q=' . urlencode($query);
Now that we’ve got our complete URL, let’s create our cURL, just as we already did:
$c = curl_init();
curl_setopt($c, CURLOPT_URL, $url);
curl_setopt($c, CURLOPT_RETURNTRANSFER, 1);
curl_setopt($c, CURLOPT_SSL_VERIFYPEER, false);
curl_setopt($c, CURLOPT_SSL_VERIFYHOST, false);
And like last time, we’ll catch the results, convery the JSON to a PHP object, and output them for inspection.
$data = json_decode(curl_exec($c));
curl_close($c);
echo '<pre>';
print_r($data);
echo '</pre>';
I won’t show them to you here, but you should be able to stroll through them and see the pieces of data we want to pull out. Notice that the results object has a results array inside it; that’s a bit unexpected, but I believe it has something to do with the fact that we’re executing multiple queries. When you’re done, head back to the PHP and create a $results variable (and don’t forget to remove the printr code):
$results = $data->query->results->results;
The HTML
Now that we’ve got our data, its time for some HTML. Let’s throw in a basic template under the PHP:
<!DOCTYPE html>
<html>
<head>
<meta charset='utf-8' />
<title>Yahoo Query Language Introduction</title>
<link rel="stylesheet" href="default.css" />
</head>
<body>
<h1>The Tuts+ Network: Latest Tweet</h1>
<ul>
</ul>
</body>
</html>
Now, inside the list, we’ll use some PHP to iterate over each of the items in the $results array we extracted and build an entry for it. First, set up the loop:
<?php for ($i = 0; i$i < count($twitter); $i++) : ?>
<li>
</li>
<?php endfor; ?>
Inside that list item, open a PHP codeblock; we should begin by setting up a few variables.
$meta = $results[$i]->item->meta;
$item = $results[$i]->item->item;
$link = $results[$i]->item->resource;
Unfortunately, the author of the twitter table didn’t make the return object too easy to work with; instead of using key/value pairs, each key and value are entries in their own array. So it won’t be incredible obvious what we’re each object reference is when we’re done. However, remember that this is all subject to the author. The flickr table we looked at earlier—or the RSS table that you should check out—is a much more usable API.
So what do we want in our twitter widget? Let’s show the user avatar on the left and their name, username, latest tweet, and time of latest tweet on the right. To do so, let’s add this below those variables:
<?php echo "<a class='img' href='$link'><img src='" .$item[0]->resource ."' alt='" . $meta[0]->content ."' /></a>"; ?>
<div>
<?php echo "<a href='$link'>" .$meta[0]->content . "</a> "; ?>
<small>(<?php echo $meta[1]->content ?>)</small>
<small> <?php echo $item[1]->meta[2]->content; ?> </small>
<?php echo '<a href="' . $item[1]->resource . '">' . $item[1]->meta[1]->content . '</a>'; ?>
</div>
I know it’s a bit cryptic, but if you look at this and then look at the object we printed out to the browser, you’ll see that it works out nicely. We start with an anchor, and put the avatar image in it. After that, inside a div, we make another link for the name, which links to their twitter page. Then we put their username and time of last tweet in small tags (and if we wanted to, we could convert the time to something a little more viewer-friendly). Finally, we put their latest tweet in an anchor; cliking it will take you to the tweet’s page.
Here’s what this looks like:
Not pretty yet, but we’ve got some good hooks for our CSS.
The CSS
Nothing complicated here; we start by evening out the landscape:
body {
font: 13px/1.5 'Helvetica Neue', Arial, 'Liberation Sans', FreeSans, sans-serif;
background:#ececec;
padding:10px;
}
img {
border:0;
}
Then we’ll give out list its look and feel:
ul {
margin:0;
padding:0;
border:1px solid #474747;
border-radius:5px;
-moz-border-radius:5px;
-webkit-border-radius:5px;
background:#ccc;
width:50%;
}
li {
min-height:50px;
padding:10px 5px;
list-style-type:none;
border-bottom:1px solid #474747;
border-top:1px solid #ececec;
}
li div {
padding-left:58px;
}
li a.img {
float:left;
padding-right:5px;
}
li a {
display:block;
}
li:first-child {
border-radius:5px 5px 0 0;
-moz-border-radius:5px 5px 0 0;
-webkit-border-radius:5px 5px 0 0;
}
li:last-child {
border-bottom:0;
}
As a final touch, we’ll give each list item a shadow on hover:
li:hover {
box-shadow: 0px 0px 15px #000;
-moz-box-shadow: 0px 0px 15px #000;
-webkit-box-shadow: 0px 0px 15px #000;
}
There you have it! Behold our completed twitter widget:
Doing it with JavaScript
If you’d prefer, you can use jQuery to execute a YQL statement. You can get the plugin—called jquery.queryYQL—on GitHub. It’s pretty simple to use; here’s a modification of the example query:
$.queryYQL("select * from feed where url='http://feeds.feedburner.com/nettuts?format=xml'", function (data) {
var ul = $("<ul/>");
$.each(data.query.results.item, function () {
$("<li/>").append(this.title).appendTo(ul);
});
ul.appendTo($("#content"));
});
Will you use it?
YQL is a pretty powerful tool; it should save you a lot of time by giving you a single, common API to access content all over the web. You really should browse through the list of available tables; you’ll probably find something that will same you a lot of time. Some tables even provide authentication and writing.
Is YQL a tool you’ll use in the future? Let me know in the comments!
Write a Plus Tutorial
Did you know that you can earn up to $600 for writing a PLUS tutorial and/or screencast for us? We’re looking for in depth and well-written tutorials on HTML, CSS, PHP, and JavaScript. If you’re of the ability, please contact Jeffrey at [email protected].
Please note that actual compensation will be dependent upon the quality of the final tutorial and screencast.
But, this can easily be extended to import new stylesheets, if you wish.
