[ic] Complex/SQL Search help!

[Nathan Young]

When you say it works for you, have you tried it in the command line
tool and it returns rows?  The SQL you sent:

SELECT products.* FROM products, zip_zone
         WHERE zip_zone.zip = search_zip
         AND products.zones ~ zip_zone.zone

Has a tilde in it, where I think it should have an equals sign. I would
advocate getting the select to work in a command line tool first.  If
you've done that and it still doesn't work on your page, then I probably
can't help further.

However, if the SQL is not working from the command line, fixing it
would be a step in the right direction, and I could help debug if that's
the case.

----------------->Nathan

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

-- 




=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=


Nathan Young
N. C. Young Design
(707)822-1793
http://ncyoung.com