Sortable Rows with Data Assist

Background

This tutorial began based on work done by Brial Michel. His tutorial can be seen here. I want to thank him for the work he did on this which gave me a jump start on my project.

I frequently use and customize Adobe Dreamweaver Developer Toolbox (ADDT) previously InterAKt Kollection Pro for my custom built content management systems. As usual, for every good thing Adobe puts on the table, they have to take a good thing off. In this case, it is ADDT which will not receive any further development and has to be hacked to be compatible with PHP 5.3.  

Even though my ADDT is hacked to work with PHP5.3, I wanted to start building whole projects using WebAssist's DataAssist (WADA). I have usually used WADA for front end website items and occasionally back end custom CMS when WADA was, for one reason or another, better suited to the job.

The primary features that ADDT has over WADA:  

1. Management based on a recordset or table whereas the WADA code has to be modified to create recordset management;
2. The ability to sort lists based on a sorting column.

There are other benefits that ADDT offered, and a few draw backs. It is heavy code, sometimes slow to load, but the power and ease of the triggers and transactions was a pleasure to work with. But enough reminiscing.

Being unable to allow the end user to reorder lists to appear however the end user wants rather than just by date or id or alphabetically is a deal breaker for me, so I was looking to add that capability to WADA. The method Brian posted is a good start. I want to build on that.

Brian's method needs to be expanded to accomplish the following:

• The method does not work when the data is paginated. If you only show 10 records per page and you are on the third page, the presented method will reorder records 21-30 to be 1-10 and there will be two sets of 1-10.
  
  
• The method also does not work for filtered recordsets. If you have a list of items, and some are left handed while others are right handed, and you are looking at only the right handed items, the reordering method will not take that into account. This is particularly important for me since I do this a lot. This would also apply to WebAssist Search Results.
  
  
• Lastly, if the data is paginated, there has to be a way to move the top and bottom records to the corresponding pervious or next page.

On top of this, I wanted the method to require a minimum amount of modification to the page as I will have to do this a lot until WADA adds these capabilities. I reasonably accomplished this by wrapping the necessary functions in a class and creating code snippets in Dreamweaver. I have it down to about 4 edits.

You can view the demo here: http://www.blazingimages.com/demos/WADAreorder/

The basic steps to accomplish this included:

• Keeping track of the original order fields. If the recordset is filtered, the records in the list might be ordered 1,3,7,9,10... so you need to save and reuse those numbers in order to not interfere with the unordered or differently order records. In other words, this preserves the ability to reorder the left handed ones and not involve the right handed ones.
• Getting the page and the query information, so the top and bottom records can be move to the previous or next page.
• Adding the ability to change the number of records displayed mostly as a feature, and as an alternative way to move records across the pagination.

This could be done with POST or SESSION values. I chose SESSION values because it fit my current needs, but I may try to add a POST option in the future along with a couple of other enhancements if I need them.

How To:

As Brian mentioned, you need to download the latest version of JQuery: http://jquery.com/

You'll also need to download the Table Drag n Drop plugin found here:

http://www.isocra.com/articles/jquery.tablednd_0_5.js.zip 
(see Table Drag and Drop JQuery Plugin)

UPDATE: These files have been updated for the newer version of DA in DataBridge which has a form tag wrapping the entire results page. It is not fully tested especially with image buttons:
DataBridge_DAreorder.zip

Last you will want these two files: DAreorder.zip
NOTE: updated on 7/27/11.

First include the php include:

This does not count as one of the 4 steps I mentioned.

1. Next, instantiate the class before the WADA recordset. Change the max rows if you want to use the feature to allow the end user to choose a different number of rows to display.

(Click to enlarge image)

 The format of the instantiation is:

2. Next feed the class some information it needs from the WADA recordset:

(Click to enlarge image)

The format for this function is:

Notice in the image, these values come right out of the recordset creation.

3. Next insert the head code:

(Click to enlarge image)

Be sure to include the jQuery files. The insertJS function only takes the path to the reorderDA.js file. Don't include the file name, just the path to the file. A relative path is fine.
The classes below the function are available to style the rows during drag and drop. The main benefit of this is that, once dropped, the column can remain a different color, so the user can see there has been a change.

4. Adjust the table:

(Click to enlarge image)

Add a unique, incremented ID to the table row. This is necessary for the css table effects. Insert the sortArrays funtion code in a legal space within the table. By legal, I mean that it outputs some javascript, so don't place it between the </td> and the </tr> or something like that.

That is it. It should work now. The class default to including the select number of rows to display form. You can change the choices by reassigning the array values. The default is: $reorder->rowChoices = array(5,10,15,20,25); If you want to exclude the form to change the number of rows to display, simply change the value to false.

Depending on my needs and how long it will be before WebAssist comes out with a more complete substitute for ADDT, I may add some additional functionality, like a choice between SESSION and POST, the ability to use arrow buttons to reorder rows.

I hope this helps someone. I have already put it to use.

UPDATE 10/10/10: I was asked by Sades on the WebAssist forum about inserts and updates which I see is not addressed in the tutorial. Here is that information.

The database table needs to include a sorting column obviously. I use "sortorder" Joomla! for example uses ordering. Just remember "order" is a reserved word.

For updates, just don't change the field.

For inserts, what you need is a recordset with this SQL: "SELECT (MAX(mysortcolumn)+1) AS num FROM mytable"
(replace mysortcolumn and mytablewith appropriate values)

The resulting recordset row field ("num") will be the increment value you want to insert into the new record.

If you have concerns about multiple people making edits at the same time, you need to implement some for of record locking scheme not addressed here.

I'm going to refer to 2 approaches you can take, the second being better if multiple users maybe making changes, but that does not solve your problem without a record locking scheme because someone updating a record can conflict with someone resorting the records.

Method 1, submit the recordset mentioned above as a hidden value and use it for your sort column value on the insert transaction.

Method 2, insert the new record allowing no value for the sort column. It should default to 0. create the recordset for the new increment value after the insert, and add an update transaction using the session value set by the insert transaction to identify the record id and the new recordset as the value of the sort column.

The second method is a couple more steps and perhaps some hand coding, but it minimizes the time from insert to setting the sorting column's value.

In any case, you MUST make sure that the minimum value you submit is 1. The SQL statement provided returns no records on you very first insert, so test against the number of rows retrieved (0 on the first record), or account for this in the insert tranaction, but just be aware of the fact that the first record entry will fail and screw the whole thing up unless you make sure it is 1.

Do not use COUNT instead of MAX. This scheme does not recreate the order when records are deleted. If you have 4 records and the sort values 1,2,3,4 and you delete record 3, COUNT+1 will return 4 which still exists.

UPDATE 7/27/11: I updated the download files today. The main motivation was that I discovered a bug if you wanted to show all files and entered "false" for the row selection. Soooo the only change I made to the class was a small change in the getMax function, but when I compared the file I have been working with to the one available for download here, it looks like I made some other tweaks that never got uploaded. In other words, one bug fix and "miscellaneous improvements". One issue I noted was that I referenced the SESSION variables through GLOBALS in the newer version and that could be important. I also use reorderDA.class.php now instead of reorderDA.inc.php, so the zip file contains both file names, BUT THEY ARE IDENTICAL. The reason both files are included is so that the tutorial will work as written without having to explain that the file name is different, but I recommend substituting reorderDA.class.php for reorderDA.inc.php.

UPDATE 9/22/11: Dizzy asks how to ensure the first row's value is 1. Obviously, method 1 above is easiest, so that is what I addressed:

There are a couple of ways to ensure the first record is not 0. First, if you applying this to an existing table, add the field "sortorder" and run the SQL statement "UPDATE mytable SET sortorder=id WHERE 1=1" on the table.

If there are no records in the table, and assuming you are using WA to do the insert, select the recordset value "num" from the recordset you created as described above. WA will put that in as . Change that to use a conditional statement like the notation that WA uses for POST values (e.g. <?php echo (isset($_POST["title"]))?$_POST["title"]:""; ?>) except we will test against either the number of rows in the newsort recordset OR the value of num and make the default 1.

So it would look like this:
  OR.

You can also shorten this up as <?php echo (($row_newsort['num'])?$row_newsort['num']:"1"); ?>.

This is showing you what it should look like in the WA GUI. It changes the statement in the code block of the insert transaction.

(It turns out, I think I misunderstood what Dizzy was asking, but this seemed like a helpful update anyway.)

UPDATE 9/26/11: Dizzy has offered some code for getting an initial value into a Many-To-Many (M2M) relationship table when using WA DA's Manage Relational Table behavior. It can be used to later put the M2M values filtered by the master key into a WA list and use this code to resort them. The code and turorial are here: Webassist Manage relational tables and setting the order.