[ic] Complex/SQL Search help!
Brian Kosick
interchange-users@lists.akopia.com
Tue Jul 17 11:38:01 2001
OK I have answered my own questions.... Thanks to all who replied.
At 08:17 PM 7/16/01 -0400, you wrote:
>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
>
>_______________________________________________
>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