Sorting multidim array: prioritize if column contains substring, then order by a second column

Question

I am currently creating a sorting method that consists of values from an mysql query.

Here's a brief view of the array:

    Array
    (
        [0] => Array
            (
                ['id'] = 1;
                ['countries'] = 'EN,CH,SP';
            )
        [1] => Array
            (
                ['id'] = 2;
                ['countries'] = 'GE,SP,SV';
            )
    )

I have succeeded in making a normal usort based on the numeric id values, but I rather want to sort the array by the content of the "countries" field (if it contains a set string, a country code in this case), and then by the id field.

The following snippet was my first idea of how to do it, but I have no idea of how to incorporate it into an working function:

in_array('EN', explode(",",$a['countries']) );

How would you do it?

Thanks!

---

I am really getting nowhere with this unfortunately.

Here is what I have for the moment, and its giving me nothing but errors: uasort() [function.uasort]: Invalid comparison function

function compare($a, $b) {
    global $usercountry;

        if ( in_array($usercountry, $a['countries']) && in_array($usercountry, $a['countries']) ) {
            $return = 0;
        }

        else if (in_array($usercountry, $a['countries'])) {
            $return = 1;
        }

        else {
            $return = -1;
        }

        return $return;


        }

        $array= usort($array, "compare");

Is there anyone who might give me a hint of how to go on with it?

Presnell

Out of curiosity, why don't you get them sorted out of the DB in the first place, e.g. ORDER BY country, id – Zweig

Its already sorted by ID in the first place, as you say, but that doesn't solve the in_array-part of the issue though... – Presnell

possible duplicate of Reference: all basic ways to sort arrays and data in PHP – Didst

Answer 1

Personally, I would use a custom (anonymous) function in conjunction with usort().

EDIT: Re - your comment. Hopefully this will put you on the right track. This function gives equal priority to elements which both have EN or neither have EN, or adjusted priority when just one has EN.

usort($array,function ($a, $b) {
    $ac = strpos($a['countries'],'EN');
    $bc = strpos($b['countries'],'EN');
    if (($ac !== false && $bc !== false) || ($ac == false && $bc == false)) {
        return 0;
    }
    elseif ($ac !== false) {
        return 1;
    }
    else {
        return -1;
    }
});

This function, on the other hand, gives equal priority if both have EN, higher if one has EN, and does a text comparison if neither has EN.

usort($array,function ($a, $b) {
    $ac = strpos($a['countries'],'EN');
    $bc = strpos($b['countries'],'EN');
    if ($ac !== false && $bc !== false)) {
        return 0;
    }
    elseif ($ac !== false) {
        return 1;
    }
    elseif ($bc !== false) {
        return -1;
    }
    else {
        if ($a['countries'] == $b['countries']) {
            return 0;
        }
        elseif($a['countries'] > $b['countries']) {
            return 1;
        }
        else {
            return -1;
        }
    }
});

Again, hopefully this will give you enough direction to move forward on your own. If you are having any problems, feel free to post more comments and I'll try to help. A note if you're tying to compare multiple properties with weight: try out a funky switch block, e.g.

$ac = array_flip(explode(',',$a['countries']));
$bc = array_flip(explode(',',$b['countries']));
switch (true) {
    case array_key_exists('EN',$ac) && !array_key_exists('EN',$bc):
        return 1;
    case array_key_exists('DE',$ac) && !array_key_exists('EN',$bc) && !array_key_exists('EN',$bc):
        return 1;
    // and so on
}

#More Edits!# Actually, I was thinking more on the problem of complex sorting, and I have come up with the following solution, for your consideration. It will allow you to define numerical rankings based on keywords which would appear in the countries index. Here is the code, including an example:

Example Array

$array = array(
    array(
        'countries' => 'EN,DE,SP',
    ),
    array(
        'countries' => 'EN,CH,SP',
    ),
    array(
        'countries' => 'DE,SP,CH',
    ),
    array(
        'countries' => 'DE,SV,SP',
    ),
    array(
        'countries' => 'EN,SP,FR',
    ),
    array(
        'countries' => 'DE,FR,CH',
    ),
    array(
        'countries' => 'CH,EN,SP',
    ),

);

Sorting Routine

$rankings = array(
    'EN' => 10,
    'SP' => 8,
    'FR' => 7,
    'DE' => 5,
    'CH' => 3,
    'SV' => 1,
);
usort($array, function (&$a, &$b) use ($rankings) {
    if (isset($a['_score'])) {
        $aScore = $a['_score'];
    }
    else {
        $aScore = 0;
        $aCountries = explode(',',$a['countries']);
        foreach ($aCountries as $country) {
            if (isset($rankings[$country])) {
                $aScore += $rankings[$country];
            }
        }
        $a['_score'] = $aScore;
    }

    if (isset($b['_score'])) {
        $bScore = $b['_score'];
    }
    else {
        $bScore = 0;
        $bCountries = explode(',',$b['countries']);
        foreach ($bCountries as $country) {
            if (isset($rankings[$country])) {
                $bScore += $rankings[$country];
            }
        }
        $b['_score'] = $bScore;
    }
    if ($aScore == $bScore) {
        return 0;
    }
    elseif ($aScore > $bScore) {
        return -1;
    }
    else {
        return 1;
    }
});

Note: This code will sort the highest ranking entires to the top of the array. If you want reverse behavior, change this:

    elseif ($aScore > $bScore) {

to

    elseif ($aScore < $bScore) {

Note that the greater-than was changed to a less-than symbol. Making this change will result in the lowest ranking entries being sorted to the top of the array. Hope all this helps!

##NOTE ALSO!## This code will make a small change to your array, in that it adds the _score element to each array. Hopefully this is not a problem, as by storing this value I was literally able to increase speed by more than double (.00038-.00041 down to .00016-.00018 in my benchmarks). If not, remove the if blocks that retrieve the cached value and let the contents of the else blocks execute every time, except of course for the part which stores the score value.

By the way, here's a var_export() dump of the array after it was sorted:

array (
  0 => array (
    'countries' => 'EN,SP,FR',
    '_score' => 25,
  ),
  1 => array (
    'countries' => 'EN,DE,SP',
    '_score' => 23,
  ),
  2 => array (
    'countries' => 'EN,CH,SP',
    '_score' => 21,
  ),
  3 => array (
    'countries' => 'CH,EN,SP',
    '_score' => 21,
  ),
  4 => array (
    'countries' => 'DE,SP,CH',
    '_score' => 16,
  ),
  5 => array (
    'countries' => 'DE,FR,CH',
    '_score' => 15,
  ),
  6 => array (
    'countries' => 'DE,SV,SP',
    '_score' => 14,
  ),
)

Enjoy!

Motorman

Answer 2

Finally found this wonderful function at PHP.net:

        function array_msort($array, $cols)
        {
            $colarr = array();
            foreach ($cols as $col => $order) {
                $colarr[$col] = array();
                foreach ($array as $k => $row) { $colarr[$col]['_'.$k] = strtolower($row[$col]); }
            }
            $eval = 'array_multisort(';
            foreach ($cols as $col => $order) {
                $eval .= '$colarr[\''.$col.'\'],'.$order.',';
            }
            $eval = substr($eval,0,-1).');';
            eval($eval);
            $ret = array();
            foreach ($colarr as $col => $arr) {
                foreach ($arr as $k => $v) {
                    $k = substr($k,1);
                    if (!isset($ret[$k])) $ret[$k] = $array[$k];
                    $ret[$k][$col] = $array[$k][$col];
                }
            }
            return $ret;

        }

This is how each country looks like: $array['countries'] = in_array($needle, $haystack); }

$array = $array = array_msort($array, array('countries'=>SORT_DESC, 'id'=>SORT_ASC));

Thanks all for your help!

Presnell

Yikes... eval()? Entirely unnecessary. – Diversification

Answer 3

You might consider array_walk and array_walk_recursive and array_map, which when combined together maybe to get to doing what you want to do.

Spleen

This "hint" could have been a comment under the question. – Diversification

Answer 4

I am currently creating a sorting method that consists of values from an mysql query.

TRUTH:
Using anything other than MySQL to sort your result set will be less efficient (with php, a usort() or array_multisort() call will be more convoluted and harder to maintain) and therefore inappropriate.

SQL: (Demo)

ORDER BY IF(LOCATE('EN', countries), 0, 1), id;

This prioritizes countries column values that contain EN then sorts on id ASC.

---

For anyone who isn't handling a sql result set or cannot manipulate the query for some reason, I endorse usort(). PHP7 offers a beautiful new operator that performs a comparison and returns one of three values (-1, 0, 1). This operator is affectionately called the "spaceship operator" and looks like this <=>.

PHP: (Demo)

$test = [
    ['id' => 1, 'countries' => 'EN,CH,SP'],
    ['id' => 2, 'countries' => 'GE,SP,SV'],
    ['id' => 3, 'countries' => 'PR,SP,IT'],
    ['id' => 4, 'countries' => 'EN'],
    ['id' => 5, 'countries' => 'SP,EN'],
    ['id' => 6, 'countries' => 'SV,SP,EN'],
    ['id' => 7, 'countries' => 'GE,SP'],
    ['id' => 8, 'countries' => 'FR'],
    ['id' => 9, 'countries' => 'RU,EN'],
    ['id' => 10, 'countries' => 'EN,SP,IT'],
    ['id' => 11, 'countries' => 'SP,GR'],
    ['id' => 12, 'countries' => 'GR,EN']
];

usort($test, function($a, $b) {
    return [strpos($a['countries'], 'EN') === false, $a['id']]
           <=>
           [strpos($b['countries'], 'EN') === false, $b['id']];
});

var_export($test);

PHP8 alternatives:

return [str_contains($b['countries'], 'EN'), $a['id']]
       <=>
       [str_contains($a['countries'], 'EN'), $b['id']];

or

return str_contains($b['countries'], 'EN') <=> [str_contains($a['countries'], 'EN')
       ?: $a['id']] <=> $b['id']];

Output:

array (
  0 => 
  array (
    'id' => 1,
    'countries' => 'EN,CH,SP',
  ),
  1 => 
  array (
    'id' => 4,
    'countries' => 'EN',
  ),
  2 => 
  array (
    'id' => 5,
    'countries' => 'SP,EN',
  ),
  3 => 
  array (
    'id' => 6,
    'countries' => 'SV,SP,EN',
  ),
  4 => 
  array (
    'id' => 9,
    'countries' => 'RU,EN',
  ),
  5 => 
  array (
    'id' => 10,
    'countries' => 'EN,SP,IT',
  ),
  6 => 
  array (
    'id' => 12,
    'countries' => 'GR,EN',
  ),
  7 => 
  array (
    'id' => 2,
    'countries' => 'GE,SP,SV',
  ),
  8 => 
  array (
    'id' => 3,
    'countries' => 'PR,SP,IT',
  ),
  9 => 
  array (
    'id' => 7,
    'countries' => 'GE,SP',
  ),
  10 => 
  array (
    'id' => 8,
    'countries' => 'FR',
  ),
  11 => 
  array (
    'id' => 11,
    'countries' => 'SP,GR',
  ),
)

The array elements on either side of the spaceship operator are evaluated from left to right (leftside [0] vs rightside [0], then moving onto the pair of [1] values if there is a "tie" between the two [0] values).

If the === false looks backwards, let me explain...

If EN is found in the countries string, the condition will evaluate as false. When comparing true and false, remember that true equates to 1 and false equates to 0. We want ASC sorting, so we want to put false outcomes before true outcomes, ergo strings containing EN need to return false. Hopefully that clears up the logic.

Diversification