Ok, so I am having trouble coming up with a solution to my problem. Basically, what I am trying to accomplish is to total the number of registrations in the last twelve months, starting with the current month.
First, since I cannot guarantee that each month would have a return, I built an array and populated it with my query results. I was able to pull all the data that I needed, but I am having trouble "sorting" them as you will.
I want to be able to display it in this manner, ex:
It is currently october so the months would list like this:
Nov 2014, Dec 2014, Jan 2015, Feb 2015, March 2015, ... October 2015.
Using this array
$months = array(
'1'=>array('TOTAL'=>0, 'YEAR'=>NULL),
'2'=>array('TOTAL'=>0, 'YEAR'=>NULL),
'3'=>array('TOTAL'=>0, 'YEAR'=>NULL),
'4'=>array('TOTAL'=>0, 'YEAR'=>NULL),
'5'=>array('TOTAL'=>0, 'YEAR'=>NULL),
'6'=>array('TOTAL'=>0, 'YEAR'=>NULL),
'7'=>array('TOTAL'=>0, 'YEAR'=>NULL),
'8'=>array('TOTAL'=>0, 'YEAR'=>NULL),
'9'=>array('TOTAL'=>0, 'YEAR'=>NULL),
'10'=>array('TOTAL'=>0, 'YEAR'=>NULL),
'11'=>array('TOTAL'=>0, 'YEAR'=>NULL),
'12'=>array('TOTAL'=>0, 'YEAR'=>NULL)
);
And populating it with the results I get:
key: 1 (January) total: 17 year: 2015
key: 2 (February) total: 20 year: 2015
key: 3 (March) total: 23 year: 2015
key: 4 (April) total: 29 year: 2015
key: 5 (May) total: 26 year: 2015
key: 6 (June) total: 26 year: 2015
key: 7 (July) total: 26 year: 2015
key: 8 (August) total: 24 year: 2015
key: 9 (September) total: 22 year: 2015
key: 10 (October) total: 24 year: 2015
key: 11 (November) total: 30 year: 2014
key: 12 (December) total: 42 year: 2014
Here's the query/loop
$query = "SELECT MONTH(DATE_ADDED) as MONTH_NUMBER, MONTHNAME(DATE_ADDED) as MONTH_NAME, COUNT(*) as TOTAL_REGISTRATIONS, YEAR(DATE_ADDED) AS YEAR FROM MEMBERS WHERE DATE_ADDED >= (CURDATE() - INTERVAL (DAY(CURDATE()) - 1) DAY) - INTERVAL 11 MONTH GROUP BY MONTH(DATE_ADDED) ORDER BY DATE_ADDED ASC";
$result = mysql_query($query) or die(mysql_error());
while($row = mysql_fetch_assoc($result)){
$months[$row['MONTH_NUMBER']]['NAME'] = $row['MONTH_NAME'];
$months[$row['MONTH_NUMBER']]['TOTAL'] = $row['TOTAL_REGISTRATIONS'];
$months[$row['MONTH_NUMBER']]['YEAR'] = $row['YEAR'];
$months[$row['MONTH_NUMBER']]['MONTH_NUM'] = $row['MONTH_NUMBER'];
}
foreach($months as $key=>$data){
echo 'key: '.$key. ' ('.$data['NAME'].') total: '.$data['TOTAL'].' year: '.$data['YEAR'].'<br/>';
}
exit;
Edit: So far, I've been able to accomplish this:
key: 12 (December) total: 42 year: 2014
key: 11 (November) total: 30 year: 2014
key: 10 (October) total: 24 year: 2015
key: 9 (September) total: 22 year: 2015
key: 8 (August) total: 24 year: 2015
key: 7 (July) total: 26 year: 2015
key: 6 (June) total: 26 year: 2015
key: 5 (May) total: 26 year: 2015
key: 4 (April) total: 29 year: 2015
key: 3 (March) total: 23 year: 2015
key: 2 (February) total: 20 year: 2015
key: 1 (January) total: 17 year: 2015
using this code:
function sortArray(array $a, array $b) {
if($a['YEAR'] <= $b['YEAR'] && $a['MONTH_NUM'] < $b['MONTH_NUM']){
return 1;
}elseif($a['YEAR'] <= $b['YEAR'] && $a['MONTH_NUM'] > $b['MONTH_NUM']){
return -1;
}elseif($a['YEAR'] >= $b['YEAR'] && $a['MONTH_NUM'] > $b['MONTH_NUM']){
return -1;
}elseif($a['YEAR'] >= $b['YEAR'] && $a['MONTH_NUM'] < $b['MONTH_NUM']){
return 1;
} else {
return 0;
}
}
// Sort
uasort($months, 'sortArray');
Edit: You can see it sorts by the year properly, but lists the months the wrong way.
Edit: Desired Output
key: 11 (November) total: 00 year: 2014
key: 12 (December) total: 00 year: 2014
key: 1 (January) total: 00 year: 2015
key: 2 (February) total: 00 year: 2015
key: 3 (March) total: 00 year: 2015
key: 4 (April) total: 00 year: 2015
key: 5 (May) total: 00 year: 2015
key: 6 (June) total: 00 year: 2015
key: 7 (July) total: 00 year: 2015
key: 8 (August) total: 00 year: 2015
key: 9 (September) total: 00 year: 2015
key: 10 (October) total: 00 year: 2015
Ultimately, I will be displaying these in a bar graph showing the total per month over the last year.
I don't know if this is the best way to go about doing this, any suggestions are welcome.
I think your answer lies as an user contribution note on PHP Docs's uasort() function page, by "clement.hk". I've adapted it to your problem and it seems to work!
<?php
// array reproduction
$months = array(
'1'=>array('TOTAL'=>0, 'YEAR'=>2015),
'2'=>array('TOTAL'=>0, 'YEAR'=>2015),
'3'=>array('TOTAL'=>0, 'YEAR'=>2015),
'4'=>array('TOTAL'=>0, 'YEAR'=>2015),
'5'=>array('TOTAL'=>0, 'YEAR'=>2015),
'6'=>array('TOTAL'=>0, 'YEAR'=>2015),
'7'=>array('TOTAL'=>0, 'YEAR'=>2015),
'8'=>array('TOTAL'=>0, 'YEAR'=>2015),
'9'=>array('TOTAL'=>0, 'YEAR'=>2015),
'10'=>array('TOTAL'=>0, 'YEAR'=>2015),
'11'=>array('TOTAL'=>0, 'YEAR'=>2014),
'12'=>array('TOTAL'=>0, 'YEAR'=>2014)
);
// "clement.hk"'s alternate version of "uasort", where it keeps
// the index order when $a == $b. Credits to him!
function stable_uasort(&$array, $cmp_function) {
if(count($array) < 2) {
return;
}
$halfway = count($array) / 2;
$array1 = array_slice($array, 0, $halfway, TRUE);
$array2 = array_slice($array, $halfway, NULL, TRUE);
stable_uasort($array1, $cmp_function);
stable_uasort($array2, $cmp_function);
if(call_user_func($cmp_function, end($array1), reset($array2)) < 1) {
$array = $array1 + $array2;
return;
}
$array = array();
reset($array1);
reset($array2);
while(current($array1) && current($array2)) {
if(call_user_func($cmp_function, current($array1), current($array2)) < 1) {
$array[key($array1)] = current($array1);
next($array1);
} else {
$array[key($array2)] = current($array2);
next($array2);
}
}
while(current($array1)) {
$array[key($array1)] = current($array1);
next($array1);
}
while(current($array2)) {
$array[key($array2)] = current($array2);
next($array2);
}
return;
}
// the equal comparison is pointless since you have twelve months of the same
// year... we need comparison only when it differs
stable_uasort($months, function ($a, $b) {
/* if($a['YEAR'] == $b['YEAR']) {
return 0;
} */
return ($a['YEAR'] > $b['YEAR']) ? 1 : -1;
});
echo '<pre>';
print_r($months);
The output:
Array
(
[11] => Array
(
[TOTAL] => 0
[YEAR] => 2014
)
[12] => Array
(
[TOTAL] => 0
[YEAR] => 2014
)
[1] => Array
(
[TOTAL] => 0
[YEAR] => 2015
)
[2] => Array
(
[TOTAL] => 0
[YEAR] => 2015
)
[3] => Array
(
[TOTAL] => 0
[YEAR] => 2015
)
[4] => Array
(
[TOTAL] => 0
[YEAR] => 2015
)
[5] => Array
(
[TOTAL] => 0
[YEAR] => 2015
)
[6] => Array
(
[TOTAL] => 0
[YEAR] => 2015
)
[7] => Array
(
[TOTAL] => 0
[YEAR] => 2015
)
[8] => Array
(
[TOTAL] => 0
[YEAR] => 2015
)
[9] => Array
(
[TOTAL] => 0
[YEAR] => 2015
)
[10] => Array
(
[TOTAL] => 0
[YEAR] => 2015
)
)
Seems uasort can go unstable when issuing multi-leveled sortings while it maintains array index order when they have same values. The talking is that is currently fixed on PHP7 for small size arrays (<16), so if you dare it, may be worth the trying!
you can use usort and add your logic in there with conditions. An example with sorting only by your total value would be this.
function sortFunction($item1, $item2)
{
return $item1->TOTAL - $item2->TOTAL;
}
usort($months, "sortFunction");