[Date Prev][Date Next][Thread Prev][Thread Next][Interchange by date
][Interchange by thread
]
Re: [ic] Complex/SQL Search help!
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
