[ic] Complex/SQL Search help!

Dan B interchange-users@lists.akopia.com
Fri Jul 13 19:38:00 2001


At 05:15 PM 7/13/2001 -0400, you wrote:
>Thanks for the hints...  I'm still having trouble though..
>
>Here is the query that actually works that way I need it to.  Both as a 
>subquery and as a join.
>
>SELECT * from products
>         where zones  ~ (
>                 SELECT zone from zip_zone
>                 where zip = search_zip
>                 )
>
>SELECT products.* FROM products, zip_zone
>         WHERE zip_zone.zip = search_zip
>         AND products.zones ~ zip_zone.zone
>
>Here is the code on my search page:
>The testprofile is set, because I have  few other searches on the page, 
>and don't want to get them confused.
>
><snip>
>
>[set testprofile][/set]
><form action="[area search]" METHOD=POST>
><INPUT TYPE="HIDDEN" NAME="mv_profile" VALUE="testprofile">
><input type=text name="search_zip" size=3 maxlength=3>
><INPUT TYPE="hidden" NAME="mv_doit" VALUE="search">
><INPUT TYPE="HIDDEN" NAME="mv_sql_query" VALUE="
>                         SELECT products.* FROM products, zip_zone
>                                 WHERE zip_zone.zip = search_zip
                                                       ^^^^^^^^^^

I think your problem is that the mv_sql_query thinks that search_zip is a 
SQL static value.  Changing it to
         WHERE zip_zone.zip = '[value search_zip]'
probably wont fix the problem either, since [value search_zip] doesn't mean 
anything until *after* you hit the submit button.  You could try setting 
mv_sql_query within mv_check, but I think there might be another problem as 
well, read on.

I haven't tried your code out yet, but it seems like you are using the 
wrong tools for the job.  See 
http://interchange.redhat.com/cgi-bin/ic/docfly.html?mv_arg=icdatabase05%2e05

<QUOTE>
If Jochen Wiedmann's SQL::Statement module is installed, a SQL syntax can 
be specified for the text-based search. (This is not the same as 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 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">
</QUOTE>

It sounds to me like you have a PostgreSQL database, not a txt database -- 
so shouldn't you be using the [query] tag instead of the 
mv_sql_query?  Besides that, I don't know if SQL::Statement supports joins 
or subqueries.  I wouldn't know because I've never tried the mv_sql_query 
method.  However, I do know that the following works:

page1.html:
[set testprofile][/set]
<!-- Note: -->  <form action="[process]" METHOD=POST>
<INPUT TYPE="HIDDEN" NAME="mv_profile" VALUE="testprofile">
<input type=text name="search_zip" size=3 maxlength=3>
<!-- Note: --> <INPUT TYPE="hidden" NAME="mv_doit" VALUE="return">
<!-- Note: --> <INPUT TYPE="hidden" NAME="mv_nextpage" VALUE="page2">
<br><hr>
</center>
<p>
<center>
<INPUT TYPE="submit" VALUE="[L]Search[/L]">
<br>
</center>
</form>


page2.html:

[query list=1 st=db sql=|
         SELECT products.* FROM products, zip_zone
         WHERE zip_zone.zip = search_zip
         AND products.zones ~ zip_zone.zone;
         |
]
         [sql-param sku]
         [sql-param description]
         etc.

         [item-list]
                 [item-price]
                 etc.
         [/item-list]
[/query]

So if the using [value search_zip] or setting mv_sql_query using mv_check 
works, then all the better, but you might have to change tools.  Let me 
know you need more help figuring out the mv_check method.  But I use 
[query] everywhere, so I can claim ignorance.  :-)

-Dan

PS.  I heartily prepare myself at each posting for the potential of a 
Heinstrike.  Heinstrike is much like the feared German "blitzkrieg", except 
it is when Mike Heins solves a problem in 2.2 seconds that took me 2.2 
months just to understand.  But such is the benefit (and the welcome 
humility) of having him around.  Thanks again Mike for browsing the list 
(Jon and Mark too).




>                                 AND products.zones ~ zip_zone.zone">
>
>
><br><hr>
></center>
><p>
><center>
><INPUT TYPE="submit" VALUE="[L]Search[/L]">
><br>
></center>
></form>
>
></snip>
>
>This is the error I get on the *default* results page:
>
>         Sorry, no matches for ARRAY\(0x90aef9c\) AND BAD_SQL
>
>
>And for you really sadistic types here is the URL to the page:
>
>http://dev.bluestoneperennials.com/cgi-bin/bluestone.cgi/adv_search.html
>
>(What I would eventually be able to do is join the zip form and the 
>category form into one.)
>
>Thanks!
>
>
>
>At 09:44 AM 7/13/01 -0700, you wrote:
>>Hi Brian.
>>
>>I've been told that if possible you should replace nested queries with
>>joins wherever possible.  A join can get all your data into one row, and
>>it runs much faster than sub selects do.
>>
>>My understanding of your problem is that you are trying to draw
>>information about a product from a related zip_zone table where the
>>product table is related to the zip_zone table by the zone field.
>>
>>The SQL you gave was:
>>
>>select * from products where zone = (
>>                                  SELECT zone from zip_zone
>>                                  where zip = zip_code
>>                                  )
>>
>>As I read it, an equivalent join would be:
>>
>>select products.* from products, zip_zone where products.zone =
>>zip_zone.zone and zip_zone.zip = zip_code
>>
>>Give it a try in a more interactive environment (like the mysql command
>>line tool) and see if it works.  You'll have to put in a real value for
>>zip_code of course...
>>
>>---------------->Nathan
>>
>>
>>
>>
>>Brian Kosick wrote:
>> >
>> > Hello all,
>> >         I have been trying to get a search to work all day.  First let me
>> > explain.  I have a two tables my products table and a zip_zones table with
>> > two columns zip and zone
>> > I need to do a search on a user inputted zip to return which should return
>> > the zone that it's in then do a lookup on the products table to match the
>> > products in that zone.  The docs say no nested searches allowed, so I have
>> > to figure out how to do it with SQL
>> >
>> > <psuedo code>
>> > <input type="text" name="zip_code">
>> > <input type="mv_sql_query" VALUE="
>> >                         select * from products where zone = (
>> >                                 SELECT zone from zip_zone
>> >                                 where zip = zip_code
>> >                                 )">
>> >
>> > All I get is an error usually stating "No match for zip_code" or invalid
>> > search string "zip_code"
>> >
>> > What am I missing?
>> >
>> >
>> > Brian Kosick
>> > Web Programmer
>> > New Age Consulting Service, Inc.
>> > 216-619-2000
>> > briank@nacs.net
>> >
>> > _______________________________________________
>> > Interchange-users mailing list
>> > Interchange-users@lists.akopia.com
>> > http://lists.akopia.com/mailman/listinfo/interchange-users
>>
>>--
>>
>>
>>
>>
>>=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
>>
>>
>>Nathan Young
>>N. C. Young Design
>>(707)822-1793
>>http://ncyoung.com
>>_______________________________________________
>>Interchange-users mailing list
>>Interchange-users@lists.akopia.com
>>http://lists.akopia.com/mailman/listinfo/interchange-users
>
>Brian Kosick
>Web Programmer
>New Age Consulting Service, Inc.
>216-619-2000
>briank@nacs.net
>
>_______________________________________________
>Interchange-users mailing list
>Interchange-users@lists.akopia.com
>http://lists.akopia.com/mailman/listinfo/interchange-users

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