|
| |
|
|
jonance
Posts: 298
From: Louisville KY USA
Status: offline
 |
Trouble with sql displaying results - 1/17/2008 12:46:32
Hi,
I have a database results page and have the sql set up as follows:
SELECT * FROM listings WHERE (activated = 'yes' AND sic = '::category::' AND zip LIKE '::a::' OR zip LIKE '::b::' OR zip LIKE '::c::' OR zip LIKE '::d::' OR zip LIKE '::e::' OR zip LIKE '::f::' OR zip LIKE '::g::' OR zip LIKE '::h::' OR zip LIKE '::i::' OR zip LIKE '::j::')
The way i see it, this should return only activated listings that match the category passed in the url...and then display all results with the matching zips that fall under those parameters. BUT...it is displaying ALL CATEGORIES for some reason. How can i make it function properly? I've tried many different variations of parenthesis but cannot get it.
Thanks
John
|
|
|
|
rdouglass
Posts: 9280
From: Biddeford, ME USA
Status: offline
|
RE: Trouble with sql displaying results - 1/17/2008 12:54:24
quote:
(activated = 'yes' AND sic = '::category::' AND zip LIKE '::a::' OR zip LIKE '::b::' OR zip LIKE '::c::' OR zip LIKE '::d::' OR zip LIKE '::e::' OR zip LIKE '::f::' OR zip LIKE '::g::' OR zip LIKE '::h::' OR zip LIKE '::i::' OR zip LIKE '::j::')
Use a few more parens. I use them to always separate boolean logic.
(activated = 'yes') AND (sic = '::category::') AND ((zip LIKE '::a::') OR (zip LIKE '::b::') OR (zip LIKE '::c::') OR (zip LIKE '::d::') OR (zip LIKE '::e::') OR (zip LIKE '::f::') OR (zip LIKE '::g::') OR (zip LIKE '::h::') OR (zip LIKE '::i::') OR (zip LIKE '::j::'))
Without parens, they just operate left to right without any groupings. So once any record had any of those zips, it totally disregarded the previous part of the query criteria. What I did was to combine all the "zip OR's" into 1 boolean so all your AND's work left to right now properly. Did that make any sense?
That any better?
_____________________________
Don't take you're eye off your final destination.
ASP Checkbox Function Tutorial.
| |
|
|
|
jonance
Posts: 298
From: Louisville KY USA
Status: offline
|
RE: Trouble with sql displaying results - 1/17/2008 13:10:57
Wow...i must have tried all the combinations but that one...that seems to work but i do have one other problem. My database has zip codes with the 4 digit extension (ex. 40204-5567)...so i need the results to also include ones that "contain" the values sent over in a, b, c, etc.(just 40204) I thought LIKE would cover that but apparently not. Do you know what i need to change to make that happen?
| |
|
|
|
jonance
Posts: 298
From: Louisville KY USA
Status: offline
|
RE: Trouble with sql displaying results - 1/17/2008 13:16:33
Nevermind...i think i got it...just added the % to either side.
'%::a::%', etc.
For future reference, what is the difference? I just tried that b/c i've seen it in my code before.
Thanks
John
| |
|
|
|
rdouglass
Posts: 9280
From: Biddeford, ME USA
Status: offline
|
RE: Trouble with sql displaying results - 1/17/2008 15:01:34
quote:
what is the difference
LIKE '%::text::%' = contains
LIKE '::text::%' = begins with
LIKE '%::text::' = ends with
LIKE '::text::' = equals
That help clear it up?
_____________________________
Don't take you're eye off your final destination.
ASP Checkbox Function Tutorial.
| |
|
|
|
jonance
Posts: 298
From: Louisville KY USA
Status: offline
|
RE: Trouble with sql displaying results - 1/17/2008 15:34:27
Cool...thanks
| |
|
 New Messages |
 No New Messages |
 Hot Topic w/ New Messages |
 Hot Topic w/o New Messages |
 Locked w/ New Messages |
 Locked w/o New Messages |
|
Post New Thread
Reply to Message
Post New Poll
Submit Vote
Delete My Own Post
Delete My Own Thread
Rate Posts
|
|
|
Printable Version 
