Updating MiniVend database tables with a form

### Index Up <<

## Updating MiniVend database tables with a form

Any MiniVend database can be updated with a form using the following method. The MiniVend companion application `MiniMate` uses this facility extensively.

NOTE: All operations are performed on the database, not the ASCII source file. You will have to perform a `[tag export table_name][/tag]` operation if you want the ASCII source file to reflect the results of the update.

You of course may insert or update records in any SQL database with the `[sql set]` tag, but you may also do form-based updates or inserts.

In an update form, special MiniVend variables are used to select the database parameters:

mv_data_enable (scratch)
  IMPORTANT: This must be set to a non-zero, non-blank value in the scratch space to allow data set functions. Usually it is put in an mv_click that precedes the data set function, i.e.:

          [set update_database]
          [if type=data term="userdb::trusted::[data session username]"]
              [set mv_data_enable]1[/set]
          [else]
              [set mv_data_enable]0[/set]
          [/else]
          [/if]
          [/set]
          <INPUT TYPE=hidden NAME=mv_click VALUE=update_database>

mv_data_table
  The table to update.

mv_data_key
  The field that is the primary key in the table. Must match the existing database definition.

mv_data_function
  UPDATE, INSERT or DELETE. The variable mv_data_verify must be set true on the form for a DELETE to occur.

mv_data_verify
  Confirms a DELETE.

mv_data_fields
  Fields from the form which should be inserted or updated. Must be existing columns in the table in question.

mv_update_empty
  Normally an variable that is blank will not replace the field. If you set mv_update_empty to true, a blank value will erase the field in the database.

mv_data_filter_(field)
  Instantiates a filter for `(field)`, using any of the defined MiniVend filters. For example, if `mv_data_filter_foo` is set to `digits`, only digits will be passed into the database field during the set operation. A common value might be ``entities'', which protects your HTML by translating `<` into `&lt;`, `"` into `&quot;`, etc.

The MiniVend action set causes the update. Here is an pair of example forms. One is used to set the key to access the record (careful with the name, this one goes into the user session values). The second actually performs the update. It uses the `[loop]` tag with only one value to place default/existing values in the form based on the input from the first form:

        <FORM METHOD=POST ACTION="[process-target]">
        <INPUT TYPE=HIDDEN name="mv_doit" value="return">
        <INPUT TYPE=HIDDEN name="mv_nextpage" value="update_proj">
        Sales Order Number <INPUT TYPE=TEXT SIZE=8
                                NAME="update_code"
                                VALUE="[value update_code]">
        <INPUT TYPE=SUBMIT name="mv_submit"  Value="Select">
        </FORM>

        [new]
        <FORM METHOD=POST ACTION="[process-target]">
        <INPUT TYPE=HIDDEN NAME="mv_data_table"    VALUE="ship_status">
        <INPUT TYPE=HIDDEN NAME="mv_data_key"      VALUE="code">
        <INPUT TYPE=HIDDEN NAME="mv_data_function" VALUE="update">
        <INPUT TYPE=HIDDEN NAME="mv_nextpage"      VALUE="updated">
        <INPUT TYPE=HIDDEN NAME="mv_data_fields"
                    VALUE="code,custid,comments,status">
        <PRE>

        [loop arg="[value update_code]"]
        Sales Order <INPUT TYPE=TEXT NAME="code    SIZE=10 VALUE="[loop-code]">
       Customer No. <INPUT TYPE=TEXT NAME="custid" SIZE=30
                        VALUE="[loop-field custid]">
           Comments <INPUT TYPE=TEXT NAME="comments"
                        SIZE=30 VALUE="[loop-field comments]">
             Status <INPUT TYPE=TEXT NAME="status"
                        SIZE=10 VALUE="[loop-field status]">
        [/loop]
        </PRE>

            <INPUT TYPE=hidden NAME="mv_todo" VALUE="set">
            <INPUT TYPE=submit VALUE="Update table">
        </FORM>

The variables in the form do not update the user's session values, so they can correspond to database field names without fear of corrupting the user session.

### Index Up <<