CODEWITHSUNDEEP
phpmysql
Glyph
Glyph

Reputation: 555

Sorting on 2 columns in PHP without having to redo MySQL query

I have a $result variable in PHP from a MySQL query & I would like to sort it (with PHP) on 2 different columns.

I am doing this anytime a user clicks a different sort method, so I don't want to have to redo the MySQL query with ORDER BY ...., .... each time.

Upvotes: 2

Views: 669

Answers (2)

Paul Dixon
Paul Dixon

Reputation: 300845

It would probably be more efficient to have MySQL do the ordering for you, but if you want to do it in PHP, check out usort which will let you sort an array using a callback function to decide the ordering.

If your $result is an array of associative database rows, here's a very simple example of using usort to sort on a string column 'foo' ...

//sort on string column
function sortStringFoo($a, $b)
{
    return strcmp($a['foo'], $b['foo']);
}
usort($result, 'sortStringFoo');

Here's how you'd do a numeric sort...

//sort on numeric column
function sortNumericBar($a, $b)
{
    if ($a['bar'] == $b['bar']) {
        return 0;
    }
    return ($a['bar'] < $b['bar']) ? -1 : 1;
}
usort($result, 'sortNumericBar');

That illustrates the basics of how to use usort, but how might we sort on several columns at once? Here's how we could combine our comparison callbacks to illustrate this by sorting on 'foo' and then 'bar'...

function sortFooBar($a,$b)
{
    $order=sortStringFoo($a,$b);
    if ($order==0)
       $order=sortNumericBar($a,$b);

    return $order;
}
usort($result, 'sortFooBar');

You could unroll sortFooBar into a single function, but this illustrates the technique: compare first column, if same, compare second column, and so on.

Upvotes: 2

Shef
Shef

Reputation: 45589

You can achieve this by splitting all the columns of your table into separate arrays, then doing an array_multisort (notice example #3). Here is a test case:

<?php
$arr = array(
    array(
        'name' => 'Maria',
        'surname' => 'White',
        'age' => 24
    ),
    array(
        'name' => 'John',
        'surname' => 'Brown',
        'age' => 32
    ),
    array(
        'name' => 'John',
        'surname' => 'Brown',
        'age' => 33
    )
);

$names      = array();
$surnames   = array();
$ages       = array();

foreach($arr as $key => $row){
    $names[$key]    = $row['name'];
    $surnames[$key] = $row['surnames'];
    $ages[$key]     = $row['age'];
}

// perform any sorting task you like
array_multisort($names, SORT_ASC, $surnames, SORT_ASC, $ages, SORT_DESC, $arr);

print_r($arr);
?>

The output will be:

Array
(
    [0] => Array
        (
            [name] => John
            [surname] => Brown
            [age] => 33
        )

    [1] => Array
        (
            [name] => John
            [surname] => Brown
            [age] => 32
        )

    [2] => Array
        (
            [name] => Maria
            [surname] => White
            [age] => 24
        )

)

If you split your columns into arrays, you have the power to sort by any column on any direction.

Upvotes: -1

Related Questions