[ic] Searching: grouping AND / OR searches on multiple fields withone se

[Dan B <db@cyclonehq.dnsalias.net>]

At www.diabeticsupplies.com I am trying to modify a form based search to 
restrict search to 3 fields instead of all fields, while keeping only one 
mv_searchspec.  This is the desired functionality (in pseudo-code):

SELECT sku FROM products WHERE
         (       title LIKE se
                 OR category LIKE se
                 OR keywords LIKE se     )
AND
         (       sku LIKE [scratch skus_canview_all]     )

But right now, all I can get it to do is:

SELECT sku FROM products WHERE
         (       :* LIKE se      )
AND
         (       sku LIKE [scratch skus_canview_all]     )

(Where :* is the mv_search_field: which translates to all fields)

It boils down to:  how do I do a grouped OR search with a single AND in a 
form-based search?

Here is the code that I think should work (but doesn't):
-------------------------------------------------------------
<INPUT TYPE=hidden      NAME=mv_coordinate              VALUE=1>
<INPUT TYPE=hidden      NAME=mv_searchtype              VALUE=db>
<INPUT TYPE=hidden      NAME=mv_matchlimit              VALUE=10>
<INPUT TYPE=hidden      NAME=mv_sort_field              VALUE=category>
<INPUT TYPE=hidden      NAME=mv_search_page             VALUE=results_cat2>
<INPUT TYPE=hidden      NAME=mv_search_file             VALUE=products>
<INPUT TYPE=hidden      NAME=mv_check                   VALUE=supsearch_click>
<INPUT TYPE=hidden      NAME=mv_substring_match VALUE=1>

         <INPUT TYPE=hidden      NAME=mv_search_field    VALUE="title">
         <INPUT TYPE=hidden      NAME=mv_column_op               VALUE=rm>
<!--or-->
         <INPUT TYPE=hidden      NAME=mv_search_field    VALUE="category">
         <INPUT TYPE=hidden      NAME=mv_column_op               VALUE=rm>
<!--or-->
         <INPUT TYPE=hidden      NAME=mv_search_field    VALUE="keywords">
         <INPUT TYPE=hidden      NAME=mv_column_op               VALUE=rm>
<!--equals-->
         <INPUT 
type=text        NAME=mv_searchspec              MAXLENGTH=30 size=10>
<!--and-->
         <INPUT TYPE=hidden      NAME=mv_search_field    VALUE="sku">
         <INPUT 
type=hidden      NAME=mv_searchspec              VALUE="[scratch 
skus_canview_all]">
-------------------------------------------------------------
And here is the code that I'm using now (works, but searches all fields 
instead of desired 3):
-------------------------------------------------------------
<INPUT TYPE=hidden      NAME=mv_coordinate              VALUE=1>
<INPUT TYPE=hidden      NAME=mv_searchtype              VALUE=db>
<INPUT TYPE=hidden      NAME=mv_matchlimit              VALUE=10>
<INPUT TYPE=hidden      NAME=mv_sort_field              VALUE=category>
<INPUT TYPE=hidden      NAME=mv_search_page             VALUE=results_cat2>
<INPUT TYPE=hidden      NAME=mv_search_file             VALUE=products>
<INPUT TYPE=hidden      NAME=mv_check                   VALUE=supsearch_click>
<INPUT TYPE=hidden      NAME=mv_substring_match VALUE=1>

         <INPUT TYPE=hidden      NAME=mv_search_field    VALUE=":*">
         <INPUT TYPE=hidden      NAME=mv_column_op               VALUE=rm>
<!--equals-->
         <INPUT 
type=text        NAME=mv_searchspec              MAXLENGTH=30 size=10>
<!--and-->
         <INPUT TYPE=hidden      NAME=mv_search_field    VALUE="sku">
         <INPUT 
type=hidden      NAME=mv_searchspec              VALUE="[scratch 
skus_canview_all]">
-------------------------------------------------------------

Thanks for any tips.  I've tried to glean all I could from the database.pdf 
and mailing list searches, but I'm probably missing something really 
brain-damaged.  (I need to quote Linus more often).

P.S.  I do have a good reason for doing such a silly-looking 
search.  DiabeticSupplies.com has a pretty cool feature (which I think 
demonstrates the power of interchange) where it displays the exact prices / 
products that your insurance pays for / covers, for every single customer 
and thousands of insurance companies (it's a *big* database).  I get 
excited about it because they are our most exciting client (entire business 
built from the ground up on Linux, very high "cool" factor, etc.)

Dan Browning, Cyclone Computer Systems, danb@cyclonecomputers.com


_______________________________________________
Interchange-users mailing list
Interchange-users@lists.akopia.com
http://lists.akopia.com/mailman/listinfo/interchange-users