Specifying a text-based search with SQL-like syntax

### Index Up << >>

## Specifying a text-based search with SQL-like syntax

If you have installed Jochen Wiedmann's `SQL::Statement` module, you can specify an SQL syntax for the text-based search. (This is not the same as the the SQL search, treated below separately. It would work on an SQL table but only on the ASCII text source file, not on the actual database.)

This syntax allows this rather nice form setup:

        Artist: <INPUT NAME="artist">
        Title:  <INPUT NAME="title">
        <INPUT TYPE=hidden NAME="mv_sql_query"
                VALUE="
                    SELECT code FROM products
                    WHERE artist LIKE artist
                    AND    title LIKE title">

If the right hand side of an expression looks like a column, i.e. is not quoted, then the appropriate form variable is substituted. (If used in a one-click, the corresponding scratch variable is used instead.) The assumption is reversed for the left-hand side -- if it is a quoted string then the column name is read from the passed values -- otherwise the column name is literal.

        Search for: <INPUT NAME="searchstring"><BR>
        Search in   <INPUT TYPE="radio" NAME="column" VALUE="title"> title
            <INPUT TYPE="radio" NAME="column" VALUE="artist"> artist
            <INPUT TYPE=hidden NAME="mv_sql_query"
              VALUE="SELECT code FROM products WHERE 'column' LIKE searchstring">

Once again, this does not do a search on an SQL database, but formats a corresponding text-based search. Parentheses will have no effect, and an OR condition will cause all conditions to be OR. The searches above would be similar to:

        [page search="
                    co=yes
                    sf=artist
                    op=rm
                    se=[value artist]
                    sf=title
                    op=rm
                    se=[value title]
                "  ]
            Search for [value artist], [value title]
        [/page]

        [page search="
                    co=yes
                    sf=[value column]
                    op=rm
                    se=[value searchstring]
                "  ]
        Search for [value searchstring]
               in  [value column]
        [/page]

### Index Up << >>