CODEWITHSUNDEEP
phpmysqlarrays
Sarath Hari
Sarath Hari

Reputation: 219

Getting database values grouped in an array

I have a database column with order dates. I need to get the count of orders in each month.Currently according to my code i use a switch statement. I know its not a neat way of coding.So i need to know is there any way i can do it more precise. I need the output in such a way as below to form a chart in Javascript.So i hope someone can help me out in this.

Thanks in advance.

$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);

Upvotes: 0

Views: 43

Answers (2)

3Dos
3Dos

Reputation: 3487

Does this satisfy your need ?

$thisyear =  date("Y");
$months = array(
    1 => 0,
    2 => 0,
    3 => 0,
    4 => 0,
    5 => 0,
    6 => 0,
    7 => 0,
    8 => 0,
    9 => 0,
    10 => 0,
    11 => 0,
    12 => 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']));
    $months[intval($month)]++;  
}

$high = max($months) + 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_values($months)
    ))
);
$temp = json_encode($datayearly);

Upvotes: 1

Theodore R. Smith
Theodore R. Smith

Reputation: 23231

This will work:

SELECT COUNT(*), DATE_FORMAT(dateoforder,'%Y-%m') as Month 
FROM orders 
GROUP BY DATE_FORMAT(dateoforder,'%Y-%m');

If you want something other than "2017-10" as the month, change the first DATE_FORMAT to any format you want. See the docs for the details.

Upvotes: 2

Related Questions