Crazy Queries
Recently I have been really into creating these long and extreamly complicated queries.  Most of them are for displaying website statistics, but writing them has certainly helped me become a better SQL programmer.

Some of these are a lot of fun, but will give you a headache trying to figure them out.  One of the more recent queries that I wrote was to get the keyword phrases from the search engines from the site statistics list of referers.  It's quite amazing how fast MySQL will run this query.

SELECT DISTINCT(SUBSTR(referer, LOCATE('q=', referer) + 2,
IF(((LOCATE('&', referer, LOCATE('q=', referer)) -
LOCATE('q=', referer)) > 0),
(LOCATE('&', referer, LOCATE('q=', referer)) -
LOCATE('q=', referer)) - 2, '1000'))) as keywords,
count(referer) as times, referer
FROM counter
WHERE referer like('%google%')
AND referer NOT like('%translate.google%')
AND referer NOT like('%images.google%')
AND referer NOT like('%toolbar.google%')
GROUP BY keywords
ORDER BY times desc;

Confused?  If your not, then your smarter than me.  It took me the better part of an hour to figure out how to write this query.

In my website hit counter, I keep a list of website referers.  These are always fun to look at, but it's nice to have the data organized in a manner that makes some sense.  What this is doing is searching through the referer list (column in the counter table) for any hits that came from Google.  If a hit came from Google then we try and determine what the keyword phrase was that the user entered.  All google searches have q=searchterms in the URL querystring like:


So what we want to do is pull apart the referer URL and get the peice between the   q= and the next ampersand or the end of the line.

To accomplish this we use a SUBSTRING function with an IF statement to see if we're at the end of the querystring and a series of LOCATE statements to pick out the search phrase string.