Sortable Rows with Data Assist

PDF Print Email

 

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:

 img001

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

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.

 Instantiate the class and set the max rows.

(Click to enlarge image)

 The format of the instantiation is:

$reorder = new reorderDA($database, $connection, $table, $sort_column, $key_column, $default_display_rows, [$return_page if left blank, assumes WADA default tablename_Results,php]);  ($reorder is just the variable name I chose to use).

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

Feed the class some information.

(Click to enlarge image)

The format for this function is:

$reorder->setQueryData($query, $totalRows, $totalPages, $pageNum);

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

3. Next insert the head code:

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:

Adust the display 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.

 


Comments
WD
-
2010-04-26 at 09:06
Given WA's current treatment of their long term customers, I switched to PHPMaker. It has sortable rows built-in, security, tons of options, and produces pure PHP code that can be modified easily by DW or any other editor. No more worries about extensions crashing DW either.
-
2010-09-15 at 03:23
Your demo sets the order to zero. I think this is due to a gap in the php in the instantiate the class before the WADA recordset. Otherwise, great solution and thanks :-) B
-
2010-09-15 at 23:28
I've been using it extensively, so know it works. I'll take a look at the demo in the next day or two and track down the bug.
-
2010-09-16 at 18:11
Well, I didn't look at what you were describing before updating, but I did see the row order values were all zero. I updated the files anyway to be sure I didn't accidentally have some old code in there, but it seems to be working fine. Can you recreate the error? I know that the original bug had to do with when I cleared the session data resulting in the submitted values for the order being empty under certain conditions, but that should be fixed.
Sades
-
2010-10-11 at 15:57
worked like a charm ill soon have my website ready with some other webassist tutorials and i wanted to ask if i can reffer to yours?

Sades
-
2010-10-12 at 01:40
Absolutely. I'm glad it was helpful.
Hans
-
2010-10-21 at 04:23
I've to comment out row 32 and 33 in reordeDA.js (otherwise I got a script error: "TypeError: Result of expression 'idreorderArray[0]' [undefined] is not an object"), then all works , except the update Row order does not saves it. Am I overseeing something? I followed exact the instructions above!

Any help MUCh appreciated!
Hans Haverlach
-
2010-10-21 at 06:14
Never mind, i redid everything and now it works. Sorry for troubeling you!
Hans
Steven
-
2010-10-21 at 10:38
Glad you got it resolved.
DL
-
2011-01-03 at 17:54
Works great. Thanks for this much needed tutorial. Hopefully WebAssist will incorporate it into DA for the next release.
Dan
-
2011-07-27 at 14:45
I have used your tutorial about 5 times now. Works perfect every time. I was a longtime user of ADDT and have missed the ability to reorder records. Now I have been using WebAssist happily for the past 2 years. Thank you for this much needed modification.
Carol
-
2011-08-04 at 22:34
Thank you for this, wish webassist data-assist gets some function that adds this kind of stuff thumbs up, thank you so much, from a Colombian designer Girl kisses
-
2011-09-22 at 07:26
Absoloutly brilliant, good work fella!!!
Have a question though, how would i go about setting the initial sort order value on a realational table??
Steven
-
2011-09-22 at 14:08
I just posted an update to explain that further. Thanks for the question.
Steven
-
2011-09-22 at 17:56
Hi Dizzy,

Sorry, I did not read your original question correctly. I missed the relational table part.

I saw your post at http://www.webassist.com/forums/showthread.php?p=110322&posted=1#post110322 on your new raltional table method.

I look forward to your post on this code. It could be quite helpful.

To answer your question, I use it on relational tables all the time by hacking WA so the list page (WA calls Results) is a filtered recordset. You have to go through and ensure you pass the filtering ID through the Insert, Update, and Detail pages. For example, if I understand what you are doing, I have a number of image galleries. I use a WA DA wizard set of pages to manage the galleries. I include the sort order modification to that page.

Next, I add a button to the button row from WA (Details, Edit, Delete becomes Images, Details, Edit, Delete). Then I make a set of WA DA wizard set of pages for the images. Add the WHERE portion to the recordset, and pass the MasterKey in the form url's and links. It is about 10 edits I think, but still a bit of a pain. Then I apply the sort method to those pages.

On menus and submenus, I do the same thing one one table where parentid = 0 means it is a main menu item.

I look forward to seeing how that compares with what you are doing in the code you posted.
Tony
-
2012-02-20 at 07:22
Is there a .zip of sample pages/db I can download to see a working solution?
Steven Berkson
-
2012-02-20 at 12:33
Tony, I'm sorry, but I don't think there is. You can look at the demo, and I tried to provide step by step details, but I do not think that I can redistribute WA files publicly (free download). I'm pretty sure that is a conflict with their copyright. I'll ask though.
sades
-
2012-08-09 at 09:19
Hello since the new databridge im getting the following error

Timestamp: 09/08/2012 11:48:36 a.m.
Error: TypeError: $(".WADAResultsTable").tableDnD is not a function
Source File: http://localhost/adm/reorderDA.js
Line: 66

and does not work any idea how to get it working again?
Steven
-
2012-08-11 at 16:32
Regarding the tableDnD error, that appears to be connected to the update. It looks like the tableDnD JavaScript file is not included or included too late in the page.
Leave a Comment
Captcha
Enter text shown in left: