Best practices for sortable columns

[newtophp2000]

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
again.

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?

Thanks!

[Peter Fox]

Following on from 's message. . .

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
again.

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?

Thanks!

ANSWER No 1
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
like
Sorted by [Name v] [x] Ascending [go]
ANSWER No 2
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".
ANSWER No 3
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.
eg
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
pe******@eminent.demon.co.uk.not.this.bit.no.html
2 Tees Close, Witham, Essex.
Gravity beer in Essex <http://www.eminent.demon.co.uk>

[Richard Levasseur]

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
direction.
$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);
$query->toString();
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.

[Jerry Stuckle]

ne**********@yahoo.com wrote:

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
again.

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?

Thanks!

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, http://www.utopiapimp.com (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.
js*******@attglobal.net
==================