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 5996 Nov 18, 2003, 6:41 AM
Thread Re: [timbo] adding records - two select fields, one updates the other?
assombracao 5902 Nov 19, 2003, 3:02 AM
Thread Re: [assombracao] adding records - two select fields, one updates the other?
timbo 5914 Nov 27, 2003, 12:07 PM
Thread Re: [timbo] adding records - two select fields, one updates the other?
604 5860 Nov 27, 2003, 1:30 PM
Thread Re: [TheStone] adding records - two select fields, one updates the other?
timbo 5853 Nov 27, 2003, 10:45 PM
Thread Re: [timbo] adding records - two select fields, one updates the other?
604 5903 Nov 28, 2003, 10:43 AM
Thread Re: [TheStone] adding records - two select fields, one updates the other?
timbo 5876 Nov 29, 2003, 12:47 AM
Thread Re: [timbo] adding records - two select fields, one updates the other?
604 5835 Dec 1, 2003, 10:14 AM
Thread Re: [TheStone] adding records - two select fields, one updates the other?
TIF 5274 Nov 9, 2005, 9:02 PM
Thread Re: [TIF] adding records - two select fields, one updates the other?
604 5292 Nov 10, 2005, 9:45 AM
Thread Re: [TheStone] adding records - two select fields, one updates the other?
TIF 5277 Nov 10, 2005, 9:01 PM
Post Re: [TIF] adding records - two select fields, one updates the other?
TIF 5181 Nov 15, 2005, 5:52 AM
Thread Re: [TIF] adding records - two select fields, one updates the other?
604 5145 Nov 17, 2005, 4:54 PM
Thread Re: [TheStone] adding records - two select fields, one updates the other?
TIF 5084 Nov 21, 2005, 8:49 PM
Thread Re: [TIF] adding records - two select fields, one updates the other?
604 5122 Nov 22, 2005, 12:43 PM
Post Re: [TheStone] adding records - two select fields, one updates the other?
TIF 5175 Nov 25, 2005, 8:58 AM