Suppose you have a many-to-many relationship and you use WebAssist DataAssist (WADA) to manage one of the tables, but you want to allow the end user to filter these events based on the many-to-many relationship? This tutorial will guide you through adding that capability. At the time of this posting, I did not find a tutorial for this in the WebAssist website.

If that was not clear, perhaps the example will help. Lets assume you have a list of articles and a list of publications. Many of your articles are published in more than one publication. You manage this with a many-to-many table that connects the two. If that isn't clear, Google "database many-to-many relationships." You have a results page that displays the articles, but you want to allow the end user to filter the results based on a specific publication.

You can view the demo of this here: http://www.blazingimages.com/demos/WADAm2msearch

To do this, first you create your database with the relationship. You can get the sample data for this demo here.

To do this, first create your table management using WADA's wizard. In this case I only created the results and search pages. I also chose to include the search form in the results page, so all the changes could be in the same script, but this can easily be adapted to the external search form as well. If the relationship is the only thing you want to search on, your best choice here is to include some field for the search in order to generate the rest of the search form behaviors and then remove the unwanted fields after you add the many-to-many search.

With the results page created, the first thing we need to do is modify the query for the results table. In the example, the original query came out like this:

 

We need to join the the many-to-many table to the query and include DISTINCT into the query so that we do not get duplicates of the same article when no filter is applied. Without DISTINCT, one row will appear for any given article for each publication the article is in.

Click to enlarge the image. The modified query reads:

"SELECT DISTINCT articles.id, title FROM articles LEFT JOIN (articles2publications) ON (idarticles=articles.id) ORDER BY id ASC"

Notice that I specified articles.id in the query. I used id for the primary key column in all the tables. If you have the same column name, you need to specify which table you are referring to. If they ar all unique you don't. Another place this can go wrong is if you use unique id names like idarticles, but also name the connecting column of the many-to-many table idarticles.

Next you need to modify the search behavior code. The original code read:

$WADbSearch needs another array value to include a search for the many-to-many table. In this example, the join statement connects the many-to-many table on the article id, but does not put any output into the recordset, so you need to know the table structure because we want to filter on the publication id. The produces the query that says show all articles that are in the specified publication. The many-to-many table uses idarticles and idpublication. In this case, I would not need to specify the table in the where clause, but I have included it anyway to demonstrate that you can include it in the WADA Search behavior.

Click to enlarge the image. S_publications will be the name of the search form input we are going to add. Notice the name of the function is different. WADA Search includes:

• addComparisonFromEdit for textbox inputs
• addComparisonFromCheck for checkbox input and
• addComparisonFromList for dropdown and multi-select inputs.

Many-to-many tables connect ID's, so the third parameter NEEDS to be "=". Finally, the last parameter is 1. That last parameter has to do with the type of qualifier that is placed around the statement. 0 is for text comparison (e.g. name='joe'). 1 is for numeric comparisons on numeric fields (e.g. id=1), and 2 is for dates.

Finally, create a recordset of the table on the other side of your many-to-many relationship. In this case that is publications. The recordset is used to create a dynamic list of the publications that the end user can filter on. Add a row to the search form and modify it to add the new search element, in this case S_publications. Be sure to include a non dynamic fist element that has no value to allow all your relationship elements to be included. In this example, that would translate to displaying all articles regardless of which publications they are in.

Click on the image to enlarge it.

That is it. Try the demo to see how it works.

 

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:

$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:

(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:

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