Query the Web with a Simple Tag: YQL Custom Tag + examples

Sep10
2009 Written by Jon Dowdle

I've created a Coldfusion tag to make using YQL (even) easier. I was really surprised to find nothing doing this already. I'm not sure if it is just to simple in the first place or that there is a general lack of interest. Feel free to shed some light on this in the comments. Enough talking already, lets get some demo action going.

The basic usage is pretty similar to another oh-so familiar tag:

<cf_yql>
Select * from flickr.photos.recent
</cf_yql>

<cfloop query="cfyql">
 <!---Format of the static urls to flickr images--->
 <cfoutput>
   <img src="http://farm#farm#.static.flickr.com/#server#/#id#_#secret#_s_d.jpg" />
 </cfoutput>
</cfloop>

Creates...

Top Flickr Photos

As you can see, the format is pretty similar to cfquery. The table name "flickr.photos.recent" is table supported by the YQL (for a full listing of available tables see the footnotes).

Here is another example using an RSS feed:

<cf_yql format="query">
select * from rss where url='http://rss.news.yahoo.com/rss/topstories'
limit 5
</cf_yql>

<cfloop query="cfyql">
 <li>
 <cfoutput><a href="#link#">#title# &mdash; #Source# &mdash; #pubdate#</a>
 <div>#description#</div>
 </cfoutput>
 </li>
</cfloop>
</ul>

Creates...

News Feed

In the future, I'd like to take this further by using YQL's newly added 'execute' functionality.

The project is hosted on github. Feel free to fork me or download the code here.

Edit: Yes, I know this looks like Ray Camden's cf_yql example (http://www.coldfusionjedi.com/index.cfm/2009/9/10/Yahoo-Query-Language) posted earlier today (9/10/09). I had written this post originally on 8/28 and just never got around to publishing it. Ray just lit a fire under my arse by posting his, so thanks Ray!

Items referenced while writing this post:
Posted in Programming

Finding international characters with MySQL

Aug19
2009 Written by Jon Dowdle

I recently had to find the rows in a database that contained any multibyte characters. My first thought was to leverage the difference between length() and char_length(). The query would be similiar to this:

Select pKey, dataCol
From myTable
Where length(dataCol) != char_length(dataCol)

To my surprise this query didn't work. After mulling it over in my mind, I tried a few debugging statements such as:

Select pKey,
  dataCol,
  length(dataCol),
  char_length(dataCol)
From myTable

That only reaffirmed that all return values of the char_length() were equal to the length() results.

Then it hit me, I (or rather the database) was using the latin1 character set. So I quickly cast the data into utf8 and it worked! Below is the resulting query.

Select pKey, dataCol
From myTable
Where length( cast(dataCol using utf8) ) !=
        char_length( cast(dataCol using utf8) )
Posted in MySQL - Tagged ,
« Older Entries Newer Entries »