PHP 5.2 Function needed for GENERIC sorting of a recordset array

Question

Somebody must have come up with a solution for this by now. We are using PHP 5.2. (Don't ask me why.) I wrote a PHP class to display a recordset as an HTML table/datagrid, and I wish to expand it so that we can sort the datagrid by whichever column the user selects. In the below example data, we may need to sort the recordset array by Name, Shirt, Assign, or Age fields. I will take care of the display part, I just need help with sorting the data array.

As usual, I query a database to get a result, iterate throught he result, and put the records into an assciateiave array. So, we end up with an array of arrays. (See below.) I need to be able to sort by any column in the dataset. However, I will not know the column names at design time, nor will I know if the colums will be string or numeric values.

I have seen a ton of solutions to this, but I have not seen a GOOD and GENERIC solution Can somebody please suggest a way that I can sort the recordset array that is GENERIC, and will work on any recordset? Again, I will not know the fields names or datatypes at design time. The array presented below is ONLY an example.

UPDATE: Yes, I would love to have the database do the sorting, but that is just not going to happen. The queries that we are running are very complex. (I am not really querying a table of Star Trek characters.) They include joins, limits, and complex WHERE clauses. Writing a function to pick apart the SQL statement to add an ORDER BY is really not an option. Besides, sometimes we already have the array that is a result of the query, rather than the ability to run a new query.

Array
(
    [0] => Array
        (
            [name] => Kirk
            [shrit] => Gold
            [assign] => Bridge
        )

    [1] => Array
        (
            [name] => Spock
            [shrit] => Blue
            [assign] => Bridge
        )

    [2] => Array
        (
            [name] => Uhura
            [shrit] => Red
            [assign] => Bridge
        )

    [3] => Array
        (
            [name] => Scotty
            [shrit] => Red
            [assign] => Engineering
        )

    [4] => Array
        (
            [name] => McCoy
            [shrit] => Blue
            [assign] => Sick Bay
        )

)

Answer 1

I suggest you do it in the database. Otherwise, you can use this:

function sortby(&$arr, $column) {
    usort($arr,
        function ($a, $b) use ($column) {
            return strcmp($a[$column], $b[$column]);
        }
    );
}

Answer 2

Well, first off, you should be sorting in the database (it's more efficient). If not

In php 5.3+:

function getSortCommand($field, $sortfunc) {
    return function($var1, $var2) use ($field, $sortfunc) {
        return $sortfunc($var1[$field], $var2[$field]);
    }
}

in php <= 5.2, you'll need to create a pure function:

function getSortCommand($field, $sortfunc) {
    return create_function('$var1, $var2', 'return '.$sortfunc.'($var1["'.$field.'"], $var2["'.$field.'"]);');
}

Usage for both:

$array = usort($array, getSortCommand('name', 'strnatcasecmp'));

To adjust the field, you just change the $field parameter. To change the sort function, just change the $sortfunc parameter...

Answer 3

You should definitely do the sorting when querying the database.

You do this by adding a link like ?orderby=name&dir=asc to the <TH>s

Then when you query the database you check for the existence of $_GET['orderby']. If it exists you add an order by clause.

A clientside option is http://tablesorter.com/docs/