Beefy Boxes and Bandwidth Generously Provided by pair Networks
There's more than one way to do things

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

by yaconsult (Acolyte)
on Dec 21, 2010 at 08:33 UTC ( #878187=perlquestion: print w/replies, xml ) Need Help??

yaconsult has asked for the wisdom of the Perl Monks concerning the following question:

I have an sqlite DB that I generate with my script. What would be the best way to do something like this:

1) user logs in and is presented with a table of data extracted from the database for them.
2) user prioritizes this list somehow.
3) priority information saved somewhere - probably added to database.

The database stuff is working. The data changes frequently, so the table will probably just be regenerated instead of updated.

Which modules should I be looking at for the UI part? CGI, or is there something better.

Not sure how to allow the user to prioritize the list... Up and down arrows that they could click? Boxes where they enter a number for sorting purposes? Suggestions welcome!

Thank you for the replies so far! Let me add a few more details...

There will be multiple users having responsibility for different areas. The database rows related to their area are extracted from the database and need to be presented to them. They will then want to reorder the rows to indicate the priority/importance/ordering. The ordering that they impose on these rows is the information I need to capture and convey to the people that will be acting on the tasks represented by the rows.

I will check out the suggestions already made today.


  • Comment on How to allow a user to reorder rows from a database using a web form?

Replies are listed 'Best First'.
Re: How to let a user sort a list on a web page
by marto (Cardinal) on Dec 21, 2010 at 10:21 UTC

    Perl and CGI should be your backend process for this. For the frontend, if you mean dynamic sorting and table sorting you could use the jQuery datatables plugin.

      Thank you for the pointers.

      There is an excellent add-on to jQuery called jQuery UI. It has an example page that does exactly what I want. It's called Sortable and you can play with a demo here: Drag and drop the items to change their order.

      The small amount of javascript code to do all this with the jquery ui library is listed below. The question now is, how can I do this from my perl script? There doesn't seem to yet be a jQuery-UI module in cpan. Can anyone demonstrate a way to get the demo below running from a perl script?

      Examples, links, pointers would be much appreciated. Thanks!

      <style> #sortable { list-style-type: none; margin: 0; padding: 0; width: 6 +0%; } #sortable li { margin: 0 3px 3px 3px; padding: 0.4em; padding-left +: 1.5em; font-size: 1.4em; height: 18px; } #sortable li span { position: absolute; margin-left: -1.3em; } </style> <script> $(function() { $( "#sortable" ).sortable(); $( "#sortable" ).disableSelection(); }); </script> <div class="demo"> <ul id="sortable"> <li class="ui-state-default"><span class="ui-icon ui-icon-arrowthi +ck-2-n-s"></span>Item 1</li> <li class="ui-state-default"><span class="ui-icon ui-icon-arrowthi +ck-2-n-s"></span>Item 2</li> <li class="ui-state-default"><span class="ui-icon ui-icon-arrowthi +ck-2-n-s"></span>Item 3</li> <li class="ui-state-default"><span class="ui-icon ui-icon-arrowthi +ck-2-n-s"></span>Item 4</li> <li class="ui-state-default"><span class="ui-icon ui-icon-arrowthi +ck-2-n-s"></span>Item 5</li> <li class="ui-state-default"><span class="ui-icon ui-icon-arrowthi +ck-2-n-s"></span>Item 6</li> <li class="ui-state-default"><span class="ui-icon ui-icon-arrowthi +ck-2-n-s"></span>Item 7</li> </ul> </div><!-- End demo --> <div style="display: none;" class="demo-description"> <p> Enable a group of DOM elements to be sortable. Click on and drag a +n element to a new spot within the list, and the other items will ad +just to fit. By default, sortable items share <code>draggable</code> prope +rties. </p> </div><!-- End demo-description -->

        I didn't appreciate that this was the kind of sorting you were looking for, however I'm glad you've found this useful. You'll notice this example code from the jQuery page works as is, you don't need any Perl module to make it work. Using CGI (or CGI::Application or your framework of choice) you can output dynamic content. You simply include this example jQuery code in the output you send to the browser.

        I'd suggest using a templating system such as HTML::Template or Template::Toolkit to separate your Perl code from the HTML/CSS/JavaScript code. If you look at the HTML::Template documentation you'll notice the <TMPL_LOOP> tag which you could use to populate your sortable data (in the example above each row is a HTML <li>, list item).

        I'd also suggest that you benchmark performance of this method with your dataset and compare it to the performance of the datatable plugins I mentioned previously.

Re: How to let a user sort a list on a web page
by andal (Hermit) on Dec 21, 2010 at 10:22 UTC

    The question is not clear. If it is about UI, then it is best to ask future users. I don't know what are those priorities you are talking about, so how should I know which input method should be used? Take into account available to you methods for displaying UI and discuss things with the users. If you don't know who is going to use it, then choose the one you yourself like the best :)

Re: How to let a user sort a list on a web page
by jakeease (Friar) on Dec 21, 2010 at 11:36 UTC
Re: How to let a user sort a list on a web page
by sundialsvc4 (Abbot) on Dec 21, 2010 at 16:14 UTC

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

      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?


        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

Log In?

What's my password?
Create A New User
Domain Nodelet?
Node Status?
node history
Node Type: perlquestion [id://878187]
Approved by Corion
Front-paged by Corion
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others meditating upon the Monastery: (2)
As of 2022-05-18 22:19 GMT
Find Nodes?
    Voting Booth?
    Do you prefer to work remotely?

    Results (71 votes). Check out past polls.