Dynamic Search Extension Suite for ColdFusion

Price: $32.00

*Works in Dreamweaver 6 and higher

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 server behaviors by adding dynamic search functionality to arsenal. The server behavior assumes you are using standard Dreamweaver MX recordsets and repeat regions. It does NOT work with hand-coded recordsets or loops.

This extension works in the following languages:

ColdFusion

versions are available for purchase for ASP/VB, ASP/JScript, and PHP as well

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

Basic search pages are easy to create in Dreamweaver using a filter on a recordset. Dynamic keyword searches, on the other hand, are not.There is no automated way of creating a search page that uses a dynamic query in Dreamweaver. 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:

Search:   all words:   any of the words:   exact phrase:

OR like this:

Search:     All words Any words Exact phrase

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 (Dreamweaver 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 typical results section to the page with a repeat region and table to the page to display the search results. Details on creating Repeat Regions and dynamic tables can be found in the Dreamweaver help menu.

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

Basic Steps

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 >> Tom-Muck.com >> 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 the page with different searches.

Advanced Options

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

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.

* Steps 7 - 10 can be filled in automatically by the server behavior by clicking the Default button, which fills in default values that coincidewith the search forms that come with the package.

11. The checkbox for "Leave Where Clause Intact" allows you to create an existing filter (such as SELECT * FROM Mytable WHERE myUsername = 'someVariable') and then retain that filter in the search. The extension will automatically write the correct SQL statement to maintain your WHERE clause. If you are using this extension to replace a pre-existing search filter, you can leave the box unchecked to have your WHERE clause replaced by the extension.
12. The checkbox for "Return NO records if no search parameters" allows you to determine whether an empty search field returns ALL records (default) or NO records (checked).
13. Click OK to apply the server behavior.

Step 11 deserves a bit of further explanation. 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 sqlstring that this server behavior generates. An example might be thefollowing:

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

JobLocation	JobDescription
San Francisco, CA	This position requires a minimum 3 to 5 years of working experience with key skills in Java, JavaScript, ASP, VBScript, .cfmL. Candidate must posses a detailed knowledge of web site concepts, web browser protocols in the design and display of web pages
Buffalo, NY	Must be proficient in all aspects of database programming using SQL Server. Must have extensive knowledge in building dynamic data-driven web sites using Flash 5.0, Generator 2.0, SQL Server, Javascript and ASP

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.

Debugging the Dynamic SQL

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.

A simple tutorial showing the behavior used with a Master/Detail pageset is shown 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.

Dreamweaver 8.0.2 compatibility

If using Dreamweaver 8.0.2, the recordset will show up as a "TomListRecordset" in the Bindings and Server Behaviors panel. This is required because of severe bugs with dynamic SQL using the Dreamweaver 8.0.2 updater. The new recordset has all the same properties as a Dreamweaver recordset, but has special code behind the scenes to allow previewing and bindings panel functionality that. The standard Dreamweaver recordset has bugs that prevent this in 8.0.2. Recordsets work fine with the server behaviors in DW 6, 6.0.1, 7, 7.1, 8.0, and 8.0.1.