TW Blog :: Archives for November 2003

Using MySQL Variables

Relates to MySQL and Databases

Here is a code snippet demonstrating the use of MySQL variables in a set of queries. The problem was to find the longest standing entry in a table for a custom CMS I am building. The date added is a timestamp datatype.

SELECT @min := MIN(added) FROM tbldirectory;
SELECT @interval := UNIX_TIMESTAMP(NOW()) - UNIX_TIMESTAMP(added),
       @name := name 
       FROM tbldirectory WHERE added = @min;
SELECT @weeks := MOD(FLOOR(@interval / (7 * 24 * 60 * 60)),52) as weeks,
       @years:= FLOOR(@interval / (7 * 24 * 60 * 60 * 52)) as years,
       CONCAT(@years, ' year', IF (@years!=1,'s',''), ' and ',
              @weeks, ' week', IF (@weeks!=1,'s','')) as duration,
       @name as name;

I wanted to display the length of time in years and weeks, so once a variable (@interval) was set to the duration in seconds, I used some basic arithmetic with the FLOOR and MOD functions to get the appropriate values. To keep the final select query readable I stored the week and year calculations in variables and then used the CONCAT function to build the required string.

Using the variables and functions in MySQL queries is a great way to reduce unnecessary formatting of the data within the API, and now the query result is ready to go straight into the template. The MySQL Control Centre has a useful SQL editor for building complex query strings step by step.

Posted on Monday, Nov 10, 2003 at 02:22:21.