Before the weekend I had 100-150 or so, very bored Twitter followers. I now have a small army currently 666 (eek!) strong, all equally unimpressed, and all thanks to a very kind chap called David Barker and his new twocation.com twitter app.
David uses my ExCanvas World Map code to enhance his site and has very kindly given a prominent shout out to me on the main page of his site.
Thanks for the new followers, David! I'm glad you found the code useful and good luck with your thang!
I came across a little SQL problem today that supplied a small 'Eureka!' moment, for me at least. I'm sure you hardcore DBA types will mock my ignorance, but hey, we weren't born knowing this stuff. I'm using MySQL here, but the priniciple here is easily translated to your DBMS of choice.
What I am trying to do is find events, or the number of events, that happen on a particular day. This should be easy, right?
Find the number of events happening per day:
SELECT DATE(start_date_time) AS dt, /* retrieve the date */ COUNT(*) AS freq /* and # of events for that date */ FROM events /* from our events list */ GROUP BY dt /* aggregate results by calendar days */ ORDER BY dt /* and output in chronological order */
Find details of all the events happening today:
SELECT * /* retrieve everything */ FROM events /* from our events list */ WHERE DATE(start_date_time) = '2011-08-08' /* where the event starts today */ ORDER BY start_date_time /* and output in chronological order */
But events - and I'm talking in particular about nightlife here - can happen post-midnight and still be thought of (by the drunken masses) as the previous day. To clarify, a DJ, band or late night cinema showing kicking off at 1am on Saturday morning, is still considered by the party-goer as Friday night, right? What I need to do is get the events starting in the wee hours included in yesterday's results.
So how to do the above queries now? My Google-fu failed me; what on earth do you type in to solve this one? Thankfully, after envisaging all sorts of fugly sub-queries and JOINs, the answer was really very easy and blindingly obvious when you think about it. All we have to do is shift the timestamps backwards by an arbitary number, say four hours (4am is the new midnight!), and we can keep the same basic (read: fast) queries we had earlier.
Number of events (partygoer-friendly version):
SELECT DATE(DATE_SUB(start_date_time, INTERVAL 4 HOUR)) AS dt, COUNT(*) AS freq FROM events GROUP BY dt ORDER BY dt /* subtract four hours, so any event starting before 4am is counted as happening on the previous day */
Today's events (partygoer-friendly version):
SELECT * FROM events WHERE DATE(DATE_SUB(start_date_time, INTERVAL 4 HOUR)) = '2011-08-08' ORDER BY start_date_time
Unless anyone knows a better way?
Last night I was lucky enough to attend Google's first London Site Clinic at TechHub. This, as the name suggests, was a free-of-charge chance for webmasters to hear from the horse's mouth the do's and don'ts of increasing your website's visibility in Google Search.
After a brief run through of basic SEO, e.g. the importance of good anchor text,
<title> and description meta tags, human-readable image filenames and use of the alt tag, etc., we were given an overview and brief tour of Google Webmaster Tools. While informative, much of this was quite basic and seemed aimed a little below the average attendee; indeed I don't think those running the show were quite expecting everyone to raise their hand when asked the question, "who here uses our Webmaster Tools?".
By far the most interesting part of the evening was the Google Search Quality team's appraisals of sites owned by members of the audience. It was eye-opening to see how many easily avoidable mistakes were being made by relatively established sites. It was a little disappointing that so few of these webmasters failed to show up as some excellent questions could have come out of this - come on guys, it was free!
The key points that I took away from Google's Search Quality team were:
<h6>. A handy tip here was to Google Search for cache:yourdomain.com, then click the text-only version link in the top-right corner see non-styled page layout. Bang go my days of loading up Lynx to achieve the same result.
<title>and description meta tags on every page. This was the broken record point of the evening; the panel couldn't stress it enough. Do not duplicate these across pages on your site.
As pretty much everything covered during the evening is freely available in Google's SEO Starter Guide PDF, many of the Tweeters present found the content way too simple and were disappointed they weren't being given any SEO golden nuggets. While I too may have been frustrated if I were a hardcore SEO professional, I feel the point the team were trying to make was that these are simple tasks anyone can get right and site owners should concentrate on getting all these basics down before even thinking about delving into the "nitty gritty" details.
Yes, perhaps Google could have set greater expectation before the talk in terms of difficulty level or target audience, but the number of basic, "schoolboy errors" made by the critqued sites (plus this one you are reading now) suggest that perhaps all these advanced SEO tricks and tips are a complete waste of time and money if we don't get the foundations right first.
Most encouragingly from my point of view as someone so passionate about User Interface and User Experinece, the overall message was that if you look after your user, Google will look after you.
Finally it seems our house move is just about complete.
They say moving house and changing jobs are among the most stressful things a person can do. I can assure you that doing both at the same time - especially with a baby - is not to be sniffed at.
Just to add a little more frustration into the equation, thanks partly to moving over the Christmas and New Year holidays, we were to be without internet in our new home for over three weeks.
The often slow, semi-reliable, one-user-at-a-time 3G USB dongle I own was going to have to suffice. How were we to survive? *shudder*
That is until of course I desperately Googled the magic words "3G router" to see if such a thing even existed. The results were better than that: for a mere £20 plus VAT, the "TP-Link TL-MR3220" router which - via it's USB slot - was going to allow me to plug in my existing dongle and broadcast it's signal via wifi to all the devices in the house.
My expectations were, frankly, rock bottom low but for 20 notes, I was willing to take the plunge.
So did it work? Amazingly, yes. And it was fantastic. We were setup and broadcasting within minutes.
Seriously, if ever you find yourself in a position where you might need one of these, just get one. Where I feared I would be forced to use it solely to check for new emails, I was able to surf normally, to the point where I almost regret having a landline and "regular" broadband installed in the new place.
p.s. I bought mine from broadbandbuyer.co.uk (I'm not affiliated with them in any way). It turned up safe and sound exactly when they said it would.
There's something quite philanthropic about seeing your work put towards a good cause, especially when it is done in such an imaginitive way. Here is the christmas e-card James created on behalf of bread.org, Joy to the World.
If I don't see you before, Merry Christmas everyone.