If you do a lot of research using web searches can be frustrating. Different search engines have different results, you need to open things in tabs and in general it can be pretty time consuming to find what you need.

To make this a bit easier I thought it’d be cool to have an interface that searches Yahoo, Google and Bing at the same time and thus I built GooHooBi:

As explained in the screen cast the thing under the hood of GooHooBi is YQL. Instead of fussing about with all the different search APIs all I did was to play with the YQL console and put together the following YQL statement:

select * from query.multi where queries=’

select Title,Description,Url,DisplayUrl

from microsoft.bing.web(20) where query=”cat”;

select title,clickurl,abstract,dispurl

from search.web(20) where query=”cat”;

select titleNoFormatting,url,content,visibleUrl

from google.search(20) where q=”cat”

‘

The query.multi table in YQL allows you to list a few queries which will be executed one after the other on the YQL server. The queries themselves I put together by using the different tables in the console and only selecting what I really need from each of them.

You can try this query in the YQL console and you can see the JSON output.

The rest is pretty easy. Cut this up into a parameterized string and do a cURL call:

$query = filter_input(INPUT_GET, ‘search’, FILTER_SANITIZE_SPECIAL_CHARS);


	
$queries[] = ‘select Title,Description,Url,DisplayUrl ‘.

‘from microsoft.bing.web(20) where query=”’.$query.’”’;

$queries[] = ‘select title,clickurl,abstract,dispurl ‘.

‘from search.web(20) where query = “’.$query.’”’;

$queries[] = ‘select titleNoFormatting,url,content,visibleUrl ‘.

‘from google.search(20) where q=”’.$query.’”’;

$url = “select * from query.multi where queries=’”.join($queries,’;’).”’”;

$api = ‘http://query.yahooapis.com/v1/public/yql?q=’.

urlencode($url).’&format=json&env=store’.

‘%3A%2F%2Fdatatables.org%2Falltableswithkeys&diagnostics=false’;

	
$ch = curl_init();

curl_setopt($ch, CURLOPT_URL, $api);

curl_setopt($ch, CURLOPT_RETURNTRANSFER, 1);

$output = curl_exec($ch);

curl_close($ch);

$data = json_decode($output);

Then loop over the results and assemble the HTML output.

You can check the source code of GooHooBi.

• The HTML/JavaScript version JS source JS live.
• The PHP version PHP source PHP live
• The PHP “API” API source API live

In addition to this, here’s a half hour live coding screencast how to build something similar:

Building a search mashup with YQL using Google, Yahoo and Bing – live :) from Christian Heilmann on Vimeo.

The source of the code built in this screencast is also on GitHub.

Yesterday Johan Bouveng challenged me on Facebook with another brain teaser. Johan had found a nice resource for weather forecasts for airports. The data came back in TAF or METAR formats and he already had a parser for this (thank f*** – what a mess these formats). Now, what he wanted to have is an API to get the weather forecast data from the following resources:

• http://weather.noaa.gov/pub/data/forecasts/taf/stations/
• http://weather.noaa.gov/pub/data/observations/metar/stations/

Using YQL and YQL execute this was pretty easy. All I had to do was to write an open table that reads the correct file.

// check if taf or metar was requested or return an error

if(datatype  'taf' || datatype  ‘metar’){

var returnobj,url;


	
// get the correct url using the airport ID and format

if(datatype==’metar’){

url = ‘http://weather.noaa.gov/pub/data/observations/’ +

‘metar/stations/’ + airportid + ‘.TXT’

} else {

url = ‘http://weather.noaa.gov/pub/data/forecasts/’+

‘taf/stations/’ + airportid +’.TXT’;

}

	
// do a REST call and get the response back

var out = y.rest(url).get().response;

	
// if there is no data returned, return an error.

if(out == ‘’){

returnobj = Airport {airportid} not found.;

	
// otherwise return the data in the TXT file

} else {

returnobj = {out};

}
	
} else{

// error condition for wrong datatype

returnobj = Datatype must be either taf or metar.;

}

	
// give back the data to YQL

response.object = returnobj;

Having done this you can now use it as a table in YQL:

use “http://isithackday.com/hacks/airportweather/airportweather.xml” as aw;

select * from aw where airportid=”AAXX” and datatype=”taf”;

As you can see, you don’t have to be a genius to build your own API :)

Another reason to love Twitter: You ask for APIs and you get them. As requested in my Tweet, speaker rate acted very quickly and build an API
for latest comments – and here is my thank you. Using a bit of YQL magic and some JavaScript I put together a badge to show off the latest speakerrate comments on your page:

Check out the demo page of the badge and get the source and read the docs on GitHub.

Usage:

Simply add a DIV with a link to your speaker page on speakerrate:

Rate me on Speakerrate

Then add the script and call the init() method:

You can also provide a few options to the init() method to change the look and features of the badge:

If you need different styles, just use styled:false or override the ones applied.

Displaying photos from Flickr can be daunting. The API needs authentication and the RSS, JSON or LOLcode output is very limited. The way around is using YQL and its Flickr tables. That way it is pretty easy to search Flickr:

select * from flickr.photos.search where text=”panda”

Try out the Panda search in the YQL console.

The output format has a lot of information in there, but sadly enough, not all. For example the real name of the owner or the description is missing. Therefore you need to go through yet another Flickr API to get the whole data set:

select * from flickr.photos.info where photo_id in(

select id from flickr.photos.search where text=”panda”

)

Guess what? You can also try this more detailed query in the console.

I’ve shown before how easy it is to display Flickr Photos retrieved that way:

The issue with that though is that it uses JavaScript and JavaScript may be turned off (think Blackberries). Of course you can do the same thing in PHP but I’d wager to say more people to JavaScript than PHP these days.

The main issue is that Flickr returns the photos in a pretty weird format and that you need a script like the one above to turn it into a simple HTML list.

The good news is that YQL with the execute command allows you to embed JavaScript in your open tables. That way you can write a table that does all the necessary transformations and returns the data as a simple list for immediate use:

select * from {table} where location=”london,UK”
Christian Heilmann
http://www.wait-till-i.com/2009/11/01/getting-a-list-of-flickr-photos-by-location-andor-search-term-with-a-yql-open-table
Searches Flickr by location and/or search term and returns an HTML list that can be immediately used in a mashup.
















var amt = amount || 10;

var query = ‘select farm,id,secret,owner.realname,server,title,’+

‘urls.url.content from flickr.photos.info where ‘+

‘photo_id in (select id from flickr.photos.search(‘+

amount + ‘) where ‘;

if(location!==null){

query += ‘woe_id in (select woeid from geo.places where text=”’ +

location+’”) and ‘;

}

	
query += ’ text=”’ + text + ‘” and license=4)’

var x = y.query(query);

var out = 
;

for each(var cur in x.results.photo){

var li = 
;

var a = ;

a.@[“href”] = cur.urls.url;

var img = ;

var url = ‘http://farm’ + cur.@farm + ‘.static.flickr.com/’ +

cur.@server + ‘/’+cur.@id + ‘_’ + cur.@secret +

‘_s.jpg’;

img.@[“src”] = url;

img.@[“alt”] = cur.title;

a.img = img;

li.a = a;

out.li += li;

}


	
response.object = out;

]]>

You’ll notice that while the E4X support is very powerful, it can be a bit confusing to look at on first sight. Once you got your head around though it becomes much cleaner that way.

You can use this table like any other open table via the use command in YQL:

use “http://github.com/codepo8/yql-tables/raw/master/flickr/flickr.photolist.xml” as flickr;

select * from flickr where text=”me” and location=”uk” and amount=20

try it in the console.

I’ve wrapped one more API in there – the Yahoo Geo API to determine a place from a name should you want to search by location. All in all you have three parameters in this open table – all of which are optional:

• text – the search text
• location – the geographical location
• amount – the amount of photos to be returned

If you look at the table source, you can also see that I hard-wired the license of the photos to 4 which is CC-BY. So if you link the photos back to Flickr you both satisfied Flickr’s terms and the original photographer’s.

Now, the easiest way to use this output is by using YQL’s JSON-P-X output format. This is XML with a callback which returns a JSON object with the HTML as a string instead of a convoluted JSON object. See the JSON-P-X output here.

That way you can easily use it in JavaScript:

And also in PHP:

$url = ‘http://query.yahooapis.com/v1/public/yql?q=use%20%22http://github.com/codepo8/yql-tables/raw/master/flickr/flickr.photolist.xml%22%20as%20flickr;%20select%20*%20from%20flickr%20where%20text=%22me%22%20and%20location=%22uk%22%20and%20amount=20&format=xml&diagnostics=false’;

$ch = curl_init();

curl_setopt($ch, CURLOPT_URL, $url);

curl_setopt($ch, CURLOPT_RETURNTRANSFER, 1);

$output = curl_exec($ch);

curl_close($ch);

$output = preg_replace(‘/.*
/’,’
‘,$output);

$output = preg_replace(‘/
.*/’,’
‘,$output);

$output = preg_replace(‘//’,’‘,$output);

$output = preg_replace(‘//’,’‘,$output);

echo $output;

?>

You can see both in action on the demo page.

So by using YQL open tables you can not only access complex APIs with YQL, but you could also write complete mashups in JavaScript and have them executed in a safe environment on Yahoo’s servers. Your end of the mashup is simply the display which could be a form that works with Ajax when JavaScript is available and renders a static page in PHP (or whatever other server-side language) when JavaScript is turned off. You only need to do one HTTP request – the rest is executed and cached on the YQL server farm – everybody wins.

Avast! Saturday is the annual talk like a pirate day and my colleague Tom Croucher has done a tremendous job to create a YQL solution for translation of English to Pirate

In essence he used YQL to store a translation data set and allows us to alter it using the update and storage parts of YQL. He has come up with a few great things to use for talk like a pirate day:

A script to include into any page that will automatically convert it to pirate speak:

A bookmarklet to translate any web site: piratize (drag it to your links toolbar).

An open YQL table to add to the pirate dictionary.

Using this, I built the following interfaces:

• The Pirate Dictionary add-to form
• The ARRPI – an API to translate from English to Pirate
• The Pirate Translator form with tweet button

Have a great talk like a pirate day! Sadly enough I’ll be Aarrr-ing down from a plane as I am flying back to the UK on that date.