Group by month and return, and use 0 if data not found

146 Views Asked by At

I've been trying to create two arrays with counts of how many 0 and 1 per month.

So far I've managed to count the entries with 0 and 1 in returned but what I need is a 0 value where no items in the month. This is my code:

$data1y=array();
$data2y=array();
$months = array();

$sql = "SELECT MONTH( date_in ) AS date_in_month , COUNT( date_in ) AS date_in_count , returned, COUNT( returned ) AS returned_count
        FROM item
        WHERE date_in >= NOW() - INTERVAL 1 YEAR
        GROUP BY MONTH(date_in), returned";

$result = mysql_query($sql);

while(($row =  mysql_fetch_assoc($result))) {
    if($row['returned']=='0')
    {
        $data1y[] = $row['returned_count'];
    }
    if($row['returned']=='1')
    {
        $data2y[] = $row['returned_count'];
    }
}       

for ($i = 1; $i <= 12; $i++) {
    $months[] = date("M y", strtotime( date( 'Y-m-01' )." -$i months"));
}

What i need is something like this:

$data1y=array(0,80,0,0,47,80,40,116,47,80,40,116);
$data2y=array(0,30,0,0,61,30,82,105,61,30,82,105);
0

There are 0 best solutions below