Re: SQL searches

[mikeh@minivend.com]

******    message to minivend-users from mikeh@minivend.com     ******

Quoting heino (saareh@cromwell-tools.co.uk):
> I get no results worth talking about with something like:
> 
> <FORM ACTION="[process-search]" METHOD=POST>
>      <INPUT TYPE="hidden" NAME="mv_searchtype" VALUE="sql">
>      <input TYPE=hidden NAME=mv_search_page VALUE="results1.html">
>      <INPUT TYPE="hidden" NAME="mv_sql_query" VALUE="SELECT code FROM
> products WHERE grade like '?' or description like '?'; ">
>     <input type="text" name="mv_searchspec" size="20">
>      <INPUT TYPE="submit" VALUE="SQL">
> </FORM>
> 
> 

I think you would be better off letting MV build the query:

 <FORM ACTION="[process-search]" METHOD=POST>
      <INPUT TYPE=hidden NAME=mv_searchtype   VALUE="sql">
      <INPUT TYPE=hidden NAME=mv_search_page  VALUE="results1.html">
      <INPUT TYPE=hidden NAME=mv_search_field VALUE="grade">
      <INPUT TYPE=hidden NAME=mv_search_field VALUE="description">
      <INPUT TYPE=hidden NAME=mv_orsearch     VALUE="yes">
      <INPUT TYPE=text   NAME=mv_searchspec   SIZE="20">
      <INPUT TYPE=submit VALUE="SQL">
 </FORM>

And possibly as well:

      <INPUT TYPE=hidden NAME=mv_substring_match VALUE="yes">

and maybe:

      <INPUT TYPE=hidden NAME=mv_begin_string    VALUE="yes">

Even so, this will not select from the middle of the search field
reliably. SQL doesn't have the oomph in the search area to do that easily,
though there are some SQL implementations which do better than others.

I think it is really ridiculous that you have to put the % in the
LIKE query; if you don't use it I think it should match anywhere in
the string. But so it goes.

In MV4 I will finally be making the SQL search a module just
like TextSearch and Glimpse, so there will be improvements and the
behavior should be more consistent. But you cannot get the power of an
MV-based form search. The only way (I know) to do anything high-end
with SQL is to parse your own SQL queries. And I will not build in
Oracleisms or MySQLisms or Sybase-isms. That would be special case
after special case after special case.....and would totally blow away
MV's database-independence.

-- 
Mike Heins                          http://www.minivend.com/  ___ 
                                    Internet Robotics        |_ _|____
Fast, reliable, cheap.              131 Willow Lane, Floor 2  | ||  _ \
Pick two and we'll talk.            Oxford, OH  45056         | || |_) |
 -- unknown                         <mikeh@minivend.com>     |___|  _ <
                                    513.523.7621 FAX 7501        |_| \_\
-
To unsubscribe from the list, DO NOT REPLY to this message.  Instead, send
email with 'UNSUBSCRIBE minivend-users' in the body to Majordomo@minivend.com.
Archive of past messages: http://www.minivend.com/minivend/minivend-list