Create query with dynamic number of columns based on a range of dates

I want to create an array using PHP that can be used in MySQL. My query looks like this...

select
    name,
    sum(if (tdate='2009-09-09', amount, 0) as day1,
    sum(if(tdate='2009-09-10', amount, 0) as day2
    ...
    ... 
    sum(if(tdate='2009-10-01', amount, 0) as day23
from revenue
group by name;

The date range may change but always be less than 30 days. If I supply the start and end dates to PHP array will it be able generate the query as shown above?

Solution

You should be able to use grouping to do this for you:

SELECT name, DATE(tdate) as d, SUM(amount)
FROM revenue
WHERE d >= NOW() AND d < DATE_ADD(NOW(), 30 DAYS)
GROUP BY name, d

This will give you each date on a new row, instead of multiple fields in the same row, but it's hopefully a workable solution for you.

I assumed that tdate is a datetime. DATE(tdate) will convert it to just a date (dropping the time part). Also, replace the WHERE clause with whatever range you require.

Edit: ok, so we need to have one row per name, with as many fields as needed. This sort of table is called a "crosstab" and MySQL doesn't really support it in a nice way, but you can definitely hack it together. See here for more information: http://rpbouman.blogspot.com/2005/10/creating-crosstabs-in-mysql.html

However, if the dates are known beforehand, it wouldn't be too hard to use PHP to put it all together.

// get this list in whichever way you wish
$dates = array("2009-09-09", "2009-09-10", "2009-09-11");

$sql = "SELECT name, "
     . "sum(if (tdate='"
     . implode("', amount, 0), sum(if (tdate='", $dates)
     . "', amount, 0)"
     . "FROM revenue "
     . "GROUP BY name";