Sorting associative arrays in PHP with array_multisort() – New learning

If you write MySQL queries, at some point you are going to have to provide query results in a specific order. To impose any ordering in MySQL (this applies to SQL overall and is not directed only at MySQL), you have to use the ORDER BY clause. Without it, there is no guaranteed order. The database is free to send back query results in any order. As I learn PHP, I make it a point to explore both the MySQL side, along with the PHP side in regards to similar type tasks and the efficiency of each. So far in my PHP journey, I have found that arrays are used quite extensively. In this post, I’ll cover array_multisort() – one of many in-built PHP functions – used for sorting arrays. In the context of the example data for this post, the arrays are populated by an unordered MySQL query. Let’s see one example of how you can establish a sorting order in a PHP array, that is populated from an unordered MySQL query…

person laying out post-it-notes on a table
Photo by UX Indonesia on Unsplash
Self-Promotion:

If you enjoy the content written here, by all means, share this blog and your favorite post(s) with others who may benefit from or like it as well. Since coffee is my favorite drink, you can even buy me one if you would like!

Some Context

As I mentioned in the opening paragraph, PHP has many in-built functions that are used to sort arrays. I used array_multisort() for a requirement in a reporting dashboard/interface I am developing in my current (at time of writing) day job. Turns out, array_multisort() was the best choice for the particular requirement I needed a solution for. I am sure there are other PHP array sorting functions that can handle this type of sorting and I am unaware of them.

Nevertheless, I wanted to share what I learned – and hope to learn from readers – about array_multisort(). As of the time of writing, array_multisort() is the only PHP array-sorting function I have used.

The practice data used for this post is provided by this table:

phpMyAdmin table data
Table data…

Establishing a sorting order in a MySQL query is no fuss at all. Shown in the PHP code below, the SELECT query does just that, using the ORDER BY clause. The returned query results – sorted by the ‘brand_name’ column in ASCENDING order – are assigned to a $query variable for further processing:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
<?php
include DatabaseConn.php';
try {
    $query = 'SELECT shoe_id, brand_name FROM shoes_worn ORDER BY brand_name ASC';
    $shoes = $pdo->query($query);
    foreach ($shoes as $shoe => $value) {
        echo '</p><pre>'.$value['shoe_id'].' '.$value['brand_name'].'<br /></pre>';
    }
}
 catch (PDOException $e) {
    echo 'Database Error '.$e->getMessage().' in '.$e->getFile().
    ': '.$e->getLine();
}
?>

Stepping through with the foreach() loop, I echo out the ordered query results as they were established in MySQL. The output is displayed in the following code snippet block:

1
2
3
4
5
6
3 Keen Koven WP
5 Merrel MOAB Edge 2
1 New Balance 510v2
2 New Balance Trail Runners-All Terrain
6 Oboz Cirque Low
4 Oboz Sawtooth Low

What if the ORDER BY clause is removed from the SELECT query, but the data still requires an established order? Since I am using PHP arrays, I can use the array_multisort() function (Again, there are several available in PHP).

The SELECT query in the PHP code shown below does not have an ORDER BY clause like the query from the first example. Using foreach, I again step through the PDO query() results, assigning each key a matching column name (same as that in the table, ‘shoes_worn’) in the $shoes_worn[] associative array:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
<?php
include DatabaseConn.php';
try {
    $query      = 'SELECT shoe_id, brand_name FROM shoes_worn';
    $shoes      = $pdo->query($query);
    $shoes_worn = [];
    foreach ($shoes as $shoe) {
        $shoes_worn[] = [
            'shoe_id'    => $shoe['shoe_id'],
            'brand_name' => $shoe['brand_name']
        ];
    }
    $brand_name = array_column($shoes_worn, 'brand_name');
    array_multisort($brand_name, SORT_ASC, $shoes_worn);
    foreach ($shoes_worn as $shoe) {
        echo '</p><pre>'.$shoe['shoe_id'].' '.$shoe['brand_name'].'<br /></pre>';
    }
}
 catch (PDOException $e) {
    echo 'Database Error '.$e->getMessage().' in '.$e->getFile().
    ': '.$e->getLine();
}
?>

A couple of visible differences in this code as opposed to the first examples’ code block is the array_column() and array_multisort() function calls. Let’s visit sections from the official online documentation for more information about array_column and array_multisort.

array_column

Syntax:

1
array_column ( array $input , mixed $column_key [, mixed $index_key = NULL ] ) : array

Overview: Returns values from a single column of the input array.

  • I supply the $shoes_worn[] array as the first argument and the ‘brand_name’ array key as the second. In storing the results in the $brand_name variable, I now have an array of ‘brand_name’ data values which are used to establish the ordering in the array_multisort() function call (covered next).
array_multisort

Syntax:

1
array_multisort ( array &$array1 [, mixed $array1_sort_order = SORT_ASC [, mixed $array1_sort_flags = SORT_REGULAR [, mixed $... ]]] ) : bool

Overview: Can be used to sort a multi-dimensional array (among other functionality not listed or utilized here).

  • For this specific case, I supply these arguments to array_multisort():
    1. $brand_name: array of column values to perform the sorting on.
    2. SORT_ASC: Establishes the sorting order (ascending).
    3. $shoes_worn: The complete data array that is sorted according to the 1st argument ($brand_name).

Shown in the echo results below, I have imposed the same sorting order using array_multisort() as in the previous example where the ordering was imposed in the database (by MySQL) using ORDER BY:

1
2
3
4
5
6
3 Keen Koven WP
5 Merrel MOAB Edge 2
1 New Balance 510v2
2 New Balance Trail Runners-All Terrain
6 Oboz Cirque Low
4 Oboz Sawtooth Low

Works as intended.

I honestly do not have any sort (no pun intended) of metrics – with such a small data set at least – to support whether sorting on the PHP-side is more efficient than taking care of the ordering using MySQL directly. It’s no secret that unless properly optimized, MySQL queries can be a bottle-neck. Any thoughts on the efficiency of sorting in PHP from seasoned developers are most appreciated and welcome in the comments below so please share them freely. As I said in the opening section, there is likely another (perhaps better) way to sort a PHP array in this manner and I would like advice on that as well. Thanks for reading!

Like what you have read? See anything incorrect? Please comment below and thanks for reading!!!

A Call To Action!

Thank you for taking the time to read this post. I truly hope you discovered something interesting and enlightening. Please share your findings here, with someone else you know who would get the same value out of it as well.

Visit the Portfolio-Projects page to see blog post/technical writing I have completed for clients.

Have I mentioned how much I love a cup of coffee?!?!

To receive email notifications (Never Spam) from this blog (“Digital Owl’s Prose”) for the latest blog posts as they are published, please subscribe (of your own volition) by clicking the ‘Click To Subscribe!’ button in the sidebar on the homepage! (Feel free at any time to review the Digital Owl’s Prose Privacy Policy Page for any questions you may have about: email updates, opt-in, opt-out, contact forms, etc…)

Be sure and visit the “Best Of” page for a collection of my best blog posts.

Josh Otwell has a passion to study and grow as a SQL Developer and blogger. Other favorite activities find him with his nose buried in a good book, article, or the Linux command line. Among those, he shares a love of tabletop RPG games, reading fantasy novels, and spending time with his wife and two daughters.

Disclaimer: The examples presented in this post are hypothetical ideas of how to achieve similar types of results. They are not the utmost best solution(s). The majority, if not all, of the examples provided, is performed on a personal development/learning workstation-environment and should not be considered production quality or ready. Your particular goals and needs may vary. Use those practices that best benefit your needs and goals. Opinions are my own.

One thought on “Sorting associative arrays in PHP with array_multisort() – New learning”

  1. Pingback: PHP MySQL BLOB PDF: Display in Browser - Digital Owl's Prose

Hey thanks for commenting! Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.