Last updated December 11th, 2015 at 09:43 am
Without question, the most common operation in the Web applications I build is retrieving gobs of structured data and displaying that data in some order.
Whether the source of this data is a database, a structured file, or a script, it most often takes the shape of a multidimensional (ususally 2-dimensional) array whose first dimension is simple numeric and whose second dimension consists of associative arrays.
Let’s suppose a Web application I’m buiding involves displaying player statistics for a fictional basketball team. The data elements for each player might include the following:
last_name
first_name
dob
points
assists
rebounds
fouls
Suppose I want the users of this Web application to be able to sort and view statistics by any or all of those data elements.
If I’m storing the data in, say, a simple MySQL database table, I could excecute separate SQL commands for each sort case. Here are some typical sort cases:
Sort by name:
SELECT * FROM statistics ORDER BY last_name ASC, first_name ASC
Sort by points scored (highest to lowest):
SELECT * FROM statistics ORDER BY points DESC, last_name ASC, first_name ASC
Sort by the number of personal fouls:
SELECT * FROM statistics ORDER BY fouls ASC, last_name ASC, first_name ASC
However…
- Making repeated database connections and executing repeated SQL commands just for the purpose of displaying data in a different sequence seems sub-optimal and ecologically irresponsible. (Those who are more knowlegeable on these matters are encouraged to comment.)
- Not all data sets come neatly from a single, simple database table that supports simple ORDER BY clauses. In fact, some data sets are arrays defined in scripts.
Here’s how to sort multidimensional arrays with php array_multisort, using only ONE SQL SELECT
After doing a SELECT sorted by name, we’d end up with an array that looks like this:
$players[] = array( 'last_name' => 'Adams', 'first_name' => 'Andy', 'dob' => '1984-09-01', 'points' => '10', 'assists' => '20', 'rebounds' => '5', 'fouls' => '2', ); $players[] = array( 'last_name' => 'Bird', 'first_name' => 'Bobby', 'dob' => '1948-01-01', 'points' => '35', 'assists' => '10', 'rebounds' => '15', 'fouls' => '6', ); $players[] = array( 'last_name' => 'Carey', 'first_name' => 'Charlie', 'dob' => '1952-02-24', 'points' => '20', 'assists' => '1', 'rebounds' => '14', 'fouls' => '3', );
The trick to using array_multisort to sort a multidimensional array like this is to create a new, temporary, utility array that maps to the original array and which will be the reference for sorting the original array.
The syntax of array_multisort for this purpose is:
array_multisort($array1, $array1_sort_order, $array2, $array1_sort_flags)
where
- $array1 is the utility array
- $array1_sort_order is the order to use when sorting $array2 (SORT_ASC or SORT_DESC)
- $array2 is the original data array we want to sort
- $array1_sort_flags tells PHP how to treat the sort values (e.g., SORT_NUMERIC)
Example 1: Sort players by points, highest to lowest
So let’s suppose we want to sort $players by points (highest to lowest). We’ll create a new array (call it $points) and populate it in a foreach loop:
$points = array(); foreach ( $players as $player ) { $points[] = $player['points']; }
That will create a new array:
points: Array ( [0] => 10 [1] => 35 [2] => 20 )
A glance at the $points array tells us that if we were to sort the $points array in descending order, element[1] (with a value of 35) would be the new element[0], element[2] (with a value of 20) would be the new element[1], and element[0] (with a value of 10) would be the new element[2]. In fact, if we could freeze the reindexing process, we would see this…
//points, in an imaginary freeze-frame, after sorting but before reindexing: Array ( [1] => 35 [2] => 20 [0] => 10 )
…before we saw this, the final result of sorting $points in descending order:
//points sorted and reindexed: Array ( [0] => 35 [1] => 20 [2] => 10 )
Now we run the array_multisort fucntion:
array_multisort($points, SORT_DESC, $players, SORT_NUMERIC);
The resulting reorganized $players array looks like this:
players: Array ( [0] => Array ( [last_name] => Bird [first_name] => Bobby [dob] => 1948-01-01 [points] => 35 [assists] => 10 [rebounds] => 15 [fouls] => 6 ) [1] => Array ( [last_name] => Carey [first_name] => Charlie [dob] => 1952-02-24 [points] => 20 [assists] => 1 [rebounds] => 14 [fouls] => 3 ) [2] => Array ( [last_name] => Adams [first_name] => Andy [dob] => 1984-09-01 [points] => 10 [assists] => 20 [rebounds] => 5 [fouls] => 2 ) )
Huh? How/Why Does This Work?
This is one of those things I find easier to do than explain. And I’ll admit it took me a while to get my head around this. (Hat tip to Chris Shifflet who unlocked the door with this post from 2011.)
In each case, what we’ve done is create a “parallel” utility array that has the same number of top-level elements as the original array we want to sort. Both arrays have numeric indexes starting at zero. When we call the array_multisort function, what we’re saying is this:
- Sort $array1 according to the $array1_sort_order.
- Note how the numeric indexes are rearranged.
- Now sort $array2 such that its numeric indexes get rearranged in the same order as the numeric indexes of $array1.
It’s as if we’re connecting each array element of $array1 to each array element of $array2 with a rigid steel bar. Then, as we move the elements of $array1 to fall into the new sort order (e.g., moving 35 points — whose index is 1 — up to the first (i.e., zeroth) position in the first example), the corresponding 1 element of $players (Bobby Bird) gets dragged up to the top spot (zeroth) spot of its array.
If someone has a better explanation of this array_multisort phenomenon — or a better way to accomplish this result, for that matter — please comment below.
Here are some more examples.
Example 2: Sort players by rebounds, highest to lowest
$rebounds = array(); foreach ( $players as $player ) { $rebounds[] = $player['rebounds']; }
Now:
array_multisort($rebounds, SORT_DESC, $players, SORT_NUMERIC);
Example 3: Sort players by fouls, lowest to highest
$fouls = array(); foreach ( $players as $player ) { $fouls[] = $player['fouls']; }
Now:
array_multisort($fouls, SORT_ASC, $players, SORT_NUMERIC);
Comments Welcome
As I said, I welcome comments. If you have a better explanation for how array_multisort works — or a better way to accomplish the end result — I’d like to hear from you. And if this article helped you finally wrap your head around array_multisort, I’d like to hear that, too.
Leave a Reply