> I've got an existing database table that contains contact
> (address, city, email, etc) information and OnOff states
> for approximately 30 products.
>
> Table Structure:
> company_ID
> company_Name
> company_Address
> company_City
> company_Email
> company_RedBalloons
> company_GreenBalloons
> company_RedBall
> company_GreenBall
> etc...
>
> To top it off, there's another db table that contains the
> fieldname and a longer description:
> product_ID
> product_longdesc = this contains description information (eg.
> A bag of Red Balloons)
> product_fieldname = this actually contains field names from
> the company db (eg. like company_RedBalloons)
>
> I need to query a row of the company table and based on
> Yes/No states of the database list the product_longdesc in
> my CF page for a particular company. Basically, I need to
> display a page of everything the company sells. I'm
> not having much luck.
Yikes!
There are two answers to your problem, if I understand it correctly.
The first answer is that you could query the company table, build a list
using conditional logic that contains the fieldnames which have an "on"
value,
<CFSET MyListOfProducts = "">
<CFIF MyQuery.company_RedBalloons>
<CFSET MyListOfProducts = "'company_RedBalloons' ">
</CFIF>
<CFIF MyQuery.company_GreenBalloons>
<CFSET MyListOfProducts = MyListOfProducts & "'company_GreenBalloons' ">
</CFIF>
...
<!--- this gets rid of the final space, and converts the other spaces to
commas --->
<CFSET MyListOfProducts = Replace(Trim(MyListOfProducts), " ", ",", "ALL")>
then query the product table using that list with the IN operator:
SELECT product_longdesc
FROM product
WHERE product_fieldname IN (#MyListOfProducts#)
Conceivably, you could do this in easier ways within a stored procedure.
OK, that's the first answer. The second answer is, build a normalized
database! Your current schema has lots of problems, in addition to the
problem that you're trying to fix right now. What happens when there's a new
product, for example? You'll have to modify your data schema. Create a new
table to link company and product. Once you've done that, it will be
relatively trivial to find all the products for a company, or all the
companies that sell one or more specific products.
Dave Watts, CTO, Fig Leaf Software
http://www.figleaf.com/
voice: (202) 797-5496
fax: (202) 797-5444
------------------------------------------------------------------------------
Archives: http://www.eGroups.com/list/cf-talk
To Unsubscribe visit
http://www.houseoffusion.com/index.cfm?sidebar=lists&body=lists/cf_talk or send a
message to [EMAIL PROTECTED] with 'unsubscribe' in the body.
