Max Manders

What is YQL?

The Yahoo! Query Language (YQL) is a fantastic web service from the Yahoo! Developer Network (YDN) that can dramatically speed up development time if your application involves requesting data from multiple disparate web services and somehow combining that data.  You can think of YQL as a gateway API that allows you to quickly and easily select, filter and combine data from across the web.

How to Use it

Although YQL is a Yahoo! hosted web service that you will ultimately need to query programatically you can start using it without reading much (if any) documentation thanks to the wonderfully designed YQL console.

Screenshot of the YQL console.

The console is arranged in three main areas: the query input at the top; the results at the bottom; and a list of available tables and examples at the right.  From the menu on the right, you can click on a table name and an example query will be inserted into the query input box and executed.  Browsing through the tables and trying the example queries should give you a feel for the console.

An Example

As an example, let’s say you want to find some photos of kittens from Flickr.

select * from flickr.photos.search where text = 'kitten'

If you paste this into the input box and hit enter (or click Test) you will see the results appear in the results window, and a REST URL appear beneath that.  The REST URL will be used shortly when we see how to use YQL in PHP.  You can select either XML or JSON for the return data, and there’s also a handy tree view to show you the results in a hierarchical tree structure.

Searching for Flickr Kittens

By default, YQL only show tables for Yahoo! owned sites.  However, the YQL project is extensible and community contribution is actively encouraged; more on this later.  To see the complete list of tables, click the ‘Show Community Tables’ link.  You could search Google for YQL:

select titleNoFormatting, url, content, visibleUrl from google.search where q = 'yql'

There are even tables like html and rss to allow you to easily parse valid markup and RSS feeds.

Mixing in Some PHP

The YQL console is great for trying out queries and seeing how they work, but now we want to harness the power of YQL in some server-side code.  For the most part this involves getting a URL from the console, and stirring in some cURL.

// Our query.
$yql = 'select * from flickr.photos.search where text = 'kitten'';

// Build a REST URL.
$url = 'http://query.yahooapis.com/v1/public/yql?q=' . urlencode($yql);

// Some cURL goodness.
$ch = curl_init();
curl_setopt($ch, CURLOPT_URL, $url);
curl_setopt($ch, CURLOPT_RETURNTRANSFER, 1);
curl_setopt($ch, CURLOPT_SSL_VERIFYPEER, false);
curl_setopt($ch, CURLOPT_SSL_VERIFYHOST, false);

// ...and execute.
$data = curl_exec($ch);
curl_close($ch);

// What did we get?
echo '<pre>';
print_r($data);
echo '</pre>';

The returned XML is wrapped in a query element that has some useful metadata; but what we’re interested in is the results element.

Creating a Lifestream

You can view a live demo of my lifestream, which pulls in content from this blog, Flickr, Twitter, Delicious and LastFM.  I’ve made it pretty using some CSS and JQuery (Hoverscroll Plugin).  I’ve also borrowed from the YIU toolbox using the YUI grids builder to quickly throw together a layout.

The important bits of code follow, together with an explanation.  You can find all the code on github.  First we build our YQL query and get the data.

$yql = 'SELECT title '
. ', link '
. ', pubDate '
. ', date '
. 'FROM rss '
. 'WHERE url IN ( '
.   '\'http://maxmanders.co.uk/feed\', '
.   '\'http://feeds.delicious.com/v2/rss/maxmanders\', '
.   '\'http://www.flickr.com/services/feeds/photos_public.gne?id=52727640@N00&format=rss_200\' ,'
.   '\'http://ws.audioscrobbler.com/1.0/user/mmanders/recenttracks.rss\', '
.   '\'http://twitter.com/statuses/user_timeline/623503.rss\') '
. '| SORT(field="pubDate", descending="true")';

$endpoint = 'http://query.yahooapis.com/v1/public/yql?format=json&env=store%3A%2F%2Fdatatables.org%2Falltableswithkeys&diagnostics=false';
$url = $endpoint . '&q=' . urlencode($yql);

$ch = curl_init();
curl_setopt($ch, CURLOPT_URL, $url);
curl_setopt($ch, CURLOPT_RETURNTRANSFER, 1);
curl_setopt($ch, CURLOPT_SSL_VERIFYPEER, false);
curl_setopt($ch, CURLOPT_SET_VERIFYHOST, false);

$data = json_decode(curl_exec($ch));
curl_close($ch);
$data = $data->query->results->item;

First we build a query to select from the rss table.  This table models an RSS feed, and makes it easy to select common RSS fields.  We use the syntax WHERE url IN to aggregate RSS feeds from multiple sources.  Once we have the data, we want to sort the combined feed in descending order of publishing date.  We can then iterate over the data, and output a nice well structured table (via GitHub).