mySQL row counter

Every now and then I come across a snippet of code which is incredibly useful at the time, but rarely used. The problem with these snippets is that I usually forget the particulars by the next time I need to use it.

Today I needed to use such a snippet and luckily for me, I knew exactly where I had last used it and was able to go straight to the source and grab it. This piece of SQL adds a row counter column to the result set. This is handy if you need to add a rank to records on a bulk insert, or create a new sequential key for on-the-fly table joins.

Here's a fairly simple example where the result set would return up to 50 rows:

SELECT (@rownum:=@rownum+1) rank, fieldname
FROM (SELECT @rownum:=0) ranks, tablename
WHERE something=TRUE ORDER BY fieldname ASC LIMIT 50

This concept can be taken one step further. In this example we update table_one's new_id field with table_two.id sequentially from the row with id value of 151.

UPDATE table_one INNER JOIN (Select @rownum:=@rownum+1 rank, id
FROM (SELECT @rownum:=150) ranks, table_two
WHERE something=TRUE) temp_table ON table_one.id = temp_table.rank
SET table_one.new_id = temp_table.id

Obviously this second example will only work where the id values are entirely sequential (no missing rows), but it worked well for the task I had to do and saved me from dumping the data into a temporary table just to be able to join for a one-to-one update.

I hope you'll find this as useful and interesting as I did :)

WordPress Plugins

Earlier this month I decided to move pantsonhead.com from it's ancient codebase onto a current CMS platform. The goal was to make it easier to manage, and hopefully spur me into making more frequent posts, and to get some experience with another platform.

After a little deliberation and discussion with friends I decided to go with WordPress. With WordPress MU and BuddyPress looking to be quite big in the near future I thought this was a smart choice. I'd tried Joomla! before but didn't have a great experience. ExpressionEngine was also a possibility, I've used it before and liked it a lot and ExpressionEngine 2.0 being built on CodeIgniter sounds great.

In the end WordPress won out because it's open-source, because pantsonhead.com is 90% blog, and because the plugin development community seems very active. For me, writing Plugins is an attractive way to work on some bite-sized code projects for fun, and maybe even profit.

The conversion to WordPress was fairly painless although I did have to create a couple of plugins to provide the functionality I wanted. I decided to release some of these into wild and the response so far has been quite positive.

Activity Sparks
Activity Sparks is a highly customizable widget to display a sparkline style graph in your sidebar indicate post and/or comment activity. This WordPress plugin leverages Google’s Chart API, so does not require the PHP GDI library. Customization options include Title, size, colour, background transparency, activity granularity and period.

RandomText
RandomText is a handy WordPress plugin that allows you to save, edit and delete categorized text, and inject random text by category into the sidebar (via widget) or pagebody (via template tags). Whether you want to display random trivia, quotes, helpful hints, featured articles, or snippets of html, you can do it all easily with RandomText.

RSS Blogroll
RSS Blogroll allows you to link to your favourite blogs via the latest items from their RSS feed. Article titles are much more attention grabbing and will deliver much higher quality traffic. We all hate clicking through to abandoned blogs – displaying article publication dates also lets readers know these are up to date and active sites. RSS Blogroll will create deeplinks to the target sites, which are much more useful for SEO than homepage links. Overall it’s a win-win situation with a better browsing experience for users and the linked sites getting more visitors who are actually interested in their content.

I'll be posting any future WordPress plugins on pantsonhead.com.

Newspaper Headline Image Generator

Last week I posted an article about creating an image using RSS + GDI. It featured a fairly simple example that was not always appropriate for the text contained. Yesterday I revisited the concept and produced the following image which feeds off the latest BBC News Headlines.

I've made some canny modifications to get the execution time down, but it still really needs short-term caching, and a link to the feed source. It's adequately working with most RSS/Atom feeds tested.

(Originally posted on pantsonhead.com)

News Headline Image Generator

I'm feeling a little guilty about not posting anything here for a few weeks, so I thought I'd put this up here since it's fairly developer oriented (even though I posted it 11 months ago on my other site).

I started mucking about with generating graphics from PHP's GDI and RSS feeds and put this together for your viewing pleasure...

Now you can get the latest BBC News Headline with a smile, whatever the content (which is sometimes less than appropriate). I also put some related stuff here that you might also find to be a diversion. I’m not finished with this concept yet, so eventually we might see a more refined version.