Searching a Database Table and Displaying Results

Example

Searching for products in a database by name and item number.

MGI Tags

  • mgiSearchDatabase

Steps

  1. Create a page to display search fields and results.
  2. Save the search page.
  3. Open the search page in a text editor.
  4. Insert the mgiSearchDatabase tag.
  5. Save the search page.
  6. Create a search format file in a text editor.
  7. Save the search format file.
  8. Create a results format file in a text editor.
  9. Save the results format file.
  10. Create a results template file in a text editor.
  11. Save the results template file.
  12. FTP the search page, format files and template file to the web server running MGI.
  13. View the search page in a browser and perform a search.

Step 1: Create a page to display search fields and results.

Create a new page to display search fields and search results. Any text or headers associated specifically with the search fields or search results should be entered on the format and template files not on the search page itself. Only enter items common to both search and results screen on the search page (e.g., logos, headers, navigation bars, etc.).

Step 2: Save the search page.

Save the search page and name it "search.html".

Step 3: Open the search page in a text editor.

Open the search page (search.html) in a text editing program that allows you to view and modify the HTML.

Step 4: Insert the mgiSearchDatabase tag.

Insert your cursor in the HTML of the search page where you want the search form and results to display. Enter the mgiSearchDatabase tag, databaseFileLocation parameter, tableName parameter, searchFormatFileLocation parameter, resultsFormatFileLocation parameter, resultsTemplateFileLocation parameter, and orderByField parameter.
 
In the databaseFileLocation parameter, enter the path to and the name of the database file that contains the table of information to search. In the tableName parameter, enter the name of the database table to search. Note: one mgiSearchDatabase tag can only search the fields in one database table. In the searchFormatFileLocation parameter, enter the name of the text file that specifies the content and layout of the search form. In the resultsFormatFileLocation parameter, enter the name of the text file that specifies the layout of the results list. In the resultsTemplateFileLocation parameter, enter the name of the text file that specifies the layout of each result in the results list. In the orderByField parameter, enter the name of the database field to use for ordering results. The database field that you use to order results must be an indexed field (indexing is set up when the field is created). 
<mgiSearchDatabase databaseFileLocation="Clothing" tableName="Tshirts" 
searchFormatFileLocation="searchformat.txt"
resultsFormatFileLocation="resultsformat.txt"
resultsTemplateFileLocation="resultstemplate.txt"
orderByField="Design">

Step 5: Save the search page.

Save the changes you have made to the search page.

Step 6: Create a search format file in a text editor.

Create a new page in a text editing program to specify the content and layout of the search form. When the search page is parsed by MGI, the search format file is included in the search page at the location of the mgiSearchDatabase tag. The search format file should be a text file that contains only HTML, text, and MGI tags. You may use any type of form fields in the search form such as text fields, text boxes, popup menus, checkboxes and radio buttons. Each form field will search one database field. To specify the database field to search, name each form field with the following syntax: name="field=DatabaseFieldName" where "DatabaseFieldName" is the name of the database field to search. Database field names are case-sensitive. The value of each form field should consist of the search criteria. To match all records, use an asterisk (*) as the value (or use a blank value). For text fields and text boxes, the user will enter the search criteria. For popup menus, checkboxes, and radio buttons you must enter the search criteria in the value of the field. If multiple form fields are included in the search format file, an "AND" search will be performed across all field values. That is, only results that match all search criteria will be returned.
 
The search button can display as a regular HTML submit button or as an image. To use an HTML submit button, include the variable "mgiSearchButton" in the search format file where you want the search button to appear. Use the mgiGet tag to include the mgiSearchButton variable: 
<mgiGet name="mgiSearchButton">
To use an image as the search button in the search format file, enter an image input field named "mgiSearchDatabaseAction=performSearch" where you want the search image to appear: 
<input type="image" name="mgiSearchDatabaseAction=performSearch" 
src="search.gif">
An optional variable can be included in the search format file, mgiResultsLimitPopup. The mgiResultsLimitPopup variable creates a popup menu that limits the number of results displayed per page. The results can be limited to 5, 10, 15, or 25 by default or customized by including the resultsLimitPopupValues parameter in the mgiSearchDatabase tag on the search page and results page. If you include the mgiResultsLimitPopup variable in the search format file, you must also include the mgiPreviousButton and the mgiNextButton variables in the results format file. 
<mgiGet name="mgiResultsLimitPopup">
In the Clothing database example, the search format file for the keyword search contains two text fields for searching the t-shirt design and item number, the mgiResultsLimitPopup variable, and the mgiSearchButton variable. The following is a complete search format file of the Clothing database: 
<CENTER>
<TABLE WIDTH="250">
<TR><TD COLSPAN="2"><B>Search for a T-Shirt</B></TD></TR>
<TR><TD ALIGN="RIGHT" WIDTH="150">T-Shirt Name:</TD>
<TD WIDTH="100">
<INPUT NAME="field=Design" TYPE="text" SIZE="10"></TD></TR>
<TR><TD ALIGN="RIGHT">T-Shirt Item Number:</TD>
<TD><INPUT NAME="field=ProductID" TYPE="text" SIZE="10"></TD></TR>
<TR><TD><P ALIGN=RIGHT>&nbsp;Maximum Results Per Page:</TD>
<TD>&nbsp;<mgiGet name="mgiResultsLimitPopup"></TD></TR>
<TR><TD>&nbsp;</TD>
<TD>&nbsp;<mgiGet name="mgiSearchButton"> </TD></TR>
<TR><TD COLSPAN="2"><P><B>OR</B></TD></TR>
</TABLE>
</CENTER>

Step 7: Save the search format file.

Save the search format file and name it "searchformat.txt", the value of the searchFormatFile parameter in the mgiSearchDatabase tag on the search page.

Step 8: Create a results format file in a text editor.

Create a new page in a text editing program to specify the layout of the results list. When the results page is parsed by MGI, the results format file is included in the results page at the location of the mgiSearchDatabase tag. The results format file should contain only HTML, text, and MGI tags that are not repeated for each result. For example, if your results are displayed in a table with a row of headers, the table information and the first row of column labels might be included in the results format file since you do not want a new table and header row for each result that is displayed. However, depending on your layout, you could include all of the results formatting information in the results template file which specifies the layout of each result (e.g. if you wanted a new table for each result that is displayed).
 
Within the results format file, you must at least include the mgiSearchResults variable. When the results format file is parsed by MGI, the results template file that specifies the layout of each search result will appear in the location of the mgiSearchResults variable. 
<mgiGet name="mgiSearchResults">
If you included the mgiResultsLimitPopup variable in the search format file or if the mgiResultsLimitPopup variable was not included in the search format file and it is possible to return more than 25 search results, you must include the mgiPreviousButton and mgiNextButton variables in the results format file. These variables create a "Previous" and "Next" button to step through multiple screens of results. 
<mgiGet name="mgiPreviousButton"> <mgiGet name="mgiNextButton">
In the Clothing database example, the search results are displayed between the beginning and ending table tags. A "Search Results" header and page instructions appear above the results. The "Previous" and "Next" buttons appear below the search results (the form actions for the Previous and Next buttons are entered automatically by MGI). The following is the complete results format file for the Clothing database: 
<H3>Search Results</H3>

<p>The following results match the search criteria you entered.  
Click the "Next" button to view additional results. 

<CENTER><TABLE CELLSPACING="3" WIDTH="400">
<TR>
<TD WIDTH="350"><B>T-Shirt Description</B></TD>
<TD WIDTH="50" ALIGN="CENTER"><B>Price</B></TD></TR>
<TR>
<TD>&nbsp;</TD>
<TD>&nbsp;</TD></TR>

<mgiGet name="mgiSearchResults">

</TABLE>
</CENTER>

<p><center><mgiGet name="mgiPreviousButton">
<mgiGet name="mgiNextButton"></center>

Step 9: Save the results format file.

Save the results format file and name it "resultsformat.txt", the value of the resultsFormatFile parameter in the mgiSearchDatabase tag on the search page.

Step 10: Create a results template file in a text editor.

Create a new page in a text editing program to specify the layout of each result in the results list. The results template file should contain HTML, text, and MGI tags to display database information for each result. The entire format of the results template file is repeated for each result (whether there are 2 or 20) and the database information for each result is dynamically displayed. For example, in the Clothing database example, the Description and Price are dynamically displayed for each clothing result.
 
To include database information for each result in the results template file, use the mgiGet tag syntax. The name of the mgiGet tag specifies the database field information to display. Keep in mind that the database field names are case-sensitive. 
<mgiGet name="DatabaseFieldName">
In the Clothing database example, the each result consists of a table row. In the table row, the t-shirt description, and price are displayed. The following is the complete results template file for the Clothing database: 
<TR>
<TD><mgiGet name="Description"></TD>
<TD ALIGN="RIGHT">
<P ALIGN=CENTER>$<mgiGet name="Price"></TD></TR>

Step 11: Save the results template file.

Save the results template file and name it "resultstemplate.txt", the value of the resultsTemplateFile parameter in the mgiSearchDatabase tag on the search page.

Step 12: FTP the search page, format files and template file to the web server running MGI.

Upload the search page, format files and template file to the web server using an FTP program.

Step 13: View the search page in a browser and perform a search.

View the search page in a web browser. The static elements of the search page (e.g., logos, headers, navigation, etc.) and the search form (from the search format file) are displayed. Enter a search into the search fields and click the "Search" button. Static results information (from the results format file) and a dynamic list of results (formatted according to the results template file) that match your search replace the search form. The list of results is ordered by the "design" field (as specified by the orderByField parameter of the mgiSearchDatabase tag).

[Return to the Searching Databases and Displaying Results Menu]

[User Guide Main Menu] [Understanding MGI Menu] [Using MGI Menu] [Referencing MGI Menu]