Gossamer Forum
Home : Products : DBMan SQL : Discussion :

adding records - two select fields, one updates the other?

Quote Reply
adding records - two select fields, one updates the other?
Hi,

I have a tricky problem. To explain it I need to describe the database:

company - pk : comp_id - list of companies (2000)
product - pk : product_id - list of products (222 )
category - pk : cat_id - list of 7 categories; each product can belong to 1 or more category; hence
category_product - pk : cat_id, product_id - one row for each valid category / product combination. (295 rows)

Ok so far?

Companies can edit their own details and can make their own entries for the products they produce. These entries go into the following table:

company_product - pk : company_id, cat_id, product_id - one row for each company / category / product. (Maybe I should have called this table company_category_product - but it seemed a bit long) (4500)

All of this is working.

The problem is with the company_product add form. This has two dropdowns on it - one for category and one for product. Companies choose a category and then choose a product. But it's possible for them to choose illegal category / product combinations, and it's likely that they will.

So how to solve this?

The ideal would be that when the company chooses a category, the list of available products is dynamically updated from the database, by running a query against the category_product table. I'd be very interested in hearing from anyone who thinks they can do this, even if they want paying (GT, anybody else ...?). I'm not keen on solutions involving javascript with all the data embedded in the code...

Other workarounds:

idea 1) - build in a checking routine so illegal category / product combinations can't be saved. Companies might end up getting a lot of error messages which would be annoying.

idea 2) - split the company_product add form into two forms. On the first form choose a category and click "next". Depending on category chosen go to 1 of 7 pages where sub-list of products is displayed. This would be quite acceptable I think, and sounds easier to me than the idea solution above.

idea 3) - can't think of one.


All suggestions welcomed.

thanks
Tim Ault
Oxford UK
Subject Author Views Date
Thread adding records - two select fields, one updates the other? timbo 7367 Nov 18, 2003, 6:41 AM
Thread Re: [timbo] adding records - two select fields, one updates the other?
assombracao 7257 Nov 19, 2003, 3:02 AM
Thread Re: [assombracao] adding records - two select fields, one updates the other?
timbo 7273 Nov 27, 2003, 12:07 PM
Thread Re: [timbo] adding records - two select fields, one updates the other?
604 7213 Nov 27, 2003, 1:30 PM
Thread Re: [TheStone] adding records - two select fields, one updates the other?
timbo 7214 Nov 27, 2003, 10:45 PM
Thread Re: [timbo] adding records - two select fields, one updates the other?
604 7263 Nov 28, 2003, 10:43 AM
Thread Re: [TheStone] adding records - two select fields, one updates the other?
timbo 7235 Nov 29, 2003, 12:47 AM
Thread Re: [timbo] adding records - two select fields, one updates the other?
604 7191 Dec 1, 2003, 10:14 AM
Thread Re: [TheStone] adding records - two select fields, one updates the other?
TIF 6636 Nov 9, 2005, 9:02 PM
Thread Re: [TIF] adding records - two select fields, one updates the other?
604 6649 Nov 10, 2005, 9:45 AM
Thread Re: [TheStone] adding records - two select fields, one updates the other?
TIF 6634 Nov 10, 2005, 9:01 PM
Post Re: [TIF] adding records - two select fields, one updates the other?
TIF 6535 Nov 15, 2005, 5:52 AM
Thread Re: [TIF] adding records - two select fields, one updates the other?
604 6498 Nov 17, 2005, 4:54 PM
Thread Re: [TheStone] adding records - two select fields, one updates the other?
TIF 6445 Nov 21, 2005, 8:49 PM
Thread Re: [TIF] adding records - two select fields, one updates the other?
604 6484 Nov 22, 2005, 12:43 PM
Post Re: [TheStone] adding records - two select fields, one updates the other?
TIF 6542 Nov 25, 2005, 8:58 AM