in reply to How to allow a user to reorder rows from a database using a web form?

Simply provide a combo-box with sort-choices as part of the form.

Within your code, define a hashref that contains, for each possible valid value within the combo, the known-good SQL for an ORDER BY clause.   When you see that the POSTed data contains the sort-choice combo, try to look up that value within the hash.   If you find it, use the corresponding known-good SQL text in your next query.   (Otherwise, use some default, or generate a “Bug” error-message.)

Of course, it should go without saying that you must not use any user-provided text directly within your SQL string.   This is why the hashref, which is defined in your Perl code, contains all of the legitimate possibilities for the sort-field, and the corresponding SQL text.   (You can handle both “ascending” and “descending” sorts with an obvious variation of this technique.   Also notice that you can easily “do what I mean,” even if the actual SORT BY clause includes multiple database-columns, a mixture of ASC and DESC, and so on.)

Replies are listed 'Best First'.
Re^2: How to let a user sort a list on a web page
by yaconsult (Acolyte) on Dec 21, 2010 at 18:50 UTC
    I guess I haven't been clear enough about the sorting requirements. Perhaps an example would help...

    Let's say each row in the table represents something like a task or project.

    One person, like a manager or something, would have the ability to bring up a form in which each row represents a task/project.

    His responsibility is to decide the order in which these tasks/projects should be completed. So he's dealing with entire rows, not the individual cells or columns.

    What can I build that make it easy for him to log in, get his set of rows, easily rearrange them to the order he wants, and then capture the ordering he has specified?

    Thanks!

[reply]

      Add a SortOrder column to your database and let the manager edit the values in that column for each row. Each time you fetch the data for display append "Order By SortOrder" to the SQL.

      True laziness is hard work
[reply]

        Personally, if I were a manager, I would rather like to simply click-n-drag the individual rows up and down. Somewhat like you can rearrange Firefox' tabs, only vertically.  Having to enter numbers for this seems a little web-1.0-ish to me.

        Unfortunately, the already mentioned jQuery datatables plugin doesn't provide that functionality, AFAICT (I'd like to be proven wrong) - although it's an otherwise very neat widget.  So, if anyone is aware of some other js library which can do it, please tell...

[reply]

In Section Seekers of Perl Wisdom