Monday, June 16, 2008

A New Home!

It's always been my ambition to get my various blogs integrated properly with their relevent website. And whilst I've enjoyed being hosted with Blogger for the past couple of years, now I've finally managed to get everything set up properly on my personal website.

The main site lives at http://www.cazmockett.com/ and you can now find this blog migrated to the site too, with a properly integrated version of WordPress and a lovely new style to match the rest of the site.

I shall be leaving this site here as a permanent record, but all new posts will now appear at the other site. I hope you follow me there!

Wednesday, January 30, 2008

Some Browser Share Analysis of My Blogs

As web designers, we all know how important it is that you are aware of your target audience, and what sort of browser they might be using to view your sites.

I was idly fiddling with the Sitemeter Stats for my blogs today, and was intrigued by the variation in browser share between them - largely reflecting their readerships, and how "geeky"/computer literate the visitors may be. They're all hosted on Blogger and have referrals from a variety of sources.

First up, this blog shows the largest number of different browers - even getting a few percent of views with Konqueror and Opera 9. I see 2% of folks are still straggling along with Netscape 5 too! The majority are on Firefox 2 - just edging IE7 by 6%. I guess this wide spread of browsers reflects the "geekery" of the content and people using niche browsers are likely to read webhead stuff!

[Browser Share pie chart for this blog readership]

The second chart is my Rugby Mad blog - the first one I started back in Feb 2006. Although the subject is just limited to rugby, I'm guessing that the readers represent a more "average" web user - the blog was linked from the BBC's Six Nations blog last year, and I got massive numbers of hits from that. They are certainly a less geeky crowd than above. This is reflected by the stats - nearly half of them are using IE7 - with IE6 the next largest chunk at 28% <sigh />. Firefox has plummeted 20% compared to the geeky blog. And it looks like around 8% read from a Mac (although I suppose some could be using Safari on a PC now). But no Netscape in sight!

[Browser Share pie chart for my RugbyMad blog readership]

My Photographic blog is most similar to the web design one - although there aren't any die-hard Konqueror or Opera fans amongst the readership! The Netscape stragglers are back in about the same numbers :-)

[Browser Share pie chart for my Photographic blog readership]

Last of all is my newest blog, My Year In Pictures. It's been running less than a month, whereas the rest have been going for a year or more. I guess potential users of older browsers may have upgraded before this one went live (I think the stats are derived from the last 12 months if the blog has been going that long). The most surprising is Firefox 2 with a whopping 42% share, a good 8% ahead of IE7. There's still around 18% of users clinging to IE6. Safari and Netscape figure in the few percent.

[Browser Share pie chart for my Year In Pictures blog readership]

So, what does all this tell me? It just shows that with the proliferation of new browsers, while Firefox is doing well in the geeksphere, IE7 is gaining ground - but IE6 is still alive and kicking (us) amongst the "average" web user. And yes, there are still some poor folks using Netscape - people, if it's within your power, upgrade to a nice shiny new browser!

When I build sites for clients, I'll always design it for Firefox. Then test/fix for IE7 (some niggles but not major problems) and pesky IE6 (usually requires more tweaks). I'll also have a look at them in Safari (PC) and Opera 9.02 - there may be slight presentational differences, but no show-stoppers.

For a laugh, I'll also take a peek in IE5.5 (and 5.02 if I'm feeling masochistic), but I'm not going to waste any time fixing bugs for them. Let's face it - none of the above readers have registered as using them - and I'm guessing on average, these stats are pretty applicable for most web users these days, no matter what content they are browsing - so why should I flog myself unneccessarily?

Wednesday, December 05, 2007

Lifestream, Yahoo! Pipes and jQuery

Jeremy Keith's lifestream was the first I saw online, where he'd aggregated together his many RSS feeds into one place. It shows the various sources ordered by time, and it can be quite interesting to follow along and see what sort of sources are most prevalent at any one time. I'd been meaning to have a play with something similar myself for some time, but wasn't sure how to go about it. Then a couple of weekends ago, I went to BarCampLondon3 and Cristiano Betta did a great session on how to author your lifestream using Yahoo! Pipes. There's bags more information about how to do it at his blog. Finally, I thought it was about time I had a play with it myself. The feeds I used were the following:

I decided against using my Last.fm feed for the moment, as it would probably swamp all the other inputs (I listen to a lot of music while working, it's one of the perks of working from home, no-one moans about your choice of songs!).

Having got the basic lifestream out of Pipes, I used Dreamweaver's XSLT transform functionality to generate a list which would sit in my ASP.NET page. I wanted to use specific icons to show which feed each item was from. Cristiano was using a JSON output for his pipe, but mine is coming out as RSS, so the custom ID's applied in the pipe weren't getting through, since they're not part of the RSS spec.

CSS3 attribute selectors are one option, but they don't work in clunky browsers like IE6. So having also been at Simon Willison's jQuery session at BarCampLondon3, I thought I'd have a play with with the library to see if I could do some neat DOM manipulation to add the correct class to each list item.

The Llifestream list is marked up thus: <ul id="lifestream">. I added some jQuery to insert the class for the list element, based on the attribute of the link it contains. That requires a parent element to be changed, based on it's child's attributes. Slightly tricky for a jQuery novice like myself. It took me a while but eventually I found the correct syntax:
$(document).ready(function() {
$("#lifestream > li > a[@href^=http://twitter]").parent("li").addClass("twitter");
});
The first line calls jQuery once the full DOM is loaded; line 2 (which looks rather horrible with text wrap, but bear with me) selects an anchor with the attribute which begins with "http://twitter", but I wanted the class added to the parent LI, so hence the chaining of jQuery functions.

Now all I needed to do was to add a case for each link type to my new lifestreamclasses.js file and add a linke to it in the header of my lifestream page, and the proper stylings can be applied. I decided to keep them in a separate JS file for ease of maintenance. Once the pipe has mashed it all together and I've written the relevant styles into the stylesheet, the resulting RSS aggregated feed looks like this.

Sunday, November 25, 2007

BarCampLondon3 - Day 2

Sunday morning saw various folks rise from the dead and gather for breakfast - another mighty catering WIN from Google's chefs - Full English if you so wished, fruit and cereals for those of a more delicate constitution.

It took a while for the brain cells to get going (copious amounts of coffee and fresh orange juice helped), so the first session I sat in on was the second of the day.

What To Teach The Next Gen Web Devs - Dan Dixon
Dan is a lecturer teaching today's university-age students about the web. His round-table discussion focussed on what sort of curriculum they should be getting, from a working designer/industry expert opinion. The whiteboard proved useful in splitting up the syllabus into 4 stages and brainstorming the essentials of what should be covered:

Universals:

  • Accessibility as best practice, not an afterthought
  • Make them aware of an international web (localisation issues)
  • Good communications skills - ability to present their projects and lead discussions
  • Need good writing skills for email, reports, blog posts
  • Give them business context for their skills
  • Point them towards the developer community
Foundation:
  • Not too much emphasis on specific tools - teach basics of HTML/CSS
  • Fundamentals of design
  • Typography
  • HCI and UX design
  • The ideas of the web - a bit of history about the technology?
  • Empowerment
  • What is the web for? - show them it's not just Facebook
  • Different ways of working
  • Tell them about realistic career paths
  • Basics of how to program
Middle:
  • Practice-based
  • CSS, JavaScript
  • PHP/Ruby
  • Wireframes/IA/Sitemaps
  • Do usability testing
  • Make sure something they have worked on is torn apart (might sound harsh, but it's going to happen sooner or later!)
  • Small groups/projects
  • Understand project management skills
Internship:
  • Need good HTML/CSS skills so they are immediately useful - don't want to end up just making the tea
  • Need to work as part of a team
  • Know about browser testing
  • How to interact with clients - people skills
  • Innovation
  • Time estimation
  • Should be able to choose a path - Front End or Design or Programming or UX/IA and be able to gain relevent experience in that
End:
  • More on localisation
  • Project Manage a 2nd-year team
  • Start a project from scratch
  • More about business and how it works
It was a very thought-provoking session, and a few of us continued in well into the break. Then coffee called, so I ended up missing another session! Oh well.

Self Publishing - Vicky Lamburn
Vicky has self-published several fiction books and gave us some tips on the tools she uses for writing and typesetting (Word on Window), Lyx (for Ubuntu). Then she gave us a quick tour round the Lulu self publishing site. It is possible to get a book with ISBN - or self-promote, distribute, sell via web etc. In general, covers need to be 300dpi TIFF while text is usually send as a PDF (fonts only embedded where licencing is not an issue).

Then it was time for lunch! Still more food...

BarCamp Rhine - Sebastian "CB" Grünwaldt
CB gave a great presentation on the proposals for BarCampRhine, which basically involves BarCamp on a ship sailing from Basel (Switzerland) to Rotterdam (Netherlands), with static BarCamps in cities along the way, such as Basel, Karlsrühe, Mannheim, Köln, Strasbourg, and Rotterdam. The idea was originally suggested by Frenchman Sacha Lemaire and has been presented at various BarCamps in Europe since then.

[CB explaining the BarCampRhine idea to those in the room and in the chatroom]

If it goes ahead, it sounds like it will be a brilliant fortnight - but it needs lots of work and enthusiasm to make it happen - so if you are interested, go and sign up at the Wiki and let the other folks know you want to help. CB's talk led on nicely to Ryan Alexander's which followed:

Future of BarCamps - Ryan Alexander
Ryan's session involved asking two recent BarCamp organisers up on stage and asking some important questions about how much work was involved with putting on a BarCamp. Ian Forrester (London) and Oliver Berger (German BarCamps) kindly shared their experiences with us:

[Question Time]

Q. How long have you spent working on BarCamp?
A. Ian - Backstage are a sponsor, some time can be claimed from work time - at least a week's time.

Q. How much personal risk did you need to take?
A. Ian - does not put himself at risk, sponsors take the can. Oliver, some risk.

Q. How many others helped out?
A. Oliver 12-15. Ian 2 Googles.

Q. How many others would think about organising an event
A. Most people in room. Alistair organising one in Tyneside. Previous experience to give it a go.

Q. Norm - what would you do for a first step?
A. Get people to help - people who are passionate about it.

Q. How would you find those people?
A. Don't know (Norm). Ian - says its a lot easier to go it along to begin with. Oliver - don't need to look for passionate people - they will ask you if they can help.

Ryan's suggested BarCamp2 - a BarCamp about organising a BarCamp. This seemed to go down well, and hopefully something concrete will begin to take shape soon.

[So meta, it hurts]

Anatomy of a Business Card - Ross Bruniges
Ross's lighthearted look at the power of a business card can be summarised as follows:
  • Keep in touch with the people you meet up with.
  • Once you have a name you can see where people are going - Flickr, etc
  • Twitter - finding out what people are doing now.
  • Upcoming - what's going to happen
He ended with a favourite photo of himself:

[Ross and his Pimp goblet]

Then we were all encouraged to exchange cards with folks in the room who didn't already have ours.

Rise & Inevitable Fall of Pub Standards - Dan Webb
In the beginning... Dan took us on a little historical tour on how PubStandards formed, why it's good and why you shoulnd't miss the next one:

[Who needs the conference?]

As the sessions came to a close, everyone reconvened in the main canteen for the farewell closing speeches. I think everyone agreed it was a spectacularly successful BarCamp, certainly the best I've been to.

The Highs? - brilliant wifi, food to die for, lots of geek toys to play with and plenty of friends old and new to hang out with, winning at Werewolf!

The lows? - not getting enough sleep, not wanting to leave! Roll on BarCampLondon4...

BarCampLondon3 - After Hours

If you're staying overnight (which is essential for the full BarCamp experience), then there isn't really an "after hours" - you just keep going for as long as there's a geek still standing.

After the first day's sessions came to a close, the socialising began in earnest. Here we see Mr Boozeniges living up to his name:

[First steps in drinking the mighty Google dry]

Inevitably, where geeks gather, there will be interminable rounds of Werewolf, for it is writ in Lore.

[Short break in a round of Werewolf. Cheer up! Anyone would think someone just died... oh wait!]

I think I must have played at least half a dozen games during the evening. But the most satisfying has to be the one that wrapped up around 5am - final round me [Werewolf] versus Tom Hughes-Croucher and Sebastian "CB" Grünwaldt [villagers] and Tom decides to nominate CB - mwahhaha! I win! Yum yum, tasty villagers.

Talking of tasty, as if the mountains of food served for dinner weren't enough, Google laid on a midnight feast for the geeks - freshly cooked waffles, pancakes and a chocolate fountain. Man, you could get soooo fat working here...

[The chocolate fountain - just had to be tried, didn't it?]

There were loads of games to play (Wii sports, tabletennis, fussball) and even a Segway to fool around with. However, Jan and CB, two of the crazy German LondonBubble guys decided they could go one better than the Segway with their two-seater "find":

[Ticket To Ride - CB drives with Jan on the back, looking justifyably worried]

But the weirdest trick of the evning must go to Oliver Uuberholz (another LondonBubble boy) who decided the empty beer fridges were going to waste and his Mac was getting too warm:
[Macs in the fridge]

Enough craziness, and being about dead on my feet by 5:30am, I went to find a quiet bit of floor to collapse on for 3 hours.

Saturday, November 24, 2007

BarCampLondon3 - Day 1

The time for another BarCampLondon3 has rolled around, and I was lucky enough to get a ticket. We all turned up at Google's swanky offices in Victoria knowing we would have a good time, but not quite realising what a great time we were in for.

The organisation went very smoothly, the wifi was rock solid, there was more food, beer and snacks than even a BarCamp-load of geeks could consume (well, apart from the beer - it's the first time Google's fridges have been emptied, oops!)

As usual with an unconference, it was all about the sessions folks decided to give, and we were treated to some really thought-provoking and fun discussions. It was a shame that out of the 100 attendees, about 30 chose not to present. So the schedule was a little light at times, but that's not always a bad thing - nice to catch your breath every now and then! Jeremy marked up the timetable for us all to refer to easily.

The first session I went to was Tom Morris - Scraping Sucks - where he was giving us more usable alternatives to scraping HTML, namely doing clever stuff with GRDDL. He says it's much easier that way. As usual, I nodded sagely at the time, and then a couple of hours later, wondered what it was all about. Tom is a great geek, but he's several steps ahead of me when it comes to brain-wracking abilities :-) He's put up a page of GRDDL Profiles here - which lets you look at (X)HTML and with an XSLT transform, spits out XML/RDF which can be used as you want.
[Tom gets stuck in to his presentation]

Norm's Law
This was an excellent presentation from Mark Norman Francis. He gave us some very good reasons for doing code his way - especially for fostering interoperability betweeen different members of the team, or yourself coming back to code at a later date. Some points included:

  • Use spaces not tabs
  • Code goes no further than col 77
  • No-one ever died from using too much whitespace
  • Separate operators and braces - more of a cognitive burden to parse squashed up code
  • Always indent by 4 spaces ONLY
  • Line up assignments of variables (equals sign in the same place etc)
  • Line up data tables too (arrays or whatever)
  • Space keys out from brackets $vote[ $value }++; etc
  • Space keywords out not functions
  • Vertical rhythm - break bits up with comments for each sub part - make a story out of it
  • Respect left-to-right comprehension
  • K&R bracketing - opening brace should be tied to RHS end of line, closing brace should be on a new line - aligned with the starting coment
  • Don't cuddle and else!
  • One statement per line - you can easily miss the ";" in the middle of the line separating the two commands
  • Break lines before operators - EXCEPT in JavaScript or it won't work
  • Ignore operator precidence - use brackets to make it more "English readable"
  • Use single quotes where possible - ' in PHP will just be stuffed in, " will make PHP parse the contents looking for variables
  • Factor out long expressions and use intermediate variables (with english-sensible names) to break up
  • Always use x on regexpressions
  • Don't use camelCase! unless you're in JavaScript
  • Systems Hungarian is harmful, Apps Hungarian is too
  • All short variable names are harmful
  • Use grammatical variable names and function calls
  • Optimise for humans first! Machines - throw more hardware at it - but you can't refactor comprehension
  • HTML indents use 2 spaces not 4
  • Write the whole document FIRST before you do any CSS etc
  • Insert Microformat classes
  • Always use single quotes in attributes
  • Inline CSS means you've done it wrong
  • If it only works in JS don't
  • VALIDATE
  • Start with base stylesheets - reset, fonts, layout
  • Use Uppercase tags in HTML
  • Keep z-index below 50
[Norm - I can haz 4 skreenz]

Next up was a session about new developments from the BBC's web team:

BBC APIs First Look
PIPs is the system to list all broadcasted stuff - telly and radio
  • bbc.co.uk/programmes
  • Gives a list of all current programmes - by genre or alphabetically
  • Nice URLs which can have .yaml or .json can be added for the feed in that format
  • bbc.co.uk/programmes/formats
  • Pid is the 8-character id for each episode - taken from user experience tests and will always be constant (never change)
  • JSON and YAML are the two formats currently supported - XML coming? - RDF ontology has been produced
  • RSS feed is coming so you could subscribe to know when "every episode of Doctor Who" is on
  • Data model - "programme" can be brand, series or episode - an episode can have multiple versions (signed, extended etc) which then have broadcast (tv) or ondemand (iPlayer) times
  • Historical data back to May 2006
  • Can filter out to network (tv or radio) eg Radio4
  • Next release (API stuff) in New Year
  • http://catalogue.bbc.co.uk/ - is historical data - grand plan is to have them merged
DIY User Research - Leisa Reichelt
Leisa gave us lots of good advice on how to carry out some DIY user research - her premise being that it doesn't have to take days and days and cost big bucks - and often talking to more than half a dozen victims volunteers gives you diminishing returns. Leisa's slides are already available at the Slideshare BarCampLondon3 group.

Building Lifestream with Yahoo! Pipes - Cristiano Betta
I didn't take many notes as I was listening as I was actually playing with a real Yahoo pipe of my own and trying to follow along with what Cristiano had to say. I've been meaning to use Pipes to create my own Lifestream for some time, but had a quick go before and things weren't coming out as I wanted. Cristiano has done a series of excellent blog posts to get you going, or you can watch Tom Morris' video of Cristiano's presentation. Or view Cristiano's own Lifestream.

10 Things You Should Do In Project Management But Probably Don't - Gareth Rushgrove
Gareth's top ten tips:
  1. Use Source Control software
  2. Validate markup - XML, RSS, Atom and JSON
  3. CSS validation
  4. Broken Links! check them thoroughly - W3C Link checker
  5. Performance - do you have metrics for measuring the performance - YSlow is a Firebug enhancement, httperf - use uptime checker too such as Pingdom
  6. Maintainable Javascript - JSLint gives you good tips
  7. Carry out Unit Testing
  8. Carry out Functional tests
  9. Asset Compilation
  10. Building Scripts
More at morethanseven.net

Learning jQuery - Simon Willison
Simon gave us a lightining half hour tour of the jQuery Javascript library with great examples and succinct slides - you can get them from Slideshare. I've been meaning to beef up my JavaScript skills, and getting to grips with jQuery sounds like a good place to start.

[Simon talks about jQuery's Ajax capabilities]

Ask Them Anything
For the final sessions of day one, Norm and friends held an Ask Us Anything panel - just a bit of silliness to round off the proceedings before dinner. The guys from the Londonbubble did a live stream of the session to their mogulus chatroom, and it all got a bit recursive when this was put up on the main screen behind the guys:

[Behind you!]

The chatroom folks even got to ask a question or two - and Ross got a marriage proposal from a lady named Picki which he had to graciously decline!

[Ross, Norm and Ryan answer the online questions]

And so to dinner... but that's for another post.

Thursday, October 04, 2007

Subquery Problems in mySQL

I'm having trouble selecting data for a web-based application. There are three tables, Articles, Keywords and KeywordsInArticle. There is a many-to-many relationship between the articles and keywords, which is handled by the piggy-in-the-middle KeywordsInArticle table. So, if we have the following scenario:

Articles
articleID  articleName
1             First Article
2             Second Article
3             Third Article
4             Fourth Article

Keywords
keywdID  keyWord
1             apples
2             oranges
3             bananas
4             grapes

KeywordsInArticle
articleID  keywdID
1             1
1             3
2             1
2             2 etc

We know that "First Article" is all about apples and bananas while "Second Article" is about apples and oranges etc. And for article #1, I can pull out a list of the relevent keywords by using the following SQL statement:

SELECT keywordsinarticle.articleID, keywordsinarticle.keywdID, keywords.keyWord
FROM keywords, keywordsinarticle
WHERE ((keywordsinarticle.keywdID=keywords.keywdID) AND (keywordsinarticle.articleID=1))
This would give the following result:
articleID   keywdID   keyWord
1             1              apples
1             3              bananas
So far, straighthforward. However, in the Content Management side of the application, there needs to be a way of managing the keywords associated with each article, to update the KeywordsInArticle table. However, this has a compound key value made up from the articleID and the keywdID. It cannot contain duplicate rows, so when adding a keyword entry to the list, I want to be able to select all keywords not already associated with that article. In the example, I need to select only "oranges" and "grapes" from the keywords table, to give them as options to add.

I want to be able to invert my selection, so I tried a using the above as a subquery and negating the result:
SELECT *
FROM keywords
WHERE keywdID NOT IN (SELECT keywordsinarticle.articleID, keywordsinarticle.keywdID, keywords.keyWord
FROM keywords, keywordsinarticle
WHERE ((keywordsinarticle.keywdID=keywords.keywdID) AND (keywordsinarticle.articleID=1)))
Sadly, mySQL throws a wobbler at this, telling me:
Message = "Operand should contain 1 column(s)"
Looking up this error at the mySQL website directed me towards the Row Subqueries page, but I'm not convinced this fits my problem either. So now I'm rather stuck. If anyone has any ideas, I'd love to hear them!

Wednesday, August 29, 2007

A Stitch In Time

I've got a probem with date sorting.

I have a PHP/mySQL application which requires sorting a dataset by year.

As mySQL won't let you store just the year, I originally used a varchar (text) field to store the year, YYYY, which sorts nicely.

However, I now have a client who wants to be more specific with some dates only, ie they want to be able to store YYYY, YYYY/MM or YYYY/MM/DD in the same field and still have it sort nicely.

OK, so I can force them to input the full date (when required) in the YYYY/MM/DD format, but that looks really ugly when output to the web page as a human-readable date.

So my problem is this:

  • If I want human-readable dates, like 1 January 2007, and go for a text field, they won't sort properly as 1 April 2007 comes before 1 January 2007 in that case.
  • If I opt for the date field, it would sort properly but I can't store dates which are just month/year or year only, as mySQL throws a wobbler and protests when you don't have the full date input.
Storing the "date" as a string: 2007/08/01 (the pubDate) sorts nicely, and I had hoped using the following PHP:
<?php echo date('j F Y', strtotime($row_rsByDate['pubDate'])); ?>
would solve the problem, since it gives a nice date 1 August 2007. But there are still issues with just storing part of the date - see screenshot below:

The entries are sorted correctly (most specific at the top, descending order). The first date (red) is the nice output of the PHP function - the second (puce green) is the actual stored text.

It falls over horribly when only the YYYY/MM is stored (returning 1st January 1970) or YYYY (returning today's date).

EUREKA!

After much discussion (via the comments on this post and my buddies on Twitter) , I was almost ready to explode with frustration at not being able to find a solution. Turns out, that's exactly what I needed to do!

James Aylett suggested I use the PHP explode() and implode() functions to write a custom parser. I didn't actually need the implode() half as it happens. Being a relative newbie to PHP, I'd never heard of the explode function, so thanks for the pointer, James!

So, to recap. The database column should be stored as TEXT or VARCHAR(10) not DATETIME. That way, all variations of YYYY, YYYY/MM or YYYY/MM/DD are acceptable, and will sort correctly. This will look horrible if put straight into the page, so use something like the following to present it nicely to the web page:

<?php $split = explode("/",$row_rsByDate['pubDate'],3);
print_r($split[2]);
echo " ";
if ($split[1]==1) print_r("Jan");
elseif ($split[1]==2) print_r("Feb");
elseif ($split[1]==3) print_r("Mar");
elseif ($split[1]==4) print_r("Apr");
elseif ($split[1]==5) print_r("May");
elseif ($split[1]==6) print_r("Jun");
elseif ($split[1]==7) print_r("Jul");
elseif ($split[1]==8) print_r("Aug");
elseif ($split[1]==9) print_r("Sep");
elseif ($split[1]==10) print_r("Oct");
elseif ($split[1]==11) print_r("Nov");
elseif ($split[1]==12) print_r("Dec");
echo " ";
print_r($split[0]);
?>

explode() - separates the string found in pubDate into its component parts (the separator is set by the first parameter, "/"), giving the $split array. This containts up to 3 elements, depending on how specific the stored "date" is.
$split[2] is the DD day element and is printed directly.
$split[0] is the YYYY year element and can be printed directly.
$split[1] is the MM month element, which requires the pretty formatting. So a match against each month number prints out a more human-readable month. Voilà!

Another screenshot - this time the green output is the unprocessed pubDate text field, and the preceding red date is the bespoke-parsed version - allowing for all cases of "date" format.

Some caveats:
I wouldn't normally recommend spoofing the date like this - if it's a real date (specified to day, month, year for every record) then use the DATETIME type in your database - this will sort and you can perform arithmetic on it quite readily (eg next month, last week etc).

I only went through this pain because of the special case I was finding - the publication "dates" of newspaper articles (day, month, year), journals (month, year) and books (year only) which my data required. Using this solution lets it all sort properly and look nice to viewers. Win!