Getting database values grouped in an array

Question

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.

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

You should probably post this in codereview.stackexchange.com instead but the cleaner way would be do do this directly in sql instead of php. — jeroen
– jeroen, Commented Oct 31, 2017 at 10:06

Theodore R. Smith · Accepted Answer · 2017-10-31 10:14:41Z

2

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.

edited Oct 31, 2017 at 10:14

answered Oct 31, 2017 at 10:09

Theodore R. Smith

23.5k14 gold badges75 silver badges97 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

3Dos · Accepted Answer · 2017-10-31 10:23:10Z

1

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

edited Oct 31, 2017 at 10:23

answered Oct 31, 2017 at 10:09

3Dos

3,5474 gold badges26 silver badges40 bronze badges

4 Comments

Sarath Hari Over a year ago

data":{"1":0,"2":0,"3":0,"4":0,"5":0,"6":0,"7":0,"8":0,"9":1,"10":1,"11":0,"12":0} can i get this like data":{"0,0,0,0,0,0,0,0,1,1,0,0} the format is not proper in the output. I only need values in the label data.

3Dos Over a year ago

I edited my post but check the one from @TheodoreR.Smith which is way better to retrieve the datas the way you want

Sarath Hari Over a year ago

@3Dos i agree that but my date is not stored in varchar format in sql database.so that code doesn't work for me well.i will up-vote for him anyway.Thank you for the helping so much.

Theodore R. Smith Over a year ago

@SarathHari My code only works if dateoforder is a datetime, not a varchar.

Collectives™ on Stack Overflow

Getting database values grouped in an array

2 Answers 2

Comments

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related