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