如果在SQL中没有想要的排序字段时如何对jqGrid进行排序？

Question

When we use jgGrid, like this:

get_items.php =>

$SQL = "SELECT * FROM items ORDER BY $sidx $sord LIMIT $start , $limit"; 
//$sidx is the index row.  
//ie. $sidx = 'name';

$result = $mysql->query( $SQL ); 

header("Content-type: text/xml;charset=utf-8");
$s = "<?xml version='1.0' encoding='utf-8'?>";
...
//balah balah
...

while ( $row = $mysql->fetch_array( $result ) ) {
...
//Note the getName method.
$s .= "<cell>". getName($row['name']))."</cell>";
...
};

echo $s;


function getName ( name ) {
   if ( name == 'Lane' ) return 'Brian Lane';
   if ( name == 'Kerwin' ) return 'Diane Kerwin';
   ...
   ...
}

Now the question is :

if I sort the jqGrid by ASC, it will show like Diane Kerwin, Brian Lane as Kerwin is before Lane.

How to sort the fields like Brian Lane, Diane Kerwin by ASC?

Answer 1

Preferred solution

It looks like only the last names are stored in the database. To be able to sort by first name in your SQL query, you'll need to update the name fields in the items table to contain both first and last names. This would be much better — keeping a separate data set embedded in your code will probably cause problems at some point later on.

---

If you must:

If this isn't a possibility and you must keep the if statements for each name, you could map over the result set to fetch the names and then sort. (The example below requires PHP >= 5.3.)

$SQL = "SELECT * FROM items ORDER BY $sidx $sord LIMIT $start , $limit";
$result = $mysql->query($SQL);

$rows = array();
while ( $row = $mysql->fetch_array($result) ) {
    switch ( $row['name'] ) {
        case 'Lane': $row['name'] = 'Brian Lane';
        case 'Kerwin': $row['name'] = 'Diane Kerwin';
    }

    array_push($rows, $row);
}

usort($rows, function($a, $b) { return strcmp($a['name'], $b['name']); });