My current ecommerce platform, the worlds worst, is built in ColdFusion. Though I'm not a fan of ColdFusion, it is not the cause of the plaform's horribleness - languages are rarely the cause of bad programming, just a symptom.
Anyway, ColdFusion has all these nice little helper functions. One that comes in handy quite often is ValueList(). ValueList takes a column in a query and returns a delimited list of all the values in that column. I've used this function to total up a column by specifying a '+' as the delimiter and evaluating the result. This function is useful because there are many times where you just want to show a list of items, but the database returns them a seperate rows. So instead of looping through the result set, this gives you a nicely formatted list.
But many times retrieving this information requires a seperate query to the database. For example, to display a list of states, and the zip codes in that state. You'd query for the states, and then for each state you'd query the database for the zip codes in that state. You can't do a join, because then you'd get one record for each zip code, where I want one record for each state. And you can't do a sub-query because there is more than one zip code per state, and sub-queries cannot return more than one record. So you end up with 51 queries to database.
Ahhh, my favorite MySQL function: GROUP_CONCAT(). GROUP_CONCAT allows you to creat a delimited list (you can specify the delimiter using SEPERATOR) of the results of a query, in one record. Now you can get all the states, and for each state you get a delimited list of zip codes in that state, all in one query. That just got rid of 50 extra queries!
Here's an example of the above SQL:
SELECT s.StateID, s.StateName, (SELECT GROUP_CONCAT(z.ZipCode SEPERATOR ', ') FROM ZipCodes z WHERE z.StateID = s.StateID) as zipCodes
FROM States s
Item of the Day: Porcher Bathroom Vanity: Dune Console