3
\$\begingroup\$

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' => array('JAN', 'FEB', 'MAR', 'APR', 'MAY', 'JUN', 'JUL', 'AUG', 'SEP', 'OCT', 'NOV', 'DEC'),
    'series' => array(array(
        'name' => 'series-1',
        'data' => array($jan, $feb, $mar, $apr, $may, $jun, $jul, $aug, $sep, $oct, $nov, $dec)
    ))
);

Php Code for fetching from database and screenshot of the database. enter image description here

$thisyear =  date("Y");
$jan=$sep=$feb=$mar=$apr=$may=$jun=$jul=$aug=$sep=$oct=$nov=$dec=0;
$year[0] =0;
$stmt = $conn1->prepare("SELECT * FROM salessummary WHERE dateoforder LIKE :key");
$stmt->execute(array('key' => "%{$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' => array('JAN', 'FEB', 'MAR', 'APR', 'MAY', 'JUN', 'JUL', 'AUG', 'SEP', 'OCT', 'NOV', 'DEC'),
    'series' => array(array(
        'name' => 'series-1',
        'data' => array($jan, $feb, $mar, $apr, $may, $jun, $jul, $aug, $sep, $oct, $nov, $dec)
    ))
);
$temp = json_encode($datayearly);
\$\endgroup\$

2 Answers 2

2
\$\begingroup\$

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->prepare($sql);
$stmt->execute(array("$thisyear%"));
$data = $stmt->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' => array_keys($result),
    'series' => array(array(
        'name' => 'series-1',
        'data' => 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;
\$\endgroup\$
1
\$\begingroup\$

Flaw with current code

The original output with the sample data appears to be:

{
    "labels":["JAN","FEB","MAR","APR","MAY","JUN","JUL","AUG","SEP","OCT","NOV","DEC"],
    "series": [{            
        "name":"series-1",
        "data":[0,0,0,0,0,0,0,0,1,4,4,4]
    }]
}

Notice that the last 3 elements of the series data array are a 4, when really the first of those should be a 3 and the other two should be a 0, since there are only 4 records: 1 in September and three in October. This is because the cases of the switch statement do not contain break, and thus whenever a CASE line matches, every case after that is executed (and thus the associated counts for subsequent months get increased).

Making the code work

The answer by Your Common Sense is good, though there is still one thing that could be improved. Instead of using a LIKE operator and the string [YEAR]% for the on the dateoforder, use an equals operator with YEAR(dateoforder). Refer to this answer for a detailed explanation of why that should be used.

$sql = "select upper(date_format(dateoforder, '%b')), count(1) from salessummary WHERE year(dateoforder) = ? group by month(dateoforder) ";

And then the year parameter can be bound as an integer instead of a string:

$stmt->execute(array($thisyear));
\$\endgroup\$

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.