Getting database values grouped in an array I have a database column with dates corresponding to sales orders. I get the count of orders in each month. Currently in my code, I use a switch statement. I know it is not a neat way of coding. So I need to know if there is any way I can do it more precisely. I need the output in such a way as below to form a chart in JavaScript.

$datayearly = array(
    'labels' =&gt; array('JAN', 'FEB', 'MAR', 'APR', 'MAY', 'JUN', 'JUL', 'AUG', 'SEP', 'OCT', 'NOV', 'DEC'),
    'series' =&gt; array(array(
        'name' =&gt; 'series-1',
        'data' =&gt; array($jan, $feb, $mar, $apr, $may, $jun, $jul, $aug, $sep, $oct, $nov, $dec)
    ))
);


Php Code for fetching from database and screenshot of the database.


$thisyear =  date("Y");
$jan=$sep=$feb=$mar=$apr=$may=$jun=$jul=$aug=$sep=$oct=$nov=$dec=0;
$year[0] =0;
$stmt = $conn1-&gt;prepare("SELECT * FROM salessummary WHERE dateoforder LIKE :key");
$stmt-&gt;execute(array('key' =&gt; "%{$thisyear}%"));
foreach ($stmt as $row) {
    $month = date('m', strtotime($row['dateoforder']));
    switch ($month) {
        case "01":
        $jan++;
        case "02":
        $feb++;
        case "03":
        $mar++;
        case "04":
        $apr++;
        case "05":
        $may++;
        case "06":
        $jun++;
        case "07":
        $jul++;
        case "08":
        $aug++;
        case "09":
        $sep++;
        case "10":
        $oct++;
        case "11":
        $nov++;
        case "12":
        $dec++;
    }       
}
$high = max($jan, $feb, $mar, $apr, $may, $jun, $jul, $aug, $sep, $oct, $nov, $dec) + 10;
$datayearly = array(
    'labels' =&gt; array('JAN', 'FEB', 'MAR', 'APR', 'MAY', 'JUN', 'JUL', 'AUG', 'SEP', 'OCT', 'NOV', 'DEC'),
    'series' =&gt; array(array(
        'name' =&gt; 'series-1',
        'data' =&gt; array($jan, $feb, $mar, $apr, $may, $jun, $jul, $aug, $sep, $oct, $nov, $dec)
    ))
);
$temp = json_encode($datayearly);
SQL query

Of course, all the data calculations should be done on the database side. So the main improvement would be an SQL query like this

select upper(date_format(dateoforder, "%b")), count(1) 
from salessummary group by month(dateoforder);


PDO magic

Next, let's utilize the PDO's magic constant that can load your data into the neat associative array, PDO::FETCH_KEY_PAIR, with month abbreviations as keys and order count for the given month as values

So our code would be

$thisyear =  date("Y");
$sql = "select upper(date_format(dateoforder, "%b")), count(1) 
        from salessummary group by month(dateoforder) WHERE dateoforder LIKE ?";
$stmt = $conn1-&gt;prepare($sql);
$stmt-&gt;execute(array("$thisyear%"));
$data = $stmt-&gt;fetchAll(PDO::FETCH_KEY_PAIR);


Data gathering

It will give us the information for all the data that exists in the database, but with obvious gaps. To fill them, let's run a simple loop

$result = [];
foreach (range(1,12) as $num) {
    $mon = strtoupper(date("M", strtotime("$thisyear-$num-15")));
    $result[$mon] = $data[$mon] ?? 0;
}


and then assemble the final data structure

$datayearly = array(
    'labels' =&gt; array_keys($result),
    'series' =&gt; array(array(
        'name' =&gt; 'series-1',
        'data' =&gt; array_values($result)
    ))
);


Note that the null coalesce operator (i.e. ??) exists in PHP7 only. If your PHP version is outdated, then you could use the old form,

     $result[$mon] = isset($data[$mon]) ? $data[$mon] : 0;