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.