# CHAPTER 5: ehsql:select

## 5.1 Introduction

`ehsql:select` is a tool for simulating the functionality of an SQL `SELECT` statement using HTML form fields. By setting form variables to desired values the desired query can be built up and submitted. This will take considerably more typing than equivalent `SELECT` statements. If you prefer to use SQL, `ehsql:writer` might be a better choice. The advantages of `ehsql:select` are:

1. You don't need to know SQL.

2. The results of the query are divided up into manageable chunks so that queries returning thousands of records won't generate long HTML pages on the internet.

3. It will format the results of queries in a general way, so that the exact query doesn't have to be known in order to design the output.

4. Many different types of queries can be generated from one user-input form.

## 5.2 Invoking ehsql:select from a HTML Form

To invoke `ehsql:select` tool via a HTML form, you have to do the following:

1. Set `method=post`.
2. The `action` clause of the form should be a path (or ScriptAlias) to `ehsql.cgi` followed by a path to a template file. The template file will make use of the results of the query. Thus the syntax for the action is:

          action=path_to_ehsql.cgi/path_to_template_file

For example a typical `<form>` tag might be:

       <form method=post
        action="/My-bin/ehsql.cgi/docs/q1template.html">

The first part tells the HTTP server to execute the copy of `ehsql.cgi` located in the CGI directory indicated by the `/My-bin/` script alias. The HTTP server will pass the form variables to the `ehsql.cgi` program. The second part indicates the location of the HTML file: in this case `DocumentRoot/EH_DOC_ROOT/docs/q1template.html`. The `ehsql.cgi` program then uses `q1template.html` to format the results of the query.

By default `ehsql.cgi` uses the `ehsql:writer` tool. It switches to using the `select` tool if it sees the `eh-mode` HTML form variable set to `select`.

## 5.3 The Contents of a Select Form

The select queries are generated entirely inside an HTML form which is then posted to `ehsql.cgi`. Within the form, the various standard user-input widgets can be declared. These are like variables - they are given names, and those names are given values by the user or the application developer. They are described in the The Select Form Variables section of this chapter.

Within a form posted to `ehsql.cgi`, any type of form widget can be used (text fields, pull-down lists, hidden fields, radio buttons etc.) but the names given them are special keywords. The values given those fields determine which database, which tables, which query, and what kind of output is generated. All the keywords begin with `eh-`.

## 5.4 The Output of a Select Form

When the form is submitted to `ehsql.cgi`, two HTML pages may be generated. If more than one record is returned by the query, the first HTML page displayed will be the multi-record display. This lists all the records returned, with each row being a single record. The number of records per page can be set in the form, and the attributes displayed. Each record will have a hyperlink available, and clicking on this link will bring the viewer to the single-record display. This displays one record per page. There is a default single user list, but most developers will want to create their own. The returned attributes are available by their names, encased with double percent signs. The value of attribute `Idnumber` for instance could be referenced in the single-record list as `%%Idnumber%%`. Generally, a few key attributes are displayed in the multi-record display, and a larger set in the single- record display. Large text or character objects will not format well within the multi- record display. Binary objects will not show up in either the single or multi-record lists, but rather a hyperlink will appear pointing to display of the binary object, if it is in a browser-recognized format. Otherwise the hyperlink will bring up a browser save- box.

To create a template form, an HTML document must be made with two tags:

       <MULTI
        [anchor="attribute_name" |
         anchor="<img src="image_file">"]>
          [your_own_headers]
       </MULTI>

       <SINGLE [DEFAULT]>
          html_script
       </SINGLE>

There is not much formatting available for the multi-record display. Within the `<MULTI>` tag itself, a clause can be added to specify which attribute to use as the hyperlink to the single-record display (if you don't want the hyperlink to be `eh-key-attribute`, described in the next section). Alternately a `.gif` image can be used as the anchor. If `<MULTI>` is left blank, the default display is used.

The positioning of the closing `</MULTI>` tag is important. The following three possibilities exist:

  Position | Effect
  --- | ---
  `<MULTI...></MULTI>` | Display Default Headers
  `<MULTI...> </MULTI>` | Display No Headers
  `<MULTI...> <tr><th>your own words<th>.... </MULTI>` | Define Your Own Headers

Any kind of formatting may go between the `<SINGLE>` tags. The value of the attributes passed to the template file with the `eh-attributes` form variable, or in the `eh-select` statement, will be available by giving the attribute name surrounded by double percent signs.

If the Toolkit's standard single-record display is fine for a particular application, place the keyword `DEFAULT` inside the `<SINGLE>` tag. If `<SINGLE DEFAULT>` is used, and there is EHTML between the tags, the EHTML will display after the default output. Imagine a previous query which brought back a record from a company directory table with attributes `name`, `username`, `email_address`, and `phone_extension`. The following fragment will display this information with the default display, but also generate a "send email" button:

       ...

       <SINGLE DEFAULT>
       <center>
       <a href="mailto:%%email_address%%">
       <img src="$$LOCATION$$/mailbox.gif">
       </a>
       </center>
       </SINGLE>

       ...

## 5.5 The Select Form Variables

Unlike a standard HTML form, the select form may only use certain names for the variables, and each has a meaning. Any variable which does not start with `eh-` is assumed by `ehsql:select` to be an attribute name of a table in a database. Think of these variables as together forming something like an SQL `SELECT` statement. The tables, attributes and query conditions must all be specified.

Variables without predefined meaning can be declared in an `ehsql.cgi:select` form, but they should start with `eh-` and should not be a keyword, or else they will be interpreted incorrectly.

The following is a list of all allowable form variable names, and their meanings.

Table 5-1: The Select Form Variables

Name | Description
--- | ---
`eh-dbase` | The value specifies the data source. This is required.
`eh-table` | The value specifies the list of tables in the query.
`eh-mode` | This is required and must be set to 'select'
`eh-attributes` | The value should be a list of attributes desired in the single-record list. The order of specification determines the order of display. If not set, the all attributes in the table will be displayed
`eh-multirecord-attributes` | The value should be a list attributes desired in the multi-record list. The order of specification determines the order of display. If not set all attributes in the table will be displayed
`eh-clausetype` | If the name of an attribute appears in the select form, it is assumed to be a query condition. For instance:
       <input name="last_name">
will generate a text box in which the user can type a value. When the query is interpreted upon submission, the condition `WHERE last_name = 'user_input'` will be part of the query. The equality condition is default, `eh-clausetype` is used to specify a different type of condition. An `eh-clausetype` field should directly follow the attribute/value field it modifies. For instance:
       <input name="last_name">
       <input type=hidden name="eh_clausetype" value="MATCH">
This will cause the query to return all last names whose values match a user input pattern. `MATCH` is a standard Empress operator, and is described in the SQL Reference Guide.
       <input name="Idnumber" value="96000">
       <input type=hidden name="eh_clausetype" value= ">=" >
This will generate a query equivalent to the SQL clause: `WHERE Idnumber >= 96000`. Note the use of hidden fields. In HTML forms, a hidden field doesn't generate any user input widget. It is just a means of declaring a form variable and setting its value to be passed to the CGI which handles the form. This is good for parts of the query which are are standard and not input by the user. For instance, most applications would set `eh-dbase` with a hidden field. `eh-clausetype` doesn't have to be in a hidden field. A pull-down menu is a good way to allow the user to specify which type of operator to use when building up a query. Note: In HTML form syntax, pull down menus are declared with the `<SELECT>` tag, and these have absolutely nothing to do with an SQL `SELECT` statement. The allowed values of `eh_clausetype` are:
       =,!=,<,>,<=,>=, (NOT) MATCH, (NOT) SMATCH, IN, BETWEEN
If `eh-clausetype` is not specified, the equality operator (=) is assumed.
`eh-clausenext` | `eh-clausenext` appears between form fields with attribute names, or attribute `name`/`eh-clausetype` pairs. It specifies the boolean operator to use when connecting sections of a query. A form with fields such as:
       <input name="status">
       <input type=hidden name="eh-clausetype" value="MATCH">
       <input name="project">
will generate an SQL query:
       WHERE status MATCH 'user_input' AND
          project='more_user_input'
The binary operator `AND` is the default boolean connector. If one adds a line in between the conditions of the previous query, it becomes:
       <input name="status">
       <input type=hidden name="eh-clausetype" value="MATCH">
       <input type=hidden name="eh-clausenext" value="OR">
       <input name="project">
This is now equivalent to:
       WHERE status='user_input' OR
          project='more_user_input'
The only values `eh-clausenext` can be set to are `AND` and `OR`. `AND` is default, so it is not necessary to have an `eh-clausenext` field in a query consisting only of clauses `AND` together.
`eh-field-separator` | When the value of eh-field-separator is set to a particular character, that character becomes the separator used when listing items in those `ehsql.cgi` form variables which take lists. The default is the comma. This is sometimes useful when the user input was obtained by roundabout methods. `eh-field-separator` can be set to nothing ( `value=""` ).
`eh-key-attribute` | This is the attribute to be used in the multi-record-list which displays as a hyper-link to the single-record display. It must be one of the records listed in the `eh-multirecord-attributes` variable. If this is not specified, the internal index is used.
`eh-max-records` | This is the maximum number of records which will display on a single page of a multi- record list. The default is twenty.
`eh-order-by` | This gives an attribute list to order the output of the multi-record list by. This is the equivalent of an `ORDER BY` clause in SQL. The attributes specified need not appear in eh-multirecord-attributes.
`eh-post-key` | `eh_post_key` and `eh_post_key#`, where # is a number from 1 - 9, are provided as user-defined variables. They are not strictly necessary, because any non-keyword variable beginning with `eh-` can be declared and its value passed to the next form. Nevertheless, the values of `eh-post-key` variables can be referenced as the equivalent `eh-dbase-key` variables in the template document. This is a convenient feature, because `eh-dbase-key` variables are used in `ehsql:writer` to reference URL line parameters, but URL line parameters are not allowed in `ehsql:select`.
`eh-select` | This allows a complete select statement to be issued. This is for users who prefer SQL, but still want the multi-record-list functionality, which `ehsql:writer` doesn't provide. When an `ehsql:select` variable is set with an SQL `SELECT` string, the only other variable that needs to be set is `eh-dbase`. The other form variables are ignored. For instance:
       <form method=post action="/CGI/ehsql.cgi">
       <input type=hidden name=eh-dbase value="db1">
       <input type=hidden name=eh-mode value="select">
       <input type=hidden name=eh-select
        value="SELECT name, phone FROM employee_table">
       <input type=submit value="press to see list of employee phone
        numbers">
       </form>
`eh-where` | This variable can be set to an additional `WHERE` condition `AND`ed to the conditions specified with the `eh-clausetype` and `eh-clausenext` variables. This is useful to express more complicated `WHERE` clauses. Join conditions are an obvious example:
       <form method=post action="/emp-bin/ehsql.cgi">
       <input type=hidden name=eh-mode value="select">
       <input type=hidden name=eh-dbase value="Client_db">
       <input type=hidden name=eh-table
        value="employee,dependents">
       <input type=hidden name=eh-multirecord-attributes
        value="emp_name,dep_name">
       <input type=hidden name=eh-attributes
        value="emp_name,dep_name,dep_address">
       Type an Employee Name:<input name=emp_name><p>
       <input type=hidden
        name=eh-where value="
        employee.num=dependents.emp_num">
       <input type=submit value="Push to see list of employee's
        dependents.">
       </form>
In this example, two tables appear in the query. The user inputs an employee name and the query returns records from both employee and dependents tables joined on the employee number. This is equivalent to the SQL `SELECT` statement:
       SELECT emp_name, dep_name FROM employee,dependents
          WHERE emp_name = 'user_input' AND
          employee.num=dependents.emp_num