Best practices for sortable columns

Do you have a question? Post it now! No Registration Necessary.  Now with pictures!

Threaded View
I have several forms that display information from the database after
users log in.  I would like the column titles to be sortable so that
when the user clicks on a column heading, the data re-displays in
sorted order of that column.  Next time they click on it, it toggles
the order from ascending to descending, and then back to ascending

Since I have many forms and each form has different columns, I was
wondering if there was a method that would be better than a brute force
approach.  What are the best practices for this functionality?


Re: Best practices for sortable columns

Following on from 's message. . .
Quoted text here. Click to load it

You may have noticed that you don't often see this style of UI in web  
pages.  The main reason is that users need to be educated in how to use  
the method and (even with systems they use everyday for years) don't  
seem to cotton on very quickly to the tricks often available with column  
headings.  The secondary reason is the delayed response when requesting  
a new database query and web page.  "I just clicked on the heading  
hoping it would do something useful but I can't see any effect".

So web pages tend to use the more clunky (but (a) perfectly valid and  
(b) much more obvious) method of a drop down box(es) saying something  
Sorted by [Name v]   [x] Ascending [go]

You _may_ find  search a more productive method than sort+browse.  
Depending on the nature of your data and the  calibre or your audience  
you _may_ want to take them through a menu of
   * - select this way
   * - select that way
   * - select the other way
It is really really really REALLY important to get all users actually  
using the system on their own without either giving up or making a hash  
or trying to get technical support.  After all you (or perhaps the  
system designer) should know what people want to use the system for and  
farting around with every column under the sun is a waste of everybody's  
time.  Proper analysis should reduce "I want to look at the data" to  
"which jobs are late" or "which of my jobs are late" or "my jobs" or  
"job No X".

There are two steps to displaying lists
(1) Selecting from the database
(2) Displaying a table of results

Item 2 is easy to package.  2-dim array to 2-dim table with column-based  
function for say converting dates and adding links.

Item 1 can normally be done with one or two switch structures.  These  
tend to be pretty obvious when looking at the code and pretty specific  
to the option chosen.
switch (sortmode) {
case 'name'
   $fields = 'name,age,status';
   $where = '';
   $sort = 'name';
and these variables get stuffed into a select.

What I personally have done is to create a class which takes a SQL  
statement to fetch X records and then (as required) displays them with  
all sorts of column variations and headings.  The SQL is 'hand crafted'  
but the display is half a dozen bog-standard lines.

So, to summarise:  You might want to rethink your UI.  Displaying is a  
mechanical thing that can be stuffed into a class[1] but setting-up SQL  
is more easily done in-line with a few switches.

Well, that's /my/ experience.

[1] With the benefit that each time you add functionality to the class  
you can re-use it on other projects.

PETER FOX Not the same since the poster business went to the wall
2 Tees Close, Witham, Essex.
Gravity beer in Essex  <

Re: Best practices for sortable columns

I often have this requested as a feature of the things i write.
I've yet to come up with a simple elegant solution.

Most often i simply hack it in.  Pass a ?sort=name&dir=asc from a link,
store that in a session, and append a ORDER BY clause to all the
relevent queries.
It is a pain, but it gets the job done.

Other solutions:
Create a SortBy array and have each element an array of column name and
$sort[0] = array('col'=>'first_name', 'dir'=>'ASC');
Then implode it and concatenate it.  Messy, but a quick fix if you have
a lot of subsorts needed.
Extending that, create a SortBy class, and append that into all the
relevent queries, propagating it through $SESSION.
$sort = new SortBy($col, $dir);  $sort->addCol($col2, $dir2);
$query = "select bla bla $sort->toString()
This lets you easily manipulate the sorted direction and allows more
control over the sort.  Basically the previous thing but with a
friendly interface you can reuse in other projects.

My only other solution is to abstract the query so that you can do
whatever the hell you want to it.
$query = new Query();
$query->Select($col1, $col2);
$query->SortBy($col1, $dir);
Granted, that requires a lot of work, but is the easiest one once its
all said and done.
In fact, i think with the PDO objects you can do just that.  I'm not
sure, though, I haven't really looked at those.

Finally, often enough they say they want sort, but they will never use
it.  Really they just want to see specific results in specific ways.
I would say wringing as many details for reports as possible out of
them is a better way to go.  Its easier for you to do, and they'll like
the final results better.

Re: Best practices for sortable columns wrote:
Quoted text here. Click to load it

Another alternative is to do the sort in JavaScript (although it won't  
work for those who have JavaScript turned off).  One of the sites I've  
seen, (a RPG support site) does this.

To see an example, go to the Home Page and click on "Demo 1". Then click  
on the "General" after "Column Sets:".

This page uses JS to do the sorting.  Just click on the column headers  
such as "Acres", "NW", etc. to see what happens.

A very usable UI, IMHO.

Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.

Site Timeline