If you are viewing this post in a feed reader you should be ashamed of yourself, as you are missing my new redesign. This redesign has been in need for a while as (lets face it) the old one was getting well ... old. I found that in the old design the thick black lines always made it feel really heavy. This one on the other hand feels a lot lighter and has more room for me to build upon whenever I wish to do so.
If you cast your eyes down to the bottom of the page you will notice a new footer. This has different content depending on what page you are looking at. If you are looking at the home page or the archive page you will see my complete blogroll. However, if you are looking at any other page it with have three columns with different things in: my recent post; a random selection from the blogroll and the latest links from my delicious account. This footer has enabled me to remove the "external" page. This page was in desperate need of being revamped. However, in the end I thought it would be easier to put it in the bin.
Over on the left you will notice a new "wibbly man" (or art mannequin if you prefer). I hand crafted this myself in Flash MX. It replaces the older, smaller one that was in the bottom right hand corner. Depending on how I feel I might turn it into a desktop at some point as I think it would look cool. But for now who knows...
Behind the scenes this redesign was more than just a change in style-sheet. I have restructured all of the templates. Templates should enable you to never have to repeat any code. However, the way I had it set up meant there were a large number of almost identical templates. This new system enables me to make site wide changes, by editing only one template. This is the way the templates should have been done in the first place.
Finally, I am giving you some advanced notice that posts may become infrequent over the next month as I have a large number of A-Level exams coming up. How I do in these exams will affect which university I get accepted at. So, as you can imagine they are slightly more important than this site at the moment. However, after that I will have finished school so there should be more time to put in to this site.
Slightly geek-ish side note: This is Version 5 of my site or V5 or Vv. Which, following the rules of Roman Numerals, is five before five therefore making this Version 0. Seeing as the Romans didn't have a zero this redesign doesn't exist.
In the second part of this multipart series I am going to be talking about the befits of using the GROUP BY clause that SQL has to offer. This is by no means a definitive guide to the GROUP BY clause. For that, checkout the manual.
GROUP BY
GROUP BY is a function that can come in very useful when you want to create counts of rows that share the same value in a column. When you call the function you also give it one or more column names. The results are then collected by the values in that column. For example if you wanted a COUNT of how many posts were in each section of your blog, you could run this query:
mysql> SELECT category, COUNT(*) AS total
-> FROM `blog-posts`
-> GROUP BY category;
This would then return a table with two columns. The category column and the total column which would contain a count of the number of posts in that category. This can then be taken on a stage using DISTINCT. Say you wanted to see how many different authors have posted to each category:
mysql> SELECT category, COUNT(DISTINCT author) AS total
-> FROM `blog-posts`
-> GROUP BY category;
This will return a table with a row for each category with a count of the number of different authors who post to that category. That is not much good for outputting though as you really want the authors' names in a list. So instead you could use the GROUP_CONCAT function:
mysql> SELECT category, GROUP_CONCAT(DISTINCT author
-> ORDER BY author DESC SEPARATOR ', ') AS authors
-> FROM `blog-posts`
-> GROUP BY category;
GROUP BY can also be used with multiple columns so if you want to group by both category and then year for example you could write: GROUP BY category, year. However, if you wanted it grouped by year and then by category, it would look like: GROUP BY year, category
One last trick GROUP BY has that I want to talk about is its mathematical functions. If you haven't yet been impressed by what it can do I hope this will impress you. Lets say you have a column of numbers, say comment counts for your blog posts. You want to list: the average number of comments; the maximum and minimum number of comments; the standard deviation and the total number of comments per post per category. GROUP BY has all of these covered and it can all be done in one single SQL query:
mysql> SELECT category, AVG(comments), MAX(comments), MIN(comments),
-> SUM(comments), STDDEV(comments)
-> FROM `blog-posts`
-> GROUP BY category;
Now that could make you code a lot smaller than it would have been if you didn't know these functions were already baked into SQL. If you haven't already, make sure you check out the first part to this series.
Up until recently, I have not really seen much good usage of SQL and to be more specific MySQL. Most people who use php and MySQL to create web-applications only use simple SELECT statements. There is far more that the language can do, so over the next couple of entries I will be showing you a rundown of the more useful features.
A DATETIME Field
Lots of web software I have downloaded has used raw unix time-stamps in a SQL field. This is wrong on so many levels and really restricts what you can do with the data. The SQL DATETIME is probably one of the most useful fields and there can be some real benefits if it is used properly.
However, if you really want to keep everything in your code using unix time-stamps then you can, as SQL will transform everything for you. For converting time-stamps to DATETIME or from DATETIME to time-stamp you can use the FROM_UNIXTIME or UNIX_TIMESTAMP respectively.
mysql> SELECT FROM_UNIXTIME(1111885200);
-> 2005-03-27 02:00:00
mysql> SELECT UNIX_TIMESTAMP('2005-03-27 03:00:00')
-> 1111885200
So, if you for some reason want to use unix time-stamps you can. However, if you are only getting the time out as a time-stamp so you can now feed it through your php date function. You don't have to. SQL can save a step by doing it for you. The DATE_FORMAT function enables you to turn your DATETIME in to whatever format you want:
mysql> SELECT DATE_FORMAT('2005-03-27 02:00:00','%D %M');
-> 27th March
mysql> SELECT DATE_FORMAT('2005-03-27 02:00:00','%l:%i %p');
-> 2:00 AM
The final function I want to talk about is the DATE_SUB function. This function enables you to take away an interval of time from any date which is inputted. This means you don't have to mess around finding time-stamps for 15 days ago. That is all taken care for you:
mysql> SELECT DATE_SUB('2006-03-20',INTERVAL 1 MONTH);
-> 2006-02-20
mysql> SELECT DATE_SUB('2006-03-05 02:40:00',INTERVAL 15 DAY);
-> 2006-02-18 02:40:00
Of course the real power that the field holds is in its ability to be used in a conditional statement. It can make finding recent data really simple and it doesn't require you to get dirty working out time-stamps. The following is fuller query showing how you can return data suitable for simple outputting onto the front page of your blog:
SELECT title, content, DATE_FORMAT(time,'%l:%i %p - %D %M') AS posted
FROM `blog-posts`
WHERE DATE_SUB(CURDATE(),INTERVAL 7 DAY) <= time
ORDER BY time DESC
The only function here which I have not mentioned is the CURDATE() function. This basically returns the current date in a DATETIME string. The output from this query can then be pasted straight in to your html templates.
While this was only a brief overview of some of the date and time functions available, I hope I have shown you the power that they hold.