Search results out comming throught correctly

I am having a problem with my search tool that is based on the a type of business it is.

right now the user has to choose if the business there adding to a database is:

Dining
Shopping
Entertainment
Services

The user can choose 1 or many of theses items which are all stored in a field as a list.

on the front end when a visitor of the site does a search they had the same list of items as check boxes and none or many of the items.

my problem is in the query statement I have it set-up like this:


if the user chooses none then it will bring back everything, if they choose multiple it will bring back all the business that have any one of there selections

ex:

if you choose Dining and Shopping it should bring back all the business that are set to Dining, and or Shopping

but right now it is having a problem bring back a business that has multiple types.

Andy suggestion

thank you

I don't understand why it wouldn't work. Is the database normalized? In other words, do you have a table for the business types, and a table for the businesses, and a table that relates businesses to business types? Or are you trying to store a list of business types along with each business? I would definitely use the first approach.

unfortunately it is only using one table with the type as just on field and all the entries are in the one filed comma delimited. It was set-up before and if possible I rather not re-create the table structure for the site.

Well then you're going to have a hard time making it work, specifically because if the order of the types is not exactly the same as it is in the database you won't get a match anyway. You can try looking into database functions that let you search within a string (in Oracle substr()), but that is going to be very slow especially if you get a lot of records in the table. I'd strongly recommend normalizing the database correctly, but that's up to you in the end. But be sure you recognize that you're doing this in a very inefficient way.

I am aware that there is some problems with the database but it was developed already. But I thought the 'IN' function was used for a list, where it is looking for the word/words in the one field.

No, you are misunderstanding what IN does. IN allows you to pass a comma-delimited list of values and the database will find any single record that has any ONE of the values you specified. But it has nothing to do with searching a comma-delimited list of values within a single table row and column which is what you are trying to do.

Here is an example:

http://www.1keydata.com/sql/sqlin.html

Thank you kiteless

I will redevelop the database when we have time fo the time beeing I will awt it up to use the 'LIKE' function. I know it isn't the best option but it will work

Yes, LIKE should also work for what you need. Again though, if the table gets large this will run very slowly (LIKE is one of the slowest SQL operators).