Of MySQL 5.1 and Joomla part 2 - A dirty trick and an idea

I don't know if you have had the chance to look at my PapaBlues.com website yet, possibly not. And you have no interest in the Blues, then it is perfectly understandable. Anyway, the site use Joomla and runs on MySQL 5.1.30. As I have written before, the support in Joomla for MySQL 5.x features are rather limited, but as a whole, it works OK. Some things not as well as others. Joomla's handling of NULL values is weird for example, but this is inherited I guess from the fact that HTML doesn't know much about NULL values either. I have fixed that partly by using a special class for my tablöes, derived from JTable. My main issue was that as I insist on using InnoDB and proper foreign keys, I can not have Joomla insert a 0 in a column when I really want a NULL, as that can make FK relations fail (NULL means no parent is specified, 0 means it IS specified and has the value 0).

Anyway, if you look at the site and have a look at the gig calendar, you will notice that there are two types of gigs listed:

• Gigs that are just that, gigs, i.e. band so-and-so playing at venue so-and-so.
• Festival gigs, i.e. gigs that happen at festivals.

As it looks right now, these are not treated differently, but really, there is a problem here. What I want to show is that there is a festival, even when no gigs had been determined yet. As it stands now, I do a join between the table that contains the gig and the table that contains the festivals and that is it. Which means that for a festival that has no gigs yet, I will not get any return values and these will not show up.

So will an outer join work? Well, no, for a specific reason: I order gigs according to the gig-date, which is reasonable. A festival on the other hand has a start-date and an end-date. If I outer join gigs with festivals and do an outer join, what date will I use for the join? And if I have no match (i.e. the festival has no gigs registered yet), what will I get the festival date from?

What I would want would be a table with festivals where each and every festival date is present, i.e. if the festival is from august 3 to august 5, I want 3 rows back, one for august 3, one for 4 and one for 5.

If I had this, I could do a UNION between my current gig select (which is more complex than it looks here) and this table with one row for each festival day. But the way I am handling festivals now, with a start and end-date is really practical and how I want it, really. So how do I fix this?

I had a few ideas. One was to get the festivals separately, and then join them with the gigs in my php code. Possible, but messy. Another idea I had, and which I actually implemented before I scrapped it, was to have a stored procedure do this job for me.

Eventually, I figured it out. What I needed was a table with X number of rows, containing the numbers 0 and up to X, X here being the maximum number of days in any festival. Then I could join the festival table and this "dummy" table and get the as many rows as there are days in the festival. Like this:
SELECT f.*,
DATE_ADD(f.`startdate`, INTERVAL il.`id` DAY) AS `eventdate`
FROM `jos_festival` AS f
JOIN `jos_intlist` AS il
ON il.`id` <= DATEDIFF(IFNULL(f.`enddate`, f.`startdate`), f.`startdate`);

As you can see, this is not a very complex SELECT, it will get me back as many rows from the festival table as there are festivals times the number of days in each festival, or to put it differently, as many rows as there are festival days. The eventdate column that is returned is the actual day of the event. So in my example above, I would get back three rows, with eventdate set to August3, 4 and 5, just the way I wanted it.

Now, the above works, but it is rather dirty. The "dummy" table has to have as many rows as the max number of festival days, so this is not really a generic solution, although it works fine in my case. But for now, it works, and I realized I could put another MySQL 5.x feature to good udse here, views. I could create a simple view that would give me back all festival days following a specific date (which is how I show my calendar, I only display upcoming gigs). The view looks like this:
CREATE OR REPLACE VIEW `jos_festivaldate` AS
SELECT f.*
DATE_ADD(f.`startdate`, INTERVAL il.`id` DAY) AS `eventdate`
FROM `jos_festival` AS f
JOIN `jos_intlist` AS il ON il.`id` <= DATEDIFF(IFNULL(f.`enddate`, f.`startdate`), f.`startdate`);

This view is now really easy to use, I just do a SELECT from this view, with a WHERE clause based on the eventdate column, and I will get the correct information back. I then join this with the existing gig SELECT, and that's it!

So what about the kludge with the fixed # of rows in the intlist table above? Well, I know how to fix that. What is needed is a storage engine, that only supports one integer column, and that will always return as many rows as the WHERE-clause determines. This should not be too difficult, and I will get back to you on this eventually. For now though, the above describes how I will remodel the current list of gigs, to be as comprehensive as I want it to be.

/Karlsson