CHAPTER 5: ehsql:select

The value specifies the list of tables in the query.

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

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 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.

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="" ).

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.

This is the maximum number of records which will display on a single page of a multi- record list. The default is twenty.

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 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.

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>

	

This variable can be set to an additional WHERE condition ANDed 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