ASP.NET filter results with checkboxes

Question

I have a list of about 20 products, each with up to 30 possible attributes. I’m trying to figure out the best way to use checkboxes (representing the 30 possible attributes) on a form to filter the products, so that only products with the matching attributes would be shown. I can use SQL Server 2005, but it seems like that might be overkill. Any suggestions?

(Additional) Edit: Ok, given the data structure below, how would you query the database to return products that have ALL of the matching features? Say Product #1 has features 1, 2 and 3. Product # 2 has features 2, 3 and 4. A query for features 1 and 3 should return Product #1, but not Product #2.


Products table
productID int
productname nvarchar(50)

Features table
featureID int
featurename nvarchar(50)

FeatureMap table
featuremapID int
productID_fk int
featureID_fk int

Are your products already in the database? If so, how do you identify the attributes for each product? — Dillie-O, Feb 16 '11 at 17:26
@Dillie-O - I've been given a paper list. This is basically for a features page for marketing purposes. — Eyeball, Feb 16 '11 at 17:27
If you arent using SQL, what are you using to store your products and attributes? — Jeff, Feb 16 '11 at 18:06

score 2 · Accepted Answer · answered Feb 16 '11 at 21:41

First you would gather the properties that the user selected from your UI

List<string> filter = this.GetAttributeFilterFromUI();
string featureNamesParam = "(" + string.Join(",", filter) + ")";

Then you should be able to use a parameterized query, whose SQL would be something like:

SELECT ProductID FROM Products P
JOIN FeatureMap FM ON FM.ProductID = P.ProductID
WHERE FM.featureID_fk IN
(SELECT FeatureID FROM Features WHERE featurename IN @FeatureNames)

score 1 · Answer 2 · answered Feb 16 '11 at 18:06

The database would work fine for this. If the attributes for your products is fixed, you can create a single table with your product Id/Name and each attribute would be in a column with boolean/bit types named "IsGreen", "IsBlue", "HasWarrant", etc. If you think atrributes may be added or removed over time, you'll want to setup an "Attribute", "Product" and "ProductAttribute" tables to match up the products and attributes accordingly.

You can then setup your ASP.Net web page to use a CheckBoxList control that has all of the options available. You can then go through the list of check box items in this list to determine which values have been selected and setup your database query from there.

If you want a "dynamic" approach to your search results, set the AutoPostBack property of the CheckBoxList to "true" and then every time a check box is selected or deselected, the SelectedIndexChanged will be triggered and you can update your search results accordingly. This way the user doesn't have to press a "search" button every time.

Replace "Attribute" and "ProductAttribute" with "Feature" and "ProductFeature" accordingly, I'm just now seeing your update. 8^D — Dillie-O, Feb 16 '11 at 18:07
>"You can then go through the list of check box items in this list to determine which values have been selected and setup your database query from there." This is the part that I'm stuck on. I don't really know how to approach writing this query. The only thing that comes to mind is using inline SQL, appending each selection as an 'and', but I'm limited to stored procedures here. — Eyeball, Feb 16 '11 at 18:21
Stored procedures are perfect for something like this. Check out the following question on how to generate your stored procedure: http://stackoverflow.com/questions/205526/how-do-i-create-a-stored-procedure-that-will-optionally-search-columns — Dillie-O, Feb 16 '11 at 20:44

score 0 · Answer 3 · answered Feb 16 '11 at 17:54

0

Hi SQL Server is not overkilling, you can use it very easily to create the Product table and insert the data. After you tell us the schema you have created for your table we could see how to handle this.

answered Feb 16 '11 at 17:54

Davide Piras

43,984
10
98
147

ASP.NET filter results with checkboxes

3 Answers3