Dynamic Search SQL Extension Suite: Buy Now for $25

*Also works in Dreamweaver MX and DW MX 2004!

Check tom-muck.com for other DW MX 2004 extensions

The Dynamic Search SQL server behavior is designed to work hand-in-hand with existing Dreamweaver MX server behaviors, like the Recordset and the Repeat Region. This server behavior will enhance the core set of Dreamweaver MX or UltraDev server behaviors by adding dynamic search functionality to arsenal. The server behavior assumes you are using standard Dreamweaver MX or UltraDev recordsets and repeat regions. It does NOT work with hand-coded recordsets or loops.

This extension works in the following languages:

ASP/VBScript

versions for CF and PHP are available for purchase as well.

Advanced Searching Using Dreamweaver MX or UltraDev

There is no easy way to create a search page using dynamic queries in Dreamweaver MX and UltraDev. There are frequently times when you want to create a search page that will allow a user to enter multiple words, or to allow searches across multiple columns. This extension allows you to do both, by dynamically creating a SQL statement on the fly depending upon what the user types in.

Using this extension you can search across multiple database fields with multiple search words. The words can be separated by commas, spaces, or the word "and". You can also use the word "or" for searches that work with ANY of the words in the search box.

Assuming you're searching a job database for all jobs that match the search terms asp, vb, and sql, a search can look like this:

or

or

OR you can do something like this:

You can also search multiple word phrases by using quotes:

The extension also comes with an advanced option which allows you to use radio buttons or a dropdown list to allow searches for any word, all words, or exact phrase, as in the following form:

OR like this:

The extension will rewrite the recordset on your page to use the new dynamic SQL string.

Features include:

• 3 sample search forms included that can be inserted into your page
• Ability to work with one simple search field, or with either of the options shown above (radio buttons, listbox)
• Ability to work across multiple database columns
• Works with a search form on the same page as the recordset OR a search for on a completely separate page.
• Includes a server behavior called Accented Search Words which you can apply to the results page to highlight the search words

The pre-requisites to use this extension are as follows:

• Add a recordset to the page (MX compatible recordset)
• Add a search form to the page (form, text field, and submit button). The extension also comes with three types of search forms that you can add to your page
• Add a repeat region to the page to display the search results.

With that in place, you can use the Dynamic Search SQL server behavior.

Applying the Server Behavior

The steps to apply the server behavior for a basic search page with one search field are as follows:

1. Open the server behavior panel and choose

   Server Behaviors >> DW Team >> Search >> Dynamic Search SQL

That brings up the following dialog box:

2. Choose the recordset name of the recordset on the page.
3. Choose the recordset fields that you want to search on by clicking the plus sign (+) for each column
4. Choose the form field that is the search field for the recordset. This can be on the same page or a different page.
5. Click OK

After clicking OK you should be able to save the page and browse it. Test it with different types of search strings.

Using the Advanced Options

If you are using a form with radio buttons or a list box to allow for other search options (any word, all words, or exact phrase) you can use the advanced tab. To add some advanced features to the page, follow the 5 steps above, and then click the advanced tab.

6. Click the Advanced tab to bring up the following dialog box:

7. Fill in the form field that is your "Any words" field (this can be a radio button, listbox, or other form field)
8. Fill in what this form field contains if the user chooses it as an option. For the radio buttons shown in the example above, the radio buttons all have the same name (anyallexact) and have different values for each button.
9. If you have an option for "Exact Phrase" you can add the form field name that you are using for this (again, in the example above we are using the name anyallexact.)
10. Fill in the value that this form field contains if the user chooses it as an option.
11. The checkbox for "Return NO records if no search parameters" allows you to determine whether an empty box returns ALL records (default) or NO records (checked).
12. Click OK to apply the server behavior.
13. One other advanced option (NOT SHOWN) is a checkbox which allows you to KEEP any where clause that you have already defined in the SQL. The server behavior by default will REMOVE any where clause in your SQL to allow the dynamic search sql string to be inserted into your SQL statement automatically. By clicking this option you can use your existing where clause in conjunction with the dynamic search sql string that this server behavior generates. An example might be the following:

"SELECT * from mytable WHERE accesslevel = 'web'"

If you didn't use the checkbox option, your where clause would be stripped from the SQL statement to resemble the following:

"SELECT * from mytable " + tfm_SQLstr + ""

By using the checkbox option, you can keep both parts of the where clause:

"SELECT * from mytable " + tfm_SQLstr + "AND accesslevel = 'web'"

Other Server Behaviors in the Package

ALSO INCLUDED is a server behavior that highlights the search words in a database result field. You can apply the server behavior to the field that is the result. The dialog box looks like this:

You simply choose the recordset name, the field that you want to utilize it on, the name of the search field, and any tags or code that you want to insert before and after the match. Typically you would use a <strong></strong> tag around your search result. A search on a job description field of the words asp, sql, vb might return a result that looked like this:

You can see that the replacement is versitile in that it maintains the CASE of the words that are searched on and also allows MULTIPLE word matches.

One last server behavior in the package is named Debug SQL String. It allows you to display the dynamic where clause that is created. Simply drop it into the page and you can display the where clause string that is created by the server behaivors. The SQL statement that produced the above results would have looked like this:

SELECT JobLocation, JobDescription FROM mytable
WHERE
((JobLocation LIKE '%asp%') OR (JobDescription LIKE '%asp%'))
AND
((JobLocation LIKE '%vb%') OR (JobDescription LIKE '%vb%'))
AND
((JobLocation LIKE '%sql%') OR (JobDescription LIKE '%sql%'))

The text that is in bold is generated by the Dynamic Search SQL server behavior and would appear on your page as you are debugging your search query. If you chose the "Any words" option, the dynamic SQL would look like this:

SELECT JobLocation, JobDescription FROM mytable
WHERE
((JobLocation LIKE '%asp%') OR (JobDescription LIKE '%asp%'))
OR
((JobLocation LIKE '%vb%') OR (JobDescription LIKE '%vb%'))
OR
((JobLocation LIKE '%sql%') OR (JobDescription LIKE '%sql%'))

This is a fairly simple statement, but you can imagine what would happen if the user entered 6 or 7 words in the query and you are searching across 3 or 4 columns. The server behavior will take care of these situations as well.

There is an FAQ page here.

NOTE: If you are using the Simple recordset, the recordset will not be editable in the Simple recordset dialog box any more -- always edit your recordset from the Advanced dialog box to avoid any problems.

NOTE: The server behavior works by using dynamic SQL. Therefore, it won't work with a stored procedure. It works well with standard recordsets created using tables or views.

Dynamic Search Suite for ASP/VBScript Buy Now!