SQL for multiple checkboxes

[SoapboxParade]

Hello!

I'm stuck on a SQL statement that I was wondering if someone could possibly assist me with.

I working on a search page for a particular company site of antique dealers. There is a "Search By Categories" section on the main search page where users can select multiple checkboxes to return any antique dealers in those categories.

When the search button is clicked, a comma delimited string of category IDs is passed to the search results page. (ex. "2,14,15,20,22,25,37,40")

Each antique dealer has a database field that contains a comma delimited list of any category IDs that they are associated with since they can be associated with more than one category if they'd like to be...and just about all of them are currently associated with more than one category

SO...we have a string being passed from the search page that could look something like this: 2,14,18,20,22,25,37,40,64

...and in the database each dealer has a DealerCats field for all the categories they're associated with that might look like this: 6,7,18,30,64,66

My question is: how can I write the select statement to basically say "select all dealers from the Dealers table where any of the passed category IDs (clicked on from the search page) exist in the DealerCats field for each dealer?"

I already tried something to the effect of:

SELECT * FROM DEALERS
WHERE DealerCats IN (#FORM.dealerCat#)

...but this did not work. I don't get an error, I just get a blank content area (the rest of the template is not broken in any way...just blank area where either results should appear or a "no results" message should appear.

Also, I DID create a page that displays both the form value of the selected checkboxes as well as any given DealerCats field in the database to make sure that both are comma separated values formatted exactly the same.

Unfortunately, I am unable to provide outside access to the testing server that I'm working off of for this particular company, so I do apologize.

However, if anyone understands what it is I am trying to accomplish and can based on my explanation, I hope that will help.

Feel free to ask for any additional clarification.

Thank you very much!!!

- Tony
Chicago, Freelance Web Developer

[Guest]

You have discovered why databases should be normalized. Do away with the comma-delimited lists in the database. You need three tables - dealers, categories and dealer/categories. This creates a nice many-to-many environment that will be easy to select from and easy to update.

[SoapboxParade]

Hi,
Thanks for responding!

I need a little clarification, if you don't mind, so please bear with me here...

Basically, I created a database admin tool to add/edit/delete dealers and categories because other users will need to eventually need to have access to it. For each dealer record, a list of checkboxes is dynamically created (linked from the categories table) so that any given category can be clicked on that the dealer would like to be associated with.

If all I needed to do is display the categories, this would be fine.

If only ONE category was allowed and I only needed to do a check against one category, this would also probably be fine.

So, I was hoping I could try to still make this work since the database admin tool was proving to work very well up til this point. But you are right...a many-to-many setup is going to be the way to go, it seems (and in most cases, it usually is anyway).

My guess is you're suggesting a 3rd table (a many-to-many table) that has one column that has Dealer IDs and another that has Category IDs that are both linked from the Dealers and Categories tables. Yes?

My quesion is: since each dealer can be associated with more than one category, and the object is to do away with a comma delimited list, are you suggesting it be set up so that each dealer ID would be repeated 1 record at a time for each different category it's associated with? For example:

DealerID | CategoryID
-----------------------------------
1 10
1 15
1 22
1 40
2 1
2 8
2 18
2 21
2 22
2 25
2 35
3 12
3 20
3 34

...etc. etc. etc.

I'm just trying to make sure I'm understanding what you would suggest here.

Thanks so much for your assistance!

Best,
Tony

[Kronin555]

Yes. You never, EVER want a comma-delimited list stored in a single field in a database.

[SoapboxParade]

Thanks.

[Amiable_Penguin15D5]

FYI: Rules of Data Normalization

http://datamodel.org/NormalizationRules.html