Hi, I'm doing a query that very basically does something like this: SELECT min(somefield) FROM TABLE GROUP BY date_format(fieldwithdate, '%Y%m'); Which returns the smallest "somefield" each month (type timestamp). Sometimes it returns something like
The problem here is the missing entry for "200511". It's missing because I don't have any data on that month, but in those cases I would like a row to be returned like "200511 - ". Is this possible? I remember someone showed me a while ago that you could create a view with dates and join with that in some way? Is that possible? Is there an easier way? (ok, enough questions ;) )
An alternative way of doing this is to handle the date range in the presentation code. I've done a similar thing with an event calendar.
1. Open your query recordset (with dates missing) 2. Begin simple loop to display the date range (200509 to 200602) 3. Check whether the current record's date value is the same as the current loop date value: - If [recordset date] = [code loop date], display the recordset value and move to the next record. - If not, just display blank/zero/whatever 4. Loop
I thought of doing that first, but since I'm processing my data in XSLT I thought it might be more efficient to have the data be "correct". But I'll look into looping and such in the php code that generates the XML file instead.