Penn Computing
Computing Menu Computing A-Z
Computing Home Information Systems & Computing Penn

Simple DatabaseSearch

Simple Database Search is a generic CGI search utility developed for web providers on the host www.upenn.edu. It is not based on AltaVista, but rather is a complementary search utility for information extracted from database utilities like Excel or FileMakerPro. With Simple Database Search, you can set up a search form that enables your users to search your database of information by specifying certain options in a configuration file that you create. The instructions below will lead you through the process of using the Simple Database Search utility. Other resources:


Creating a header record

The first record in your database must be a "header" record, containing the field names as they will be displayed to users searching the database. Each field name should be unique for any one database. If the provider is opting to let the script create a search form, this header record will be used to create the select lists of possible fields from which the user can select fields to search, to display when showing search results and/or by which to sort search results.


Creating a database file

Simple Database Search searches a flat ASCII file of field-delimited information. You must first extract the database information that you want users to search and save it in a file, formatting the data into fields that are separated by a common delimiter.

For example, if you have a database of computing jobs, the department which is offering the job, and possible salary level, your database file could look something like:

Job Title|Department|Salary Level
Programmer/Analyst|ISC Networking|B
The first line is the header record and the second line is the first real data record. Note that in this example, | has been used as the delimiter character. Either | or a tab are good delimiters. Other characters could be included in the data and your search results will not be correct.

The process for creating this file will be specific to your software.


Creating a configuration file

To use the Simple Search utility, you must create a configuration file and place it in your site directory. The configuration file can be named whatever you want but it is recommended that you name your file searchdb.cfg.

  1. Open a new file in your HTML editor and save it as "searchdb.cfg".
  2. In your searchdb.cfg file, type the location of the database file that you want to search. This is the only variable that is required in the configuration file. The location must include the full file path beginning at the HTML root The syntax is:

    dbfile=/[providerdir]/[database filename]

    For example, if the name of the database file that you want to search is called "database.text" and is located in a subdirectory of the site directory for Computing called "jobs", the line would be

    dbfile=/computing/jobs/database.text


Setting additional variables in a configuration file

If any of the following is true, you need to set additional variables in your configuration file:

  • You want to include a link to your main web site and your main web site differs from the path to the database file.
  • Your home page is not called index.html
  • The field delimiter in your database is not |.
  • You have fields in your database whose contents you don't want to be searched or displayed.
  • You want to create your search form rather than using the search form that is created by default by the script.
  • You want to create your own search help page.
  • You want to customize the search pages by adding your own header and footer.
  • You don't want to display the search option with your results.
  • You don't want to include a navigational bar at the bottom of your search results.
  • You have fields with set values that can be searched.
  • You want to give the user the ability to choose a field by which to sort search results.
  • You want to allow users to search for records within a date range.
  • You want to restrict access to your database.

To set variables to control these situations, consult the table below. Any line in the searchdb.cfg file that begins with # is considered a comment line and is ignored by Simple Database Search. When you are finished setting additional variables in your configuration file, FTP the searchdb.cfg file up to your directory.

dbtitle The optional dbtitle variable specifies the name of the database and is used in the heading of the search pages.
Default: [none]
Syntax: title=[Descriptional database title]
Example: title=Computing Job Opportunities


delimiter The optional delimiter variable specifies the character that separates fields in the database extract file. If no delimiter is specified, | will be used as the field delimiter.
Default: |
Syntax: delimiter=|
Example: delimiter=|


title The optional title variable specifies the title of your web site that will appear in the navigational bar. If no title is specified, "Provider Site" will appear as the link to your web site.
Default: Provider Site
Syntax: title=[Descriptional title]
Example: title=Computing Job Opportunities


title_url The optional title_url variable specifies the URL of your home page. If no title_url is specified, the title_url defaults to the index.html in your home directory (for example, www.upenn.edu/mysite/index.html).
Default: [providerdir]/index.html
Syntax: title_url=[url of the provider's homepage]
Example: title_url=http://www.upenn.edu/computing/jobs/home.html


excludefields If you have fields that you don't want to be searched or displayed, it is best to not include these fields when creating your extract file but there are situations where this is not possible. If there are fields that you do not want included in the search, you need to use the exclude option.
Default: [none]
Syntax: excludefields=[name of fields as they appear in the header record]
Example: excludefields=SSN|Code

Regardless of the delimiter specified in your configuration file, you must use | to separate field headers if you wish to exclude multiple fields.


search_url The optional search_url specifies the location of your search form. This option only needs to be used if you are opting to create your own search form. If you opt to let the script create your search form, do not use this option.
Default: /cgi-bin/dbsearch.pl/[providerdir]/[searchdb.cfg]
Syntax: search_url=[url of the search form]
Example: search_url=http://www.upenn.edu/computing/jobs/search.html

If you do opt to create your own search form, please see our document on creating your own search form.


help If you choose to create your own search help page, the optional help specifies the location of your page. A link to this page will be included in the navigational bar if you have set navbar=on.

Default: http://www.upenn.edu/computing/web/webdev/dbsearch/search-help.html
Syntax: help=[url of the search help page]
Example: help=http://www.upenn.edu/computing/jobs/help.html


header The optional header specified the location of your header file. If no header is specified, the header of the search form page will default to
<html>
<head>
<title>Search [dbtitle] Database<title>
</head>
<body bgcolor="#FFFFFF">
<center>
<h3>Search [dbtitle] Database><h3>
</center>
<blockquote><blockquote>
If no header is specified, the header of the search results page will default to
<html>
<head>
<title>[dbtitle] Search Results</title>
</head>
<body bgcolor="#FFFFF">
<blockquote><blockquote>
You header file should include the <html>, <head>, <title>, and <body> tags. Any links included in your header file must use paths beginning with the absolute HTML root like
<img src="/computing/img/computing.img">
rather than
<img src="img/computing.img">
Since this is a CGI script that is dynamically producing the search pages, you cannot use Server-Side Includes within your header file.
Default: [see above]
Syntax: header=[location of your header file]
Example: header=/computing/lib/inc/v0/compmenuhead.html


footer The optional footer specifies the location of your footer file. If no footer is specified, the footer of the search results page will default to
</blockquote>
</blockquote>
</body>
</html>
You footer file should include a closing </body> and closing </html> tag. Any links included in your footer file must use paths beginning with the absolute HTML root like
<img src="/computing/img/computing.img">
rather than
<img src="img/computing.img">
Since this is a CGI script that is dynamically producing the search results page, you cannot use Server-Side Includes within your footer file.
Default: [see above]
Syntax: footer=[location of your footer file]
Example: footer=/computing/lib/inc/v0/compfoot.html


searchargs The optional searchargs specifies whether you want the search arguments to be displayed with your search results. If searchargs is not specified or if searchargs is set to "on", the top of the search results page will default to something like

Search Results

Search string:   franklin
Search fields:   Letter Type - Recommendation Letter
Number of records:   44

If you turn off the display of the search arguments, the top of the search results page will only display the header.

Search Results

Default: [see above]
Syntax: searchargs=on | off
Example: searchargs=off


view The Simple Database Search script can show search results in two formats:
  • long view - paragraphs of text
    Job Description: Programmer/Analyst
    Department: ISC Networking
    Salary Level: B
  • quick view - tabled display
    Job Description Department Salary Level
    Programmer/Analyst ISC Networking B
By default, Simple Database Search will give users the ability to choose between these two formats and will default to the quick view if the user doesn't select a format.

The optional view allows you to specify the order of the select list that is presented to the user showing the two formats - "quick" and "long", or if you only set one format, then the user is not given a choice of formats for the records returned in the search results.

Default: [see above]
Syntax: view=long|quick
Example: view=long
If you don't want to give the user the option of choosing a format for displaying the search results and you don't want to default to the "quick" view.
Example: view=long|quick
If you want to give the user the option of choosing a format for displaying the search results but you want the default to be the long format.


navbar The optional navbar specifies whether you want a navigational bar to be displayed with your search results and what that navigational bar will be. If no navbar is specified, the bottom of the search results page will include links back to your homepage, your search form and to the central Penn Web search.

Test Site Search Test Site Penn Web Search

You can set the navbar to "off" and no links will appear at the bottom of your search results page.

You can also set navbar to be your own file containing navigational links. Any links included in your navigational file must use paths beginning with the absolute HTML root like

<img src="/computing/img/computing.img">
rather than
<img src="img/computing.img">
Since this is a CGI script that is dynamically producing the search results page, you cannot use Server-Side Includes within your navigational file.
Default: [see above]
Syntax: navbar=on | off | [location of navigational file]
Example: navbar=off
Example: navbar=/computing/lib/inc/v0/navbar.html


selectlist If any particular field in the database can contain only one value from a finite list of possible values, the provider can opt to present those possible values to the user in a select list in the search form. The provider should set up one selectlist option for each field that has a finite list of possible values, where the first word is the field header name, followed by the possible values separated with |. Regardless of the delimiter specified in your configuration file, you must use | to separate field headers and the list of possible field values if you wish to use selectlists.
Default: [none]
Syntax: selectlist=[fieldheader]|value1|value2|value3|etc
Example: selectlist=Job Title|Programmer/Analyst|Database Administrator|Systems Programmer|Systems Engineer


sortallow The optional sortallow specifies whether you want to allow your users to choose a field by which to sort the search results. This option does not change the order in which fields display, rather it changes the order in which records displays. By default, this option is set to "off". If you want your users to be able to sort search results, you must set this option in your configuration file.

For example, if you have a a database with the fields, "Age", "First Name, "Last Name", and "Salary" and your default search results look like this:

Age First Name Last Name Salary
40 Robert Johnson 4800.00
35 Mary Smith 3500.00
42 John Jones 4500.00
The user can opt to sort the search results by the "Salary" field and those same search results would look like:
Age First Name Last Name Salary
35 Mary Smith 3500.00
42 John Jones 4500.00
40 Robert Johnson 4800.00
Or the user can opt to sort the search results by the "Last Name" field:
Age First Name Last Name Salary
40 Robert Johnson 4800.00
42 John Jones 4500.00
35 Mary Smith 3500.00
Default: off
Syntax: sortallow=on | off
Example: sortallow=on


datatype If you have chosen to add the sortallow option to your configuration option and you have any fields that contain either dates or numeric data, you must specify the datatype of those fields.

The possible datatypes are:

  • data
  • date
  • numeric
By default, Simple Database Search will expect the contents of all fields to be plain alphabetic data. If you have a field called "SSN" that contains all numeric data, then you would add the following option to your configuration file:
datatype=SSN|numeric
The provider should set up one datatype option for each field that has either dates or numeric data, where the first word is the field header name, followed by |, followed by the datatype. Regardless of the delimiter specified in your configuration file, you must use | to separate field headers if you wish to exclude multiple fields.
Default: data
Syntax: datatype=[fieldname]|[data or numeric or date]
Example: datatype=Code|numeric


daterange The optional daterange is used if you have a field that contains dates and you want to allow users to search for records within a date range. You can have only one daterange option in your configuration file.
Default: [none]
Syntax: daterange=[fieldheader]
Example: daterange=Date


Creating a link to Simple Database Search

The Simple Database Search script will create a search form for you based on values that you have specified in your search configuration file. To call the script to create the search form, you can add a link on one of your pages like

<a href="/cgi-bin/dbsearch.pl/[providerdir]/[searchdb.cfg]">Search database</a>
/cgi-bin/dbsearch.pl is the actual search script. The rest of the link is the path to your search configuration file. The search script will read the rest of the link to know where to find the configuration file and will read it to get your configuration options when creating the search form and search results.

For example, if you want to create a link for a database where the name of the search configuration file is "searchdb.cfg" and is located in a subdirectory of the site directory for Computing called "jobs", the link would be

<a href="/cgi-bin/dbsearch.pl/computing/jobs/searchdb.cfg">Search the database of Computing jobs</a>

Additional configuration options will need to be set if you wish to restrict access to your database.

Guidelines to creating your own search form are available.

top

Information Systems and Computing
University of Pennsylvania
Comments & Questions


University of Pennsylvania Penn Computing University of Pennsylvania Information Systems & Computing (ISC)
Information Systems and Computing, University of Pennsylvania