1

I have a table called 'orders' with multiple rows with different dates.

I want to build a graph with number of orders per month, so I need to query an array that looks like this:

  • Jan ------- 3
  • Feb ------- 1
  • Mar ------- 0
  • Apr ------- 8 ......

As seen, even when there are no orders, the Month is showing.

From there, I would need to load the amounts of the array into something like this:

$data1y=array(3,1,0,8...);

While I will have an array of months like this:

$graph->xaxis->SetTickLabels(array('Jan','Feb','March','Apr','May','Jun','Jul','Aug','Sep','Oct','Nov','Dec'));

I tried with a SQL like this one, but no luck:

SELECT COUNT(*) FROM orders GROUP BY month(date)
samyb8
  • 2,560
  • 10
  • 40
  • 68
  • SELECT COUNT(*) FROM orders GROUP BY month(date) ....what is given you as result – Hackerman Dec 17 '13 at 14:56
  • This question explains the concept of how to do what you want in MS SQL: [Include missing months in Group By query](http://stackoverflow.com/q/11479918) – Danny Beckett Dec 17 '13 at 14:57
  • Create an array that lists the months as keys, then use the results from SQL to populate the values. – Dave Dec 17 '13 at 15:00

2 Answers2

2

Your query should something like this:

SELECT DATE_FORMAT(`date`, '%Y%m') AS `Ym`, COUNT(*) AS `count`
FROM `orders`
GROUP BY `Ym`

demo

p.s. You can fill missing months in PHP, or in SQL like this.

Community
  • 1
  • 1
Glavić
  • 42,781
  • 13
  • 77
  • 107
  • this one looks great. How could I assign only the counts to the array '$data1y=array()'? – samyb8 Dec 17 '13 at 16:59
  • I'm fetching like: "$orders = $ordersQuery->fetchAll(PDO::FETCH_ASSOC)" – samyb8 Dec 17 '13 at 17:11
  • Array ( [0] => Array ( [Ym] => December [count] => 7 ) [1] => Array ( [Ym] => November [count] => 11 ) [2] => Array ( [Ym] => October [count] => 9 ) [3] => Array ( [Ym] => September [count] => 8 ) [4] => Array ( [Ym] => August [count] => 9 ) [5] => Array ( [Ym] => July [count] => 1 ) [6] => Array ( [Ym] => May [count] => 2 ) [7] => Array ( [Ym] => April [count] => 1 ) ) – samyb8 Dec 17 '13 at 17:22
  • That would be great but I am on PHP 5.2, so DateTime::createFromFormat is not supported... – samyb8 Dec 17 '13 at 17:30
  • 1
    @samyb8: then create SQL query like in [this example](https://eval.in/80385), this should work on 5.2. – Glavić Dec 17 '13 at 17:37
1

You could do the whole thing in PHP after using a query like:-

SELECT date FROM orders

If after you have run, for example, PDOStatement::fetchAll(), you have an array called $dateArray containing the dates in your DB, then you could do something like:-

$countArray = ['Jan' => 0, 'Feb' => 0, 'Mar' => 0, 'Apr' => 0, 'May' => 0, 'Jun' => 0, 'Jul' => 0, 'Aug' => 0, 'Sep' => 0, 'Oct' => 0, 'Nov' => 0, 'Dec' => 0];
foreach($dateArray as $dateString){
    $date = new \DateTime($dateString);
    $countArray[$date->format('M')]++;
}

I have setup a working example here.

See the PHP manual for more information on the DateTime classes and PDO.

vascowhite
  • 18,120
  • 9
  • 61
  • 77