Using GROUP CONCAT in MySQL
Added on: Thursday 30th July 2009
Yesterday I had the need to get a list of unique values from a MySQL database where the values were stored as a comma delimited set. There were several ways to approach this but I wanted to try to minimise the amount of processing required.
Good database design says that it would be better to store the values in a table that links the value to a record in another table - allowing multiple values for each record - but I was stuck with the current arrangement so had to make do.
The particular field in the database could contain a single value such as item1 or multiple (comma separated) values in any order such as item2,item1 or item1,item3
The first thing that occurred to me was just to retrieve the records in question and then use PHP to loop through each one, splitting them into individual values. The code would then add the value to an array if it wasn't already in there or ignore it if it was. Finally it would need to sort the array.
This only involves a single call to MySQL but there is still a lot of processing for PHP.
I then decided to make use of the GROUP_CONCAT function in MySQL as follows:
SELECT GROUP_CONCAT(items ORDER BY items SEPARATOR ',') AS items
This produces a single row with a field containing a comma separated list of all possible combinations eg. item1,item1,item3,item2,item1 from the example above.
By making use of the array_unique function in PHP there are only three lines of code required to create the unique sorted list.
$ary = explode(",", $items);
$tags = array_unique($ary);
sort($tags);
I am assuming in the above code that $items is the value of the field resulting from the MySQL query. The script then uses explode to create an array of values - array_unique removes any duplicates and the sort function ensures they are in alphabetical order.
Wild River Cold Stone
Added on: Friday 24th July 2009
I went to see a screening of this new Chris Chapman film about Dartmoor last night and it just reminded me how lucky I am to live in this part of the world.
Two years in the making the film by Dartmoor photographer Chris Chapman and film maker Kate King charts the history of Dartmoor and its people.
It features some stunning shots of the landscape and lots of local people, many of whom we know well.
If you live on Dartmoor and haven't seen it, the 'roadshow' has now ended but it will also be shown at Dartmoor National Park Authority's High Moorland Visitor Centre in Princetown from 24 July as part of an exhibition about the making of the film.
...and you can buy a copy of the film from Chris Chapman's website.
Improvements in web typography
Added on: Wednesday 15th July 2009
Up until now, if a web designer wanted to use a certain font on a website they either had to play safe and use something like Times New Roman or use JavaScript and Flash to change the fonts when a page is loaded.
With version 3.5 of Firefox it now looks like these techniques are no longer required as all the major browsers now support font linking - a method of using fonts hosted on a web server.
This means that it is no longer the fonts on the users browser that determine how a website looks. Previously if the user didn't have the specified font on their computer then it would be substituted with a standard serif or sans serif font.
Some browsers have supported font linking for some time. (Oddly Internet Explorer was one of the first albeit requiring a proprietary format). The problem though has been one of copyright.
Most of the fonts you can download from the Internet have some form of licensing restriction so that you can't just load them on to your server and link to them.
I have just been alerted to the Open Font Library which has a selection of licence free fonts that can be downloaded and used for websites. They also encourage linking to the fonts on their site.
The good thing is that it just requires a declaration in the CSS file so many CMS systems can use this technique without any changes.
One problem I mentioned earlier is that with Internet Explorer you can only link to fonts that are in their proprietary EOT format.
However, Microsoft provides a free tool to convert True Type fonts (only works on Windows systems though)
Finding the position of a record in MySQL
Added on: Saturday 4th July 2009
Normally when retrieving records from a MySQL database you loop through the results and perform an operation on each in time. The other day, however, I simply wanted to get the position of a certain record in a set - nothing more.
Many people say that the actual position in a result set is meaningless because it depends on the sort order and other factors.
To a certain extent this is true but consider this scenario:
Our CRM application allows you to scroll through groups of records and keeps track of which record you are on even if you go to other pages and then come back.
It also features a navigation bar to allow you to move forward or backward through the set.
As it uses session variables these will be cleared once you've logged out and rather than using database storage I just wanted a quick way to jump to a certain record in the set.
A dropdown list of all records in the set works fine for small sets but the overhead of loading this is unacceptable for very large sets.
Instead I wanted users to be able to find a record in the database and then be able to jump to scroll mode at the position of that record in the set.
I could have simply pulled all the IDs from the set and then looped through them in php until a match is found for the current record but I was sure there was a way to get the position of a record using MySQL alone.
It turns out there is and the code is something like that shown below:
SET @C=0;
SELECT C FROM (SELECT @C := @C +1 AS C, [recordID] FROM [sqlStatement]) X WHERE [recordID] = Y;
Where C is your counter, [sqlStatement] is the SQL to retrieve the IDs (ie [recordID]) in the order that you want them and Y is the ID of the record you want the position of.
Note that this requires two calls to MySQL so if you are using PHP you'll find you have to make them separately as mysql_query doesn't support multiple queries.
Andrew Parrott runs 