Searching is another must-have functionality for a database application framework like DbForms.
(Note: from 1.1.4pr1 there is a new Filter tag that you may want to use instead of using search-forms).
DbForms allows you to create search-forms very quickly. The number of fields to be searched, the kind of input widgets (text field, textArea, select box, etc.), the search (compare) algorithms and the search criteria combinations are easily defined by the developer.
All you have to do is:
decide which fields you want to make searchable.
decide if you want the user to choose the criteria-combination-mode or if you want to use a (hidden) default.
decide if you want the user to choose the kind of search algorithm to be used or if you want to use a (hidden) default.
define a kind of search-button which will submit the search fields to the controller.
The fields holding search-criteria, search-criteria-combing-mode and search-algorithms must follow a special naming convention (in order to allow searching in nested forms, etc). This makes the whole thing look a bit complicated, but it really isnt, as you will see below. This naming convention is prepared for you in hashtable objects accessible as scripting variables. (refer to the chapter on Scripting Variables)
Table 12.2. search naming system
| Name of scripting variable Hashtable | Description |
| searchFieldNames_tableName | Contains the encoded names used for selecting the search criteria fields (firstname, lastname, etc.) Example: searchFieldNames_cust.get("firstname"); |
| searchFieldModeNames_tableName | Contains the encoded names used for specifying the search modes (and, or) of each search criteria Example: searchFieldModeNames_cust.get("firstname") |
| searchFieldAgorithmNames_tableName | Contains the encoded names used for specifying the algorithm type (weak, sharp) of each search criteria Example: searchFieldAlgorithmNames_cust.get("firstname") |
The behaviour of searching is selected by different algorithm types:
Table 12.3. search behavior selection
| possible values of search field algorithm: | Description - Searching is done with: |
| sharp | = this is the default behavior |
| sharpLT | < |
| sharpLE | <= |
| sharpGT | > |
| sharpGE | >= |
| sharpNE | <> |
| sharpNULL | is null |
| sharpNOTNULL | Not is null |
| weak | like |
| weakStart | like, % is added in front of value |
| weakEnd | like, % is appended to value |
| weakStartEnd | like, % is added in front of value and % is appended to value |
| sharpExtended | In this mode the search value is parsed for special chars -- see list below. The default search operator is taken from the list above. |
| sharpLTExtended | |
| sharpLEExtended | |
| sharpGTExtended | |
| sharpGEExtended | |
| sharpNEExtended | |
| sharpNULLExtended | |
| sharpNOTNULLExtended | |
| weakExtended | |
| weakStartExtended | |
| weakEndExtended | |
| weakStartEndExtended |
If extended mode is set, the values will be parsed for special chars at the beginning:
Table 12.4. extended mode searches
| Special chars in search values | Description |
| = | Search operator is sharp |
| <> | Search operator is sharpNE |
| != | Search operator is sharpNE |
| >= | Search operator is sharpGE |
| > | Search operator is sharpGT |
| <= | Search operator is sharpLE |
| < | Search operator is sharpLT |
| [NULL] | Search operator is sharpNULL |
| [!NULL] | Search operator is sharpNOTNULL |
| value1-value2 | Results in fieldname <= value1 and fieldname >= value2 |
| value1- | Results in fieldname <= value1 |
| -value2 | Results in fieldname >= value2 |
This is a fully functional search panel. It is included in the example application included in the DbForms distribution. You may also test it on the running live samples at the DbForms Website.
This is the code responsible for creating the panel above. In the context of this example, the use of the hashtable should be clear. As mentioned above, this mechanism may be applied on any other database fields, using any html widgets for data input.
<table cellspacing="0" cellpadding="1" width="550">
<tr bgcolor="#F7A629">
<td bgcolor="#F7A629"><b>Search a customer</b></td>
<td colspan="2" bgcolor="#F7A629">nbsp;</td>
<td bgcolor="#F7A629">
<input value="Search!"
type="button"
onClick="javascript:document.dbform.submit()">
</td>
</tr>
<tr bgcolor="#CCBBCC">
<td>Field</td>
<td>Value</td>
<td>Combining mode</td>
<td>Search Algorithm</td>
</tr>
<tr bgcolor="#BBCCBB">
<td>First name</td>
<td>
<input type="text"
name="<%=
searchFieldNames_customer.get("firstname") %>"
size="17"
/>
</td>
<td>
<input type="radio" checked
name="<%=
searchFieldModeNames_customer.get("firstname") %>"
value="
and"> AND
<input type="radio"
name="<%=
searchFieldModeNames_customer.get("firstname") %>
value="
or"> OR
</td>
<td>
<input type="checkbox"
name="<%=
searchFieldAlgorithmNames_customer.get("firstname") %>"
size="30"
value="
weak">Weak
</td> .
</tr>
<tr bgcolor="#BBCCBB">
<td>Last name</td>
<td>
<input type="text"
name="<%=
searchFieldNames_customer.get("lastname") %>"
size="17"/>
</td>
<td>
<input type="radio" checked
name="<%=
searchFieldModeNames_customer.get("lastname") %>"
value="
and"
> AND
<input type="radio"
name="<%=
searchFieldModeNames_customer.get("lastname") %>
value="
or"
> OR
</td>
<td>
<input type="checkbox"
name="<%=
searchFieldAlgorithmNames_customer.get("lastname") %>"
size="30"
value="
weak"
>Weak
</td>
</tr>