Syntax Highlighter Chrome Extension: Sight

Last week a colleague introduced me to a handy Chrome Extension called Sight that allows syntax highlighting in your browser and "makes reading code on the browser a joy".

After using it for few days I have to admit that it does make javascript a lot easier on the eye. It has optional line numbers and supports syntax for about 35 different languages.

Sadly it doesn't support view-source highlighting, but apparently it's next on the to-do list and is still worth installing in it's current state.

If you're using Chrome you can get the extension from here https://chrome.google.com/webstore/detail/epmaefhielclhlnmjofcdapbeepkmggh

WordPress Form Helper Functions

Setting the state of checkboxes, radio buttons and selected items in dropdown lists in html forms can become tiresome, so I was pleased to recently discover WordPress has some built-in helper functions. I've found these to be pretty handy in building admin forms for Wordpress plugins and hopefully they can save you some time too.

checked( $checked, $current = true, $echo = true )

This function compares two given values (for example, a saved option vs. one chosen in a form) and, if the values are the same, adds the checked attribute to the current radio button or checkbox. This is essentially the same as comparing values with if(), but results in more concise code. The third parameter determines if the result is echoed or just returned which is handy if you are concatenating to a string, etc.

This function has been defined since WordPress v1.0 and is documented here.


selected( $selected, $current = true, $echo = true )

This function is for use in dropdown form fields. Compares two given values (for example, a saved option vs. one chosen in a form) and, if the values are the same, adds the selected attribute to the current option tag. Again, the third parameter determines if the result is echoed or just returned which is handy if you are concatenating to a string, etc.

This function has been defined since WordPress v1.0 and is documented here.


disabled( $disabled, $current = true, $echo = true )

This function compares two given values (for example, a saved option vs. one chosen in a form) and, if the values are the same, adds the disabled attribute to a form input field. Again, the third parameter determines if the result is echoed or just returned which is handy if you are concatenating to a string, etc.

This function was not defined until WordPress v3.0 and is documented here.

These functions are all located in wp-includes/general-template.php.

WordPress Post ID from Permalink

Lately I've been migrating a few websites from Movable Type to WordPress. One of the SEO issues involved with that is supporting the old site's URLs so that existing page rankings don't drop off en masse.

A common Movable Type URL format appends the entry id to the URL which makes it easy to redirect to the correct content IF you have preserved the entry id from MT as the new post id in WP, other times it can be a lot more convoluted.

One of the functions I sometimes need to employ is pretty uncommon, (which is not surprising due it's rare use), so I thought I'd create a quick post here so I can can easily find it next time I need it.

The function is url_to_postid()

This function returns the id for a post or page from a given URL. I like to this of this function as the reverse of the commonly used get_permalink().

It's pretty hard to turn up any useful search results for this function so hopefully this page will help somebody when they need it.

Pro Tip: Check It Yourself

Yesterday I was pushing a module of new code to the blog site of a moderately famous celebrity that is still creaking along on Movable Type (the site, not the celeb). I've not really had to work with Movable Type much before so it was a little bit interesting and I learnt a few bits and pieces.

The code module was working exactly as expected on the development site so I was a bit surprised when it failed on the production site, truncating the rendering of some posts.

Debugging code on a production server is a tricky business (especially when you can't find the error logs), but I tracked it down to this:

PHP Fatal error: Call to undefined function: file_put_contents() ...

The function file_put_contents() was introduced with PHP version 5, so this error indicated the production server was running some version of PHP4. This kind of disparity between production and development environments is certainly not the optimal configuration, but it's not entirely uncommon when working with legacy systems. The problem for me yesterday was that it took me a lot longer to identify and fix the problem because I was sure that the production site was on PHP5...

I'd asked the rest of the development team if the sites I was working on with this module were on PHP5, they were pretty sure they were, but recommended I checked with the ops team. A member of the ops team told me they were all on PHP5 too. I don't know if the error was due to a chat-window miscommunication, lack of familiarity with the site, or just a genuine mistake - it's not really a big deal. In reality, it probably would have been quicker to just check the phpversion() myself when I first thought to ask about it - I would have discovered it was on PHP4 and altered the module's code before I tried to push it to the production environment.

IE8 Ajax Caching/Session Issue

Today I spent a bit of time trying to track down an ajax problem the was only occuring in IE8.

The ajax funcionality was calling a static URL which returned a JSON result containing the html to display login buttons depending on the user's state. The call was made via jQuery.getJSON(), which is wrapper function for .ajax() and .parseJSON()

The problem was that IE8 was exhibiting fairly unpredictable behaviour, sometimes it seemed that call was not firing, other times it appeared to return the opposite result I was expecting.

Initially it looked like IE8 was not passing the user's session. A quick Google search returned a number of threads where developers had assumed this to be the case, but there were very few constructive suggestions in addressing the issue. Eventually I spotted something where the true issue had been identified as IE8 caching the ajax response.

I added a cache-busting parameter to my ajax URL using the following javascript and voila, everything starting working as expected.

"...&cachebuster="+Math.random()

Hopefully this short post will save you some IE8 related head-scratching.

Firefox 3.6.6 redirect issue

Today I spent a chunk of time investigating an issue related to redirecting browser requests for media files to the CDN (content delivery network) for that domain. The redirect was being thrown into a redirection loop. The curious thing about the issue is that it only affected users using Firefox 3.6.6 on Microsoft Windows.

I tested the MP3 player in Opera, Chrome and even Internet Explorer and everything performed as expected, but when I used Firefox 3.6.6 it failed every time for any file. Then I checked it in Firefox 2.0 and Firefox 3.5.10 - both did not reproduce the issue. I checked it in Firefox 3.6.6 on an Apple Mac and the issue could not be reproduced. Neither could it be reproduced in Firefox 3.6.7(beta) on Windows.

Multiple Firefox Installations
I decided to grab Firefox 3.6.4 (the previous release - there was no 3.6.5) and install that to see if just this version was experiencing the issue. To do this I decided to set up another Firefox install - If you've not done this before, there's a great guide here: http://idizyn.com/development/installing-multiple-versions-of-firefox/ The post is pretty old, but what worked for Firefox 1.5 and 2.0 still works now.

Installing Firefox 3.6.4 confirmed my suspicion that this problem only occurred so predictably in Firefox 3.6.6.

What Caused This??
I recorded the headers for different browser versions using Live HTTP Headers. This highlighted that Firefox 3.6.6 was being thrown into redirection loop, but didn't explain why. I added timestamps to the redirection point and saw that the redirection was not reflecting any change in that, so some level of caching was involved - but this cached result did not occur for any other browser version (even on the same machine).

I disabled my Add-ons, flushed my cache, and restarted the browser multiple times to no avail - I could not isolate either a cause or a solution. At this point I can only suggest there is a problem with this release of Firefox causing the problem.

Firefox 3.6.7 is currently slated for release on 20 July 2010 (in 6 days). Hopefully this will address this issue and many users will quickly upgrade without us having to suggest it as a solution to this issue.

WordPress Broken Theme : Template is missing

I came across an interesting bug on a Wordpress development project I was working on this week. The issue was that the Wordpress Theme was reported as a Broken Theme because the "Template is missing" on the Appearances->Themes admin page.



In most cases the site was performing as expected using the allegedly broken theme, however the custom template selection was no longer available on the Pages admin page. (This is where I first spotted there was a problem and then discovered the Appearances->Themes admin page.)

After poking about for a bit, I traced the problem to a rather innocuous looking line the designer had added to the theme's style.css file.

/* UNIQUE CATEGORY TEMPLATE: VIDEO*/

It's just the "TEMPLATE:" portion that caused the style.css parser to fail. This line works just fine:

/* UNIQUE CATEGORY TEMPLATE : VIDEO*/

I doubt this error comes up often, but it took a few minutes to track down the problem so I thought I'd post something here to hopefully save somebody time in the future.

Google Buzz Button IE8 Error

Recently Google introduced their Buzz buttons to "Help people post your content on Google Buzz". Share buttons are not a new idea, but Google Buzz is new and kinda shiny. Here's a quick intro if you're not up to speed yet:



And here's a Buzz button:   

A few days ago I implemented the Buzz share buttons on a website at the clients request and it all seemed to be working great, until this morning I found that some pages were failing to load in Internet Explorer 8.

I pointed IE8 at the development site and confirmed there was a serious problem. The error I recieved was thus:

Webpage error details
User Agent: Mozilla/4.0 (compatible; MSIE 8.0; Windows NT 5.1; Trident/4.0; Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.1; SV1) ; .NET CLR 2.0.50727; .NET CLR 3.0.04506.30)
Timestamp: Thu, 22 Apr 2010 13:33:25 UTC
Message: HTML Parsing Error: Unable to modify the parent container element before the child element is closed (KB927917) Line: 0 Char: 0 Code: 0

I disabled the Buzz button to confirm it was part of the problem (which it was), and then checked my implementation against the documntation and other sites. Everything seemed to be OK. After a long period of debugging, and following up false leads it turned out to be that Internet Explorer was executing the Google Buzz button's javascript code too early - even when it was in the page footer.

The Solution:
Once again jQuery came to the rescue - I replaced this line:
<script type="text/javascript" src="http://www.google.com/buzz/api/button.js"></script>

with this line:
<script type="text/javascript">jQuery(document).ready(function(){jQuery.getScript('http://www.google.com/buzz/api/button.js');});</script>

Managing WordPress Filters

WordPress filters offer a great way to control many aspects of the way the WordPress engine processes or publishes information, without hacking any of the core code.

In recent projects I've found filters to be the easiest way to manage many of the functionality requests the client has made. Some examples of the are: RSS formatting, custom Avatars, auto rendering images/links in comments, controlling the Category used in permalinks, reformatting legacy data in posts, and even tweaking the behaviour of Admin pages.

All these changes can add up to quite a chunk of code and functions.php was getting quite large and difficult to maintain with all the other general functions in there. I decided to move all the filter functions and add_filter statements into a separate file called functions_filters.php and include it from functions.php. This made it much easier to add/maintain filters and localised the changes to that dedicated file.

It's an incredibly simple idea, but I highly recommend taking this approach if you are implementing filters to any degree.

If you've not used filters but want to learn more, you can find a good introduction here: http://codex.wordpress.org/Plugin_API/Filter_Reference

Suppressing Link URL In WP Media Library

UPDATE: You can now set the default action by adding the following code to your functions.php file.

update_option('image_default_link_type','none');


---

It's been a while since I've posted anything here (I've been pretty busy lately), so when I threw together a quick fix for a WordPress installation today, I thought I should put a quick note about it here.

The Problem:
When inserting or attaching an image to a post with WP's Media Library the Link URL is always pre-populated with the URL to the file. To remove this URL so that the image is not linking to the original file, users must go through the arduous process of remembering to click the "None" button before inserting the image.



OK, that was a little sarcastic, but that's pretty much what the feature request was saying. They wanted the field's behaviour to change so that the default was blank.

The Solution:
To create the desired behaviour, I created the following function and hooked it to the attachment_fields_to_edit filter to clear out the URL before it is displayed.


// hide the URL by default for people too lazy to click "None"
add_filter('attachment_fields_to_edit', 'suppress_linkURL');
function suppress_linkURL($fields) {
$img_url = $fields['image_url']['value'];
if(!empty($img_url)){
$html = $fields['url']['html'];
if(!empty($html)) {
$fields['url']['html'] = str_replace("value='$img_url'","value=''",$html);
}
}
return $fields;
}

I doubt there is going to be a lot of demand for this particular snippet of code, but hopefully somebody will find it helpful.

jQuery image roll-overs

On the site I am currently working on I needed to implement hover/roll-over states for a number of image tags. Other elements in the site use the CSS based x:hover{background:y} approach, but that was not an appropriate solution for these instances - background images were not going to used, so I came up with a nice jQuery based solution that keeps my page-source pretty uncluttered too.

I added a class value of "hover" to the image tags I wanted to exhibit this behaviour. The replacement images all had the suffix _hover added (e.g. theimage_hover.png )

<img src="/images/theimage.png" class="hover">

Then in my main javascript file I added this little snippet:

function imghover_on(img){if(img.src.indexOf('_hover.')==-1){img.src=img.src.substr(0,img.src.length-4)+'_hover'+img.src.substr(img.src.length-4)}}

function imghover_off(img){img.src = img.src.replace('_hover.','.');}

jQuery(document).ready(function(){
jQuery("img.hover").mouseenter(function(e){ imghover_on(this);});
jQuery("img.hover").mouseleave(function(e){ imghover_off(this);});
});


This code declares two functions which add or remove the "_hover" suffix to the image tag's source, while not needing to know if the image is .png .gif or .jpg.

The jQuery statement waits for the page to load and then finds all img tags in the document with the hover class set and binds the function calls to the mouseenter and mouseleave. This saves me from having to add these events to every single image tag manually like this:

<img src="/images/theimage.png" onmouseover="imghover_on(this);" onmouseout="imghover_off(this)">

Notes:

• I used mouseenter/mouseleave as they fire a lot less than mouseover/mouseout (see: http://docs.jquery.com/Events/mouseenter )
  
• It's not infallable, a .jpeg file extension would fail, but it's working nicely for first cut.

WordPress Theme URL Tip

Lately I've been putting a lot of hours into developing a couple of sites using the WordPress and WPMU platforms. These platforms are a solid starting point for pulling a content/post based site together relatively quickly - the wealth of useful plugins also helps reduce the chance of you having to re-invent the wheel.

Having said that there is still a lot to do to create a professional website that has it's own look and feel - much of this can be achieved by creating a custom theme.

If you are creating a custom theme you will probably need to link to multiple elements within that theme's directory structure. The most common approach to do this is somthing like this:

<img src="<?php bloginfo('stylesheet_directory'); ?>/images/myimage.png">

<?php
echo '<img src="'.get_bloginfo('stylesheet_directory').'/images/myimage.png">';
?>

I've certainly been using a lot of the latter style, until today.

I decided to clean up my code a bit and put the following line at the top of my theme's functions.php file:

<?php
if(!defined('WP_THEME_URL')) {
define( 'WP_THEME_URL', get_bloginfo('stylesheet_directory'));
}
?>

Then I replaced all the get_bloginfo('stylesheet_directory') instances with the constant WP_THEME_URL. Now my code is a lot more readable and there is less typing.


<img src="<?php echo WP_THEME_URL; ?>/images/myimage.png">

<?php
echo '<img src="'.WP_THEME_URL.'/images/myimage.png">';
?>

Using a constant like this should provide a slight performance increase here too - I haven't done any performance tests, but the current get_bloginfo() process is a fairly convoluted chain of function calls.

Note: Just in case the WP platform does start using this constant in the future, the constant's value is not set if it is already defined.

UPDATE: After this post was made I discovered WordPress defines a TEMPLATEPATH constant, but no constant currently exists for the TEMPLATEURL.

Animated "Loading" Image Generator

Yesterday somebody sent me an email introducing me to http://www.ajaxload.info/ and it's such a handy tool I thought I should share it here.

Generate a customised animated gif by choosing the style you want, the background and foreground colors and whether the background should be transparent.

Here are some quick examples...

Updating Google Map Marker's z-index

Lately I've been working on a web application that uses Google's Maps API. It's been an interesting and engaging project.

One of the limitations of the current Maps API is that the z-index of a marker cannot be changed after it has been created. The client requested that the selected marker "popped to the front" as some markers obscured others in certain map areas depending on zoom and closeness of coordinates. This was a reasonable request and would enhance the UI, but was not so easy to implement.


Mike Williams gives a good introduction to this issue and details of how to set the z-index of the marker when it is created with addOverlay() in his Google Maps API Tutorial. Having read this, I attempted to re-create each marker when it was clicked and keep track of the top most z-index. I had some success but had unpredictable z-index results and it was definitely an inefficient way to produce the desired effect.

I decided to browse the DOM and see if I could find a better way to do this. I found a guide to Undocumented Google API features which seems to be mostly out of date, but contained the very important details of how to calculate a Marker's default z-index:

Use marker.setZIndex(Math.round(marker.getLatitude()*-100000)) to get a moved marker to overlap correctly.

Even though setZIndex() and getLatitude() are not valid methods in the current API, it's easy to understand the calculation.

In my application I was already using a unique icon for each marker so that they displayed sequential letters (A,B,C...) and had added an index property to the marker object. I was able to leverage this with a bit of jQuery magic to find each icon in the DOM and alter the CSS z-index value. Since the default z-index is something like -108619296, I created a function to toggle the z-index between normal and front positions by multiplying it by -1.

icon = $("#mapbox div div div img[src='/images/markers/"+marker.index+".png']");
zidx = icon.css('z-index');
icon.css('z-index',zidx*-1);

Just to make sure that no other marker was still in the top position, I looped through my array of markers and reset the z-index with this function.

function reset_zorder(marker) {
$("#mapbox div div div img[src='/images/markers/"+marker.index+".png']").css('z-index',Math.round(marker.getPoint().lat()*-100000));
}

Obviously none of this is a copy+paste solution. but it should give anybody needing to manipulate Google Maps MAP Marker z-index a good example to work from.

How To: Android Scrollable Divs

Lately I've been working on a webapp for the Motorola Droid and a QNX CAR device. The application interface is a split screen with one half being a list of items of variable length. Neither of these browsers on these devices support iframes or scrollable divs, so the approach used in a traditional browser was not an option.

The application was using jQuery, so using the jQuery UI Draggable plugin was a natural choice. It was quick and easy to implement and worked well on the QNX device, but didn't work at all on the Android browser.

To get the prototype version of the application finished on time for the deadline, I resorted to adding up/down buttons to the interface that scrolled the div content by manipulating the margin-top via javascript. This satisfied the client, but as a solution it was less than optimal, and in my opinion it made the UI feel clunky.

At the time that I write this, there's not a lot of information about how to deal with this issue in the Android browser. It took me a while to discover that this issue is also a problem for the iPhone/iPod Touch browser and since Android's browser also uses WebKit, there is already a working solution called iScroll developed by Matteo Spinelli on Cubiq.org.

This code utilises WebKit's "touch" events, which are akin to click events, but are only fired by touch screens. You can find a great introduction to Javascript Touch Events on Michael's "Back To The Code" blog. If you're new to developing mobile webapps, you should probably also take a look at iPhone Webapps 101.

Hopefully posting these bits and pieces together here will help save somebody a lot of time searching on Google.

Parsing and visualising JSON

Over the last few days I've been working on a project that uses some fairly complex JSON objects. Because JSON (JavaScript Object Notation) is a lightweight data interchange format, it can be a great way to grab complex data structures via Ajax. It's been said that JSON is "the fat-free alternative to XML".

In theory JSON is easy to parse, but if your data structure starts to contain numerous nested objects and arrays, it can be come hard to keep track of and starts looking like a long jumble of punctuation.

I found Brenton Fletcher's JSON 2 HTML to be a quick and easy way to check your JSON syntax and visualize the data. You can either copy+paste a JSON string or sumbit a URL and the will page load the JSON string from the URL. It's definitely worth checking out if you're working with JSON.

Installing Movable Type 4 on XAMPP

Yesterday, I finally got Movable Type 4 working on my development PC. I needed to install it for a project I am working on, and found it to be a lot more troublesome than expected. Movable Type is a weblog publishing system that was first released back in October 2001. It is written in Perl, and that's where the trouble started...

Downloading the latest version and setting up a virtual host for the test site was all as easy as I would usually expect. Then I pulled up the Quick Start Instructions to make sure I didn't make any assumptions. I carefully followed the process, making allowances for my install being on windows localhost, and met with failure after failure.

After much searching and reading, it became apparent that my old install of XAMPP required a Perl add-on patch. None of the download files I could find seemed to be the correct version, and since the current XAMPP installation package now includes Perl, I decided it was time to upgrade (see my previous post).

After the XAMPP upgrade, I started the Movable Type install again. More failures eventually pushed me to find a much more helpful installation guide from Brian Cantoni. Due to the multiple sites I have set up on my development PC, I used a different path in Step 3, and I chose to use SQLyog instead of phpMyAdmin for steps 4 - 6.

Step 7 is the crucial info missing from the Quick Start Instructions:

In the folder `c:\xampp\cgi-bin\mt4`, edit all the *.cgi files and change the first line to: `#!c:\xampp\perl\bin\perl.exe`

Unfortunately, Cantoni's guide doesn't mention how to handle the config file. Thankfully, you can find that information in Step 2 of this guide on etc. Another helpful hint in etc's guide is to use the MT system check script http://localhost/cgi-bin/mt/mt.cgi before trying to initialise your new install.

This is the point, I found that I still hadn't quite cracked it. I had some kind of Perl/mySQL install issue.

DBD::mysql
Your server does not have DBD::mysql installed, or DBD::mysql requires another module that is not installed. The DBD::mysql database driver is required to use MySQL Database. Please consult the installation instructions for help in installing DBD::mysql.

I resorted to installing ActiveState Perl to see how that compared. It wasn't the solution, but it did help me identify how to fix the DBD::mysql issue (I have subsequently uninstalled ActiveState). It seems that there was a dll file missing from C:\xampp\perl\site\lib\auto\DBD\mysql that XAMPP had already installed elsewhere on my machine. I copied the file C:\xampp\mysql\bin\libmysql.dll and pasted it into C:\xampp\perl\site\lib\auto\DBD\mysql and finally I had a working installation of Movable Type v4.31 on XAMPP v1.7.2.

PHP Parse Error: syntax error, unexpected $end

Yesterday I decided to upgrade my XAMPP install from (ye olde) v1.5.5 to (the current) v1.7.2. This was mainly precipitated by my inability to install Movable Type - apparently due to missing PERL libs. I like XAMPP. In my experience it's the easiest way to install the Apache/PHP/MySQL stack on a Win32 machine and I've been using it for years. So, since there was no simple upgrade path from v1.5.5 to v1.7.2, I set about backing up all of my development mySQL databases, and httpd.conf and extra\httpd-vhosts.conf files, etc...

I like to keep my root folder clean, so on the last install I'd opted for "c:\program files\xampp", but since I'd read that "program files" could cause major PERL problems, this time I installed in "c:\xampp". Installation went pretty smoothly, and I was able to drop in my old extra\httpd-vhosts.conf file with no problems. Then I re-imported the mySQL databases I needed and checked all my development sites were OK.

I did have an unexpected problem when I tested one of my sites - I was presented with the following error:

Parse error: syntax error, unexpected $end in {filename} on line {linenumber}

If you Google that error, you'll see a lot of pages stating that "it is caused by a missing curly bracket" or a bad class definition, which was definitely not the case for me. The cause in this instance was that this particular site's code was using Short Open Tags.

XAMPP's php.ini file states that Short Open Tags is a php.ini directive that:

"...determines whether or not PHP will recognize code between <? and ?> tags as PHP source which should be processed as such. It's been recommended for several years that you not use the short tag "short cut" and instead to use the full <?php and ?> tag combination. With the wide spread use of XML and use of these tags by other languages, the server can become easily confused and end up parsing the wrong code in the wrong context. But because this short cut has been a feature for such a long time, it's currently still supported for backwards compatibility, but we recommend you don't use them."

Due to the number of pages using these codes, I opted to update php.ini to allow Short Open Tags, but I fully endorse the above recommendation to use <?php in any new or updated code.

Finding the cause and implementing the solution was a simple matter, however I thought I should post a note about it here as the curly bracket comments could confuse some users, and hopefully this will help somebody. The default PHP setting for this directive is "On", (although XAMPP have disabled it in their install), so Short Open Tags will be supported unless your php.ini contains the following line - I'll let you decide if that's good or bad.

short_open_tag = Off

Short rant about quotes

It seems to me that a high percentage of PHP programmers are making a simple mistake in their code that increases the execution time of their scripts. I certainly seem to be frequently fixing the problem in other people's code, whether I am maintaining a website, or using an open source class or plugin, etc.

To some, the following two statements might appear to be functionally identical, but in the second statement PHP has to parse the double-quoted content and check for any variables to evaluate before outputting it.

<?php

echo 'Hello World, so long and thanks for all the ghoti!';
echo "Hello World, so long and thanks for all the ghoti!";

?>


If you're thinking, "So what? It's just one line..." then you are not thinking like a programmer. "Expect the unexpected" is one of the first rules of defensive programming. Imagine using this statement in a function or method that is executed many times in one script, or if it's called a few hundred times from within a loop, and maybe on a page which suddenly gets ten times the traffic you expected. All those extra CPU cycles quickly start to add up.

Now, think about all the times that static text is used inside your scripts outside of echo statements, because they will be parsed in exactly the same way. It's easy to imagine how this can start making a difference to the resources your site uses.

In a nutshell, if you're not evaluating anything in the string, use single quotes.

NB: ghoti = fish.

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 :)