MiniVend can formulate and execute SQL searches in much the same way as it does text or Glimpse searches. Because of the SQL language and the limitations a common subset places on the operation, not all parameters are supported.
The following variables are in effect for SQL searches:
mv_delay_page S Sets the page for delayed search display mv_matchlimit S Sets match page size mv_numeric S Determines numeric status of column for ? bind mv_orsearch S Selects AND/OR of search terms mv_range_look S Sets the column to do a range check on mv_range_max S Upper bound of range check mv_range_min S Lower bound of range check mv_return_fields S Columns to return from query mv_search_field S Sets the column(s) to be searched mv_search_file S Sets the table to be searched mv_search_page S Sets the page for search display mv_searchspec S Search specification(s) mv_searchtype S Sets search type (text, glimpse, db, or sql) mv_sort_field S Column(s) to sort on mv_sort_option S Options for sort (only global reverse) mv_sql_query S SQL query text for simple query mv_substring_match S Turns off word-matching mode
Their two-letter abbreviations are in effect (as below), so you may easily do a one-click SQL search.
If you are using SQL for the products database, and the table you are searching is in the same SQL database, you don't need to specify the table other than in the query. If you are not using SQL for products, or it resides in a different database, then you must specify a MiniVend database identifier located in the same SQL database as the table you are querying. Use the mv_search_file variable:
<INPUT TYPE=hidden NAME=mv_search_file VALUE="inventory">
Once you have selected the database, you may query any table that is located within the same SQL data source.
There are two modes for SQL search:
<FORM METHOD=POST ACTION="[process-search]">
<INPUT TYPE=hidden NAME=mv_searchtype VALUE="sql">
<INPUT TYPE=hidden NAME=mv_sql_query
VALUE="select code from products where category = 'Americana'">
<INPUT TYPE=SUBMIT VALUE="Americana">
</FORM>
When the user clicks the button, the query will be done and the results returned using the default search return page. You may set the return page with mv_search_page as in the other searches, but most other variables have no effect.
Another exception is the mv_searchspec variable, which when set with either user-entered text or by another method, will be inserted in place of a single question mark in the query:
<FORM METHOD=POST ACTION="[process-search]">
<INPUT TYPE=hidden NAME=mv_searchtype VALUE="sql">
<INPUT TYPE=hidden NAME=mv_sql_query
VALUE="select code from products where category = ? ">
<SELECT NAME=mv_searchspec>
<OPTION> Americana
<OPTION> Contemporary
<OPTION> Impressionists
<OPTION> Renaissance
<OPTION> Surrealists
</SELECT>
<INPUT TYPE=SUBMIT VALUE="Go">
</FORM>
When the user selects one of the search categories, the value of mv_searchspec will be substituted for the question mark, and quoted if the field is not numeric in nature.
The spaces necessary in
SQL queries make hand generation of one-click URLs
pretty tedious. You may generate one-click searches easily using
[tag sql] SQL [/tag]. For example, the query
<A HREF="[tag sql]SELECT code from products where category = 'Americana'[/tag]">
Americana
</A>
generates HTML that starts out:
<A HREF="http://your.com/cgi-bin/simple/scan/sq=SELECT%20code%20from%20...";>
Americana
</A>
The actual URL is a bit too long to show. The same result would be generated by:
[page scan sf=category/se=Americana/st=sql]
Americana
[/page]
The first example may be more intuitive for some; it is marginally faster.
For example, if you don't specify a field or fields in the table to search, MiniVend will search all fields as is the default for the text and Glimpse searches. This can be quite inefficient, as the resulting query looks something like:
select code from products
WHERE title = 'Van Gogh'
OR artist = 'Van Gogh'
OR description = 'Van Gogh'
OR price = 'Van Gogh'
etc.
You get the picture. Each field is checked in turn. Much better is to set
the mv_search_field variable to the field(s) you wish
searched, skipping the ones that make no sense:
<INPUT TYPE=hidden NAME=mv_search_field VALUE=artist>
<INPUT TYPE=hidden NAME=mv_search_field VALUE=title>
This generates a much more limited query.
If there are more mv_searchspec values than fields, then only the first search field is used. The below query will fail, as the second and subsequent search fields are ignored.
<INPUT NAME=mv_searchspec VALUE="Van Gogh">
<INPUT NAME=mv_searchspec VALUE="Dali">
<INPUT TYPE=hidden NAME=mv_search_field VALUE="title">
<INPUT TYPE=hidden NAME=mv_search_field VALUE="artist">
If there are more mv_search_field values than mv_searchspec values, then only the first search specification will be used:
<INPUT NAME=mv_searchspec VALUE="Van Gogh">
<INPUT NAME=mv_searchspec VALUE="Dali">
<INPUT TYPE=hidden NAME=mv_search_field VALUE="title">
<INPUT TYPE=hidden NAME=mv_search_field VALUE="artist">
<INPUT TYPE=hidden NAME=mv_search_field VALUE="museum">
The string 'Dali' will never be looked for.
If the number of search fields and search specs are the same, a coordinated AND search is done, and only rows matching all search specs will be found.
The mv_range_look facility is in use for the complex form query as well, and operates in exactly the same way.
The following search will find all Van Gogh paintings that are between $1,000,000 and $20,000,000, providing the price field is a numeric data type. It also illustrates the use of some other MiniVend variables that are usable for SQL searches.
<FORM METHOD=POST ACTION="[process-search]">
<INPUT TYPE=hidden NAME=mv_searchtype VALUE="sql">
<INPUT NAME=mv_searchspec VALUE="Van Gogh">
<INPUT TYPE=hidden NAME=mv_search_field VALUE="artist">
<INPUT TYPE=hidden NAME=mv_range_look VALUE="price">
<INPUT TYPE=hidden NAME=mv_range_min VALUE="1000000">
<INPUT TYPE=hidden NAME=mv_range_max VALUE="20000000">
<INPUT TYPE=hidden NAME=mv_return_fields VALUE="code,description">
<INPUT TYPE=hidden NAME=mv_sort_field VALUE="price">
<INPUT TYPE=hidden NAME=mv_sort_option VALUE="r">
</FORM>
It will generate the query:
SELECT code, description FROM products WHERE artist = 'Van Gogh'
AND price >= 1000000 AND price <= 20000000
ORDER BY price DESC