[ic] Complex/SQL Search help!

Brian Kosick interchange-users@lists.akopia.com
Tue Jul 17 08:58:01 2001


Thank you, this query works great!  I have the query working in 
Interchange. and now have a new questions/problems


How do I set the results of a sql query to be the vars passed off in the 
[search-list] *display the results* [/search-list] on the default 
results.html page?  Right now, it gives me a correct query, prints out the 
vars, and THEN outputs every single
item in the DB.

Ideally, if possible, on the default results page, I would like to be able 
to do something like:
[if value search_zip]
[query]SELECT ......[/query]
[set search-list = query-results][/set]

OR

[set item-list = query-results][/set]
[/if]

That way, I don't have to have multiple results pages.  Or complex/long 
[if] clauses.


Also, when trying to format the item display, do I have to do it within the 
[query][/query] tag?  (seems that way to me, but maybe I'm missing something)

At 04:40 PM 7/13/01 -0700, Dan B wrote:
>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
>
>_______________________________________________
>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