Re: [ic] Complex/SQL Search help!

[Brian Kosick <briank@nacs.net>]

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
                                 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