SQL searches

["Russ" <russ@khouse.org>]

Hello,

I've been working to get SQL searching to happen for quite some time now.
The most promising and versatile SQL search syntax seems to be [sql
type=list]stuff[/sql].

Here's my query form:
<form method="POST" action=[process-target]>
<input type="hidden" name="mv_nextpage" value="sql-results">
<input type="hidden" name="mv_doit" value="return">
<select name="category_search">
           <option value="">All
           <option value="Book" selected>Books
           <option value="Music">Music
           <option value="Video">Videos
           <option value="Software">Software
           <option value="AudioBook">Audio Books
           <option value="Bible">Bibles
           </select>
<select name="thefield">
           <option value="author" selected>Author
           <option value="title">Title
           <option value="detail">Description
           </select></td>
<input type="text" name="theterm" size=10><input type="submit" value="Go!">
</form>

Here's what I have on the sql-results page:
[sql type=list query="select code from products where [if value
category_search]category1 = '[value category_search]' and[/if] [value
thefield] like '%[value theterm]%'"]
[lots of stuff to format the returned data how i want it, but means nothing
to this problem]...
[/sql]

What I want, is to have a results page extremely similar to the text based
search results page.  One where I can do this:
[more-list 0 0 0]
<big>Displayed are [matches] of [value mv_search_match_count] matches!</big>
<br>
<b>Result Pages:</b>
[next-anchor][<em>Next &gt;&gt;</em>][/next-anchor]
[prev-anchor][<em>&lt;&lt; Prev</em>][/prev-anchor]
[more]
[/more-list]

and this:

[search-list]
[lots of stuff to format the returned data how i want it, but means nothing
to this problem]...
[/search-list]

or equivilants, that divide the results into 10 hits per page or something
similar.

The reason I am not using a regular search form with mv_searchtype=sql in
this example is because the functionality is extremely limited. I have
implemented a nearly similar search which detects which field to search and
searches 1 field only.  The code is this:

<form method="POST" action=[process-search]>
<input type="hidden" name="mv_searchtype" value="sql">
<select name="mv_search_field">
           <option value="author" selected>Author
           <option value="title">Title
           <option value="detail">Description
           </select>
<input type="hidden" name="mv_substring_match" value="yes">
<input type="hidden" name="mv_matchlimit" value="10">
<input type="hidden" name="mv_sort_field" value="fulfiller">
<input type="hidden" name="mv_doit" value="search">
<input type="text" name="mv_searchspec" size=20>
<input type="submit" name="mv_todo" value="Search">
</form>

The main problem is I need to compare a value to one field, then another
value to the other fields.  Or do something like this:
[somewhere in all 10 fields] like value1 AND value2
(obviuosly not real code for anything, but i can't figure out how to say
what i mean otherwise!)

Any help would be great, Thanks!

Russ Mann