I have this query, which returns sales by month:
SELECT YEAR(txn_date) as 'year', MONTHNAME(txn_date) as 'month',
SUM(grand_total) as 'sales'
WHERE YEAR(txn_date) BETWEEN '$last_year' AND '$this_year'
GROUP BY month
What's a good way of populating a table showing sales for the last two years, by month, including months that have no sales? Normally I like to get SQL to do as much as possible, but in this case I don't think it can help me as there's no month table or anything like that.
Am I going to have to do some crude For Loop that counts to 12 and searches through the array for matches on month and year? I feel I'm missing some obvious, elegant way of doing this...