[ic] Complex/SQL Search help!
Brian Kosick
interchange-users@lists.akopia.com
Fri Jul 13 18:50:00 2001
Correct a ~ is a valid RE in Postgres SQL and it does return the rows that
I want. Both queries have been verified to work and return the same
results with psql and postgres 7.02.
Part of the problem is that the product zones column entries look like
"1-5,6,7,8-10" for the range of zones that it's in.
Basically what I need to do is take the zip, figure out the zone number for
that zip, then check the products zones to see if it has that number in
it's range. Take the results and display them on the default results page.
(like was not returning the correct/any rows.)
At 03:23 PM 7/13/01 -0700, you wrote:
>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
>_______________________________________________
>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