在MySQL中计算年份等于日期的行数 - JSON

Question

Am trying to come up with a JSON result showing

`[{"Year":"2014-07","Numb":4},{"Year":"2015-07","Numb":12},{"Year":"2016-07","Numb":56}]`

"Numb" equals the number of rows that the year appears. So the example above would be 4 rows where 2014 appears, 12 rows where 2015 appears and 56 rows where 2016 appears.

From there, the data is being placed into a Morris Chart. EG Number of clients who joined in YYYY. (Months are just used for the X Axis on the Morris Chart).

Am currently working with

$query = "SELECT * FROM signup ORDER BY DateAdded ASC LIMIT 0, 24";
$result = mysqli_query($mysqli,$query);

$total_rows =  $result->num_rows; // Show ALL rows regardless of date. Used in Array below

$array = array();
while($row = mysqli_fetch_array($result,MYSQLI_ASSOC))
{
    $date = $row['DateAdded'];
    $time=strtotime($date);
    $year=date("Y-m",$time);
    array_push($array,array('Year'=>$year,'Numb'=>$total_rows));

}

echo json_encode($array);

Currently works OK in the Morris Chart except of course the number of rows are all the same.

Seems a fairly complex query that is out of my league.

I have researched the use of COUNT, but cannot see how it handles multiple queries within one query...

Answer 1

I dont have the mysql table so i can not test it... Please test it and tell me if it works for you...

$query = "SELECT * FROM signup ORDER BY DateAdded ASC LIMIT 0, 24";
$result = mysqli_query($mysqli,$query);

$total_rows =  $result->num_rows; // Show ALL rows regardless of date. Used in Array below

$array = array();

function in_array_r($needle, $haystack, $strict = false) {
    foreach ($haystack as $key=>$item) {
        if (($strict ? $item === $needle : $item == $needle) || (is_array($item) && in_array_r($needle, $item, $strict))) {
            return $key;
        }
    }

    return false;
}

while($row = mysqli_fetch_array($result,MYSQLI_ASSOC))
{
    $year    =  date("Y-m",strtotime($row['DateAdded']));
    $key = in_array_r($year, $array);
    if($key !== false):
         $array[$key]["Numb"] += 1;
    else:
        $array[] = array("Year"=>$year,"Numb"=>1);
    endif;
}

echo json_encode($array);