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 8962 Nov 18, 2003, 6:41 AM
Thread Re: [timbo] adding records - two select fields, one updates the other?
assombracao 8823 Nov 19, 2003, 3:02 AM
Thread Re: [assombracao] adding records - two select fields, one updates the other?
timbo 8844 Nov 27, 2003, 12:07 PM
Thread Re: [timbo] adding records - two select fields, one updates the other?
604 8774 Nov 27, 2003, 1:30 PM
Thread Re: [TheStone] adding records - two select fields, one updates the other?
timbo 8780 Nov 27, 2003, 10:45 PM
Thread Re: [timbo] adding records - two select fields, one updates the other?
604 8830 Nov 28, 2003, 10:43 AM
Thread Re: [TheStone] adding records - two select fields, one updates the other?
timbo 8802 Nov 29, 2003, 12:47 AM
Thread Re: [timbo] adding records - two select fields, one updates the other?
604 8766 Dec 1, 2003, 10:14 AM
Thread Re: [TheStone] adding records - two select fields, one updates the other?
TIF 8208 Nov 9, 2005, 9:02 PM
Thread Re: [TIF] adding records - two select fields, one updates the other?
604 8214 Nov 10, 2005, 9:45 AM
Thread Re: [TheStone] adding records - two select fields, one updates the other?
TIF 8197 Nov 10, 2005, 9:01 PM
Post Re: [TIF] adding records - two select fields, one updates the other?
TIF 8098 Nov 15, 2005, 5:52 AM
Thread Re: [TIF] adding records - two select fields, one updates the other?
604 8059 Nov 17, 2005, 4:54 PM
Thread Re: [TheStone] adding records - two select fields, one updates the other?
TIF 8020 Nov 21, 2005, 8:49 PM
Thread Re: [TIF] adding records - two select fields, one updates the other?
604 8051 Nov 22, 2005, 12:43 PM
Post Re: [TheStone] adding records - two select fields, one updates the other?
TIF 8114 Nov 25, 2005, 8:58 AM