How to count how many rows contain a specific value

Question

I am trying to count how many of 'returned' have value of '1' per month. This is what i've got so far:

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

$months_sql = array();


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


    $sql = "SELECT returned, MONTHNAME( date_in) AS date_in
        FROM item
        WHERE  date_in >= NOW() - INTERVAL 1 YEAR";

    $query = mysql_query($sql);


    foreach($months_sql as $month)
    {
        while ($row = mysql_fetch_array($query))
        {
            if($row['date_in'] = $month && $row['returned']=='1')
            {
                $counter_returned++;
            }
            $counter_total++;
        }
        $data1y[] = $counter_returned++;
        $data2y[] = $counter_total++;;
    }

What I want is to store in the two arrays how many records in total per mont and how many of them contain 1 per month.

Answer 1

Your loops are buggy. You're only going to check for the first month in your months_sql array (November, I think?). For the next month, you're not going to have any more db results to fetch.

You need to switch them, and if you do that, your counters need to identify which month they belong to because your db data is not going to get treated per month:

while ($row = mysql_fetch_array($query))
{
    foreach($months_sql as $month)
    {
        if($row['date_in'] == $month)
        {
            if($row['returned']=='1')
            {
                $data1y[$month]++;
            }
            $data2y[$month]++;
        }
    }
}

A quicker way to do this with count()

$sql = "SELECT MONTHNAME( date_in) AS date_in, returned, count(*) as count
    FROM item
    WHERE  date_in >= NOW() - INTERVAL 1 YEAR
    GROUP BY date_in, returned;";
$query = mysql_query($sql);

while ($row = mysql_fetch_array($query))
{
    if(in_array($row['date_in'], $months_sql))
    {
        if($row['returned']=='1')
        {
            $data1y[$month] = $row['count'];
        }
        $data2y[$month] += $row['count'];
    }
}

In either case, if you want to initialize your data with 0 for every months, add this before your db query:

foreach($months_sql as $month){
    $data1y[$month]=0;
    $data2y[$month]=0;
}