My new favourite SQL function – SUBSTRING_INDEX

So I needed a quick way to convert a list of authors from “Joe A. Bloggs” into “Bloggs, Joe A.” –  i.e, not separated out into firstname and lastname fields, just one field called ‘name’. I then needed another field to return a single column with the first letter of the lastname for display purposes.

Turns out this magically easy in mySQL.

SUBSTRING_INDEX “returns the substring from the given string before a specified number of occurrences of a delimiter.” So in our example, “Joe A. Bloggs”, I want to return the last string delimited by a space, chuck in a comma, then put the first part of the name after. The reason I want to do the last part of the string (and not the first) is that as a generalisation, names could be in the format “firstname lastname”, “firstname initial. lastname”, or “firstname, initial1. initial2. doublebarrel-lastname” (in my particular circumstance anyway).

The resultant query then ends up looking like this:

SELECT
authors.id,
authors.name,
CONCAT(SUBSTRING_INDEX(TRIM(authors.name),' ',-1), ', ', SUBSTRING_INDEX(TRIM(authors.name),' ', 1)) AS reversedName,
LEFT(SUBSTRING_INDEX(TRIM(authors.name),' ',-1), 1) AS firstLetter
FROM authors
GROUP BY authors.id
ORDER BY SUBSTRING_INDEX(TRIM(authors.name),' ',-1) ASC;

Neat!

Scaling your hosting environment with free services

There comes a point in life when your current hosting environment needs to scale up, and quickly. It might just be that one of your clients gets very popular very quickly, or the gradual adding of sites and resources starts to make services fail. Thankfully there are a few tricks to make life a little bit easier. The less services you can run on your VPS, the more of that precious RAM can get put back into mySQL/Apache etc.

Free Domain based email (https://domains.live.com)
By ‘Domain based email’ I’m referring to sending mail as me@mydomain.com – and not having your webserver or mail client send it ‘on behalf of’, which I think doesn’t look massively professional (and indeed, is a lot more likely to set off the spam filters). Up until a year or two ago, I’d use Google Apps – annoyingly they’ve cut off their free tier; so now the only really free alternative is outlook.com – see https://domains.live.com/signup/signupdomain.aspx – you can do IMAP mail too, so it’s a very useful service. Saves you running your own mail service!

Free Transactional email with Mandrill (http://mandrill.com/
Speaking of sending email via your webserver – ever had mail from your webserver go into people’s junk mail? Ever had your server blacklisted for sending too much mail? Or just been rate limited by Google et al? These are fairly common complaints when running your own VPS: I’ve recently setup Mandrill to deal with all my server-side mail – so far I must say I’m rather impressed. There’s two main ways to do this – either setup your app itself (be it WordPress/Drupal etc) or make your server’s MTA (such as sendmail/postfix) send via the Mandrill SMTP service (or indeed via the Mandrill API directly from your code). Mandrill is free for the first 12,000 mails per month, which will probably be enough for most people I’d wager.

Free Email Troubleshooting (http://mxtoolbox.com/)
This is a stupidly useful tool for checking all things email related, actually, domain related too – try the domain health tool, should hopefully flag up any issues (although some of the warnings are a little strict).

Free AntiSpam service (http://mollom.com/)
It’s not flawless, but if you run a low traffic blog (sub 50 legit posts a day) it’s better than nothing. Has the requisite Drupal/Wordpress modules too.

Free CDN and DNS from CloudFlare (www.cloudflare.com)
This is a bit of a no brainer if you’re not using SSL – change your nameservers over to cloudflare, and you can take advantage of some basic threat management, and a free automatic CDN for static assets (not HTML). Just remember to install the apache/wordpress/drupal plugin to get the referring IP address back from CloudFlare – this is a reverse proxy, so if you don’t understand the implications, do a bit of reading first. You get some (very) basic analytics too, which leads me to..

Free webstats via Google Analytics (www.google.com/analytics)
OK, so Google Analytics has been around for ever, and although Google probably uses your data for everything, it’s still a very useful service indeed. Stats, stats and more stats. Saves you running a serverside stats package (unless of course, you need to track things like direct access PDF downloads – although there are some tricks via URL rewriting which can get around that).

Free Web page testing (http://www.webpagetest.org/)
This is really useful when you’ve setup a site on cloudflare, and you want to test the reverse proxy – but just generally, it’s one of the best site insight tools I’ve seen in terms of loading of assets, working out where they’re coming from etc.

So it’s amazing what you can get for nothing these days: naturally, all these services have their limitations – i.e, 12k emails a day from Mandrill, no SSL for the free version of CloudFlare, etc, but for a lot of websites out there this won’t be an issue.