state/prov search

On site already developed is a building listing database table with a state/prov field.

on the site right now they have a few links in the navigation that selects all the building in a state by have the URL variable set to the 2 character format of the state or province and then does a search in the database using the query string of:

bState LIKE '%#url.state#%'

I have got it working so that it will display the buildings in that one location

What I need to do now thought is do a search for all other locations.

The table field stores the locations in a list format

ex: PE,CA,TX

There will only be a set number of locations that a user can choose using the navigation. So what the it needs to do is select any and all items that have NF or BC or AK...


but the list my also contain the location that is in the navigation.


Any suggestions?

Confused yet.

i wouldn't pass them with URL.
suppose, on the first form, you have a field called 'State'.

on the processing page, all you need to do is

<cfquery name="getBuildings" datasource="yourSource">
SELECT * FROM yourTable
WHERE State IN (<cfqueryparam list="yes" value="#FORM.State#">)
</cfquery>

hope it helps....

Yep, I'm confused.

But first, why are you using LIKE and not = (the equals operator)?

WHERE bState = #url.state#

Second, if you have a list of things you want to query for, you can use IN:

WHERE bState IN ('MA','NC','CA')

In refrences to FALCONSEYE's replay:
This will work when there is only one item but I don't think it will work the best when the user chooses, the all other options from the navigation. all the links to the list will be comming from a navigation button and not a form, there is only 5 options that are required. all, 3 states, and all others.


In refrences to kiteless's replay:
The reason why I was useing 'LIKE' and not '= or IN' was with it beeing a list the '=' will not work and for some reason I kept on getting error with the IN option.

But with both of your replays I was able to come up with an option that will work

thank you.