Substring magic with mysql

Mysql is a wonderful database, and while many use it, most people only scratch the surface of what the database can do. One of the practical functions available is the substring_index function, and an imaginary mailing list example is a nice way to show how to use it.

Let imagine we have a mailinglist in a table named “mailinglist” and it has a (char) column with the email addresses subscribed to the list. We now want to figure out how many users, that are subscribed from the various domains in the list.

Finding the domain name from an email address is quite simple – just find the @ sign – anything past that, will be the domain name and substring_index will do just that. To create our list of domains with the number of subscribers, we simply issue this query:

SELECT SUBSTRING_INDEX(email, ‘@’, -1) AS domain, count(*) as subscribed
FROM mailinglist
GROUP BY domain
ORDER BY subscribed;

Some email providers may use 3rd level domains (sales.example.com, tech.example.com, etc). What it we want to summarize the subscribers on the second level (example.com). No worries – substring_index will help us with that too. The query to do that looks like this:

SELECT  SUBSTRING_INDEX(SUBSTRING_INDEX(email, ‘.’, -2),’@’,-1) AS domain,
count(SUBSTRING_INDEX(SUBSTRING_INDEX(email, ‘.’, -2),’@’,-1)) AS subscribed
FROM  mailinglist
GROUP BY domain
ORDER BY subscribed;

While most developers may master simple queries in SQL, most databases have a library of functions – like substring_index – available and must too often they are ignored and hardly used at all.

If you want to be a better developer, learn to use the entire toolbox available – not just what you know already in Perl, in PHP or what ever you use to do your programming.

MacBook Air – fair criticism?

Earlier this week Apple introduced a new line in their laptop lineup – the MacBook Air. On the web there’s been a number of people criticizing the MacBook Air, and frankly I really don’t get why they need to have every Apple product be a perfect match for them – they aren’t and they’re not supposed to be.

“Top 10 Flaws of the Macbook Air”

Let me add a few comments to some of the points on one of the lists criticizing the Macbook Air:

  1. Battery not user Replaceable
    No it isn’t, but how often do you need that? I’ve never had to replace a battery dispite using laptops for 10+ years. Sure you should be able to, but you can’t – so be it.
  2. No Optical Drive
    Several laptops from other vendors doesn’t have a built in optical drives. It’s not a flaw it’s a feature. Use the external if/when you need it.
  3. Mono Speakers
    Who uses the build-in speakers? I always either using headphones or external speakers. When I do use the build-in speakers it doesn’t matters it’s mono.
  4. Fixed RAM and Slow Processor
    Sure replaceable/upgradeable RAM would be nice, but for most common uses 2 Gb RAM should be sufficient for the expected lifetime of the machine.
    The Core2 Duo 1.6Ghz is probably fast enough for most average uses. It’s not for Photoshop, heavy Video editing, but it’ll run Mail.app, Safari, iWork and iLife just fine.
  5. Single Link DVI Output
    If you need to have several screens attached to the laptop (or need to power a 30” display) the Macbook Air probably isn’t for you. You’re probably a Pro user, who should look at the Macbook Pro (as the name suggest).

Why the Macbook Air might be just perfect

I’ve been recommending ShuttlePCs for friends and family for the past year, and usually that they buy a reasonable configuration, and use the machine for as long as it suits their needs – then replace it. When it seems slow, lacks hard disk space or what ever – don’t bother with upgrades, spend the money on a new machine.

I’m sure geeks find it amazing to replace memory, hard disk and other upgradable parts, but most computer users these days don’t. They think of their computer just like their car – if you aren’t a mechanic, don’t mess under the hood – you’ll probably do more damage than good.

The MacBook Air isn’t for everyone, but it’s a wonderful machine for many common computer users, and probably not for geeks (that’s why they have the MacBook Pro, remember).

I personally doesn’t give a damn, that the Air doesn’t have an optical drive – I might use the optical drive in my PowerMac twice a year, and using an external drive (or a hosted drive on an other computer) will work just fine for me, thank you.

I might have a problem with the hard disk options, but with a huge digital data collection (photos, music, video and others) I’m already looking into network drives – and with 2+ GB USB pendrives, I can easily place the data on a pendrive and bring them with me.

I really like the weight and the general form factors, and while it may not be the perfect primary computer for all, I’m sure many will be quite satisfied with it – even as their primary computer.

Footers – not just the end of the page

On most websites – including this one – the footer is the boring place where you (usually) place all the pocket lint, which didn’t make it into the page anywhere else. It usually have a copyright notice, links to a site map and other stuff which may be important, but not interesting (speaking in very broad terms).

Today I came across an article on footers with great usability. Footer Usability? Yes, and from a blunt disregard and ignorance, I’ve discovered that the site footer is an excellent place to place a lot more effort.

The footer may have been important as an “end of page marker” in many designs, but using it as an active area to promote other parts of the site, seem to be a new trend from the late part of 2007.

I really like the footer usage on farfromfearless, where especially the flickr photostream adds a bit of life to the page, and ProBlogger.net, where the categories listing might act as a miniature site map.

While using the footer could be a very smart move, don’t over do it. Bad examples on footer misuse include popsugar, where footer eats up more than 30% of the total page length, and engadget, which  seem to claim a lot of screen space – without any interesting content (just headlines from their sister sites).

Quality of code

Not  all code are created equal. Some pieces of code are more important than others. The code that powers critical parts of a nuclear power plant is hopefully of a much higher standard than the code behind this site, but how do you recognize which quality of code you should aim for when developing websites?

A website may not have one set level for all content – usually the various pieces which make up the site (no matter if it be classes, files or functions – in PHP). On the level you’re looking, let me suggest, for each piece you judge the required (minimum) quality on two simple parameters:

Business/Real world value: What’s the consequence if the code breaks? are you loosing money? or is it “just” an embarrassment”? The higher the stakes the more effort should be put into the code.

Scope of impact: How large a part of your website breaks if the code fails.

While you may not have a precise scientific way of measuring the two, I’m quite certain, that most developers have a pretty good gut feeling on both, and ought to be able to set a suitable quality level using just that.

Is it so or are there better ways to find acceptable quality levels?