phpmysqlarraysselectdynamic

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";