好吧,所以我在想出解决问题的办法时遇到了麻烦。基本上,我要做的就是统计过去12个月的注册总数,从本月开始。
首先,因为我不能保证每个月都有一个返回值,所以我构建了一个数组并用我的查询结果填充它。我能够拉出所有我需要的数据,但我有麻烦"排序"他们,因为你会。
我希望能够以这种方式显示它,例如:
现在是十月,所以月份列表如下:
2014年11月,2014年12月,2015年1月,2015年2月,2015年3月,…2015年10月。
使用数组
$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)
);
用结果填充它,我得到:
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
这是查询/循环
$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;
编辑:到目前为止,我已经完成了这个:
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
使用此代码:
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');
编辑:你可以看到它正确地按年排序,但以错误的方式列出月份。
编辑:期望输出
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
最后,我将在一个柱状图中显示它们,显示去年每个月的总数。
我不知道这是不是最好的方法,欢迎大家提出建议。
我认为你的答案在于PHP文档的 uasort()
函数页面上的用户贡献说明,由" element .hk"。我已经适应了你的问题,它似乎工作!
<?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);
输出: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
)
)
似乎uasort
在发布多层排序时可能变得不稳定,而当它们具有相同的值时,它保持数组索引顺序。谈话是目前固定在PHP7的小尺寸数组(<16),所以如果你敢这么做,可能值得一试!
您可以使用ussort并在那里添加您的逻辑条件。仅按总价值排序的示例如下:
function sortFunction($item1, $item2)
{
return $item1->TOTAL - $item2->TOTAL;
}
usort($months, "sortFunction");