Sorting array on two conditions

Question

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.

(and don't use mysql_ functions, they're deprecated, change it to mysqli_ functions or PDO objects) — alanmm
– alanmm, Commented Oct 8, 2015 at 13:05
I've never used it, I am looking into how it works now. I did throw the example on my data to see the output, but I need to understand it further so I can write the comparison function — Imperialized
– Imperialized, Commented Oct 8, 2015 at 13:15
usort/uasort will sort your array inside itself and return you true upon success, or false on failure. Each element will be ordered by the return of your callback function, which leaves you free to impose any rule you want to the sorting. The callback gets two params, being $a one array element and $b the next array element to compare with $a. The comparison method is up to you. Returning a negative number, the element $a will descend. A positive one, it will ascend. If zero, it will keep its position. — alanmm
– alanmm, Commented Oct 8, 2015 at 13:20

alanmm · Accepted Answer · 2015-10-08 18:26:43Z

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!

Works like a charm. Thank you for the help! And thanks to that person from php.net!

Stelium · Accepted Answer · 2015-10-08 14:04:15Z

0

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

answered Oct 8, 2015 at 14:04

Stelium

1,3672 gold badges13 silver badges24 bronze badges

1 Comment

Imperialized Over a year ago

usort / uasort is definitely going to be the solution. I am struggling to come up with the logic to achieve the result, however. I can get it to sort by the year & month, but the months are going to wrong way. It goes Dec14, Nov14, Oct15, Sept15, etc etc.

Collectives™ on Stack Overflow

Sorting array on two conditions

2 Answers 2

2 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related