Copy link to clipboard
Copied
I have a database table of options for additions to homes. I have another table for categories for these options. Right now I'm making a lot of queries to get each individual category of options. It looks something like this:
</cfquery>
<cfquery datasource="applewood" username="username" password="password" name="getCategory2" >
SELECT *
FROM op_categories WHERE idOpCategories = 3
</cfquery>
<cfquery datasource="applewood" username="username" password="password" name="getCategory3" >
SELECT *
FROM op_categories WHERE idOpCategories = 4
</cfquery>
...and so on. Is there a way to make 1 query that will find each WHERE clause? I want it to look like this on one page:
Category 1 Category 2 Category 3
--------------- ----------------- ----------------
Option 1 Option 1 Option 1
Option 2 Option 2 Option 2
Option 3 Option 3 Option 3
Any help would be appreciated.
What about this..
<cfquery datasource="applewood" username="username" password="password" name="getAllCategory" >
SELECT *
FROM op_categories WHERE idOpCategories in (3,4)
</cfquery>
Copy link to clipboard
Copied
What about this..
<cfquery datasource="applewood" username="username" password="password" name="getAllCategory" >
SELECT *
FROM op_categories WHERE idOpCategories in (3,4)
</cfquery>
Copy link to clipboard
Copied
I spoke before I tried. thank you, it worked great.
Copy link to clipboard
Copied
We need more information. Do the categories have the same number of options? How are Option 1, Option 2, etc. related?
If you require just a number of option values to build the display, why then do you use 'select *' instead of, say, 'select opID, idOpCategories'? It would help to know more about the columns in op_categories and how they are related.
Copy link to clipboard
Copied
The options category has the actual options plus a FK of the categories. So the category would be "dishwashers" and the options would be "power scrub dishwasher", "Dishwasher". Each category can have multiple options in them. You can see short example at the following address http://www.applewoodcommunity.com/textareaTest.cfm. This shows the format I want to accomplish. Right now each category/option section has it's own query statement.
Copy link to clipboard
Copied
skinneyfat wrote:
The options category has the actual options plus a FK of the categories. So the category would be "dishwashers" and the options would be "power scrub dishwasher", "Dishwasher". Each category can have multiple options in them. You can see short example at the following address http://www.applewoodcommunity.com/textareaTest.cfm. This shows the format I want to accomplish. Right now each category/option section has it's own query statement.
Why mention any categories at all? From the example you show, I would in fact go for the zero-brainer:
<!--- Incidentally, the name of your query says it all! --->
<cfquery datasource="applewood" username="username" password="password" name="getAllCategory" >
SELECT *
FROM op_categories
</cfquery>
Then use query-of-a-query to pick out the categories as, and when, you need them. This offers you flexibility and the economy of just one trip to the database.
<cfquery dbtype="query" name="cat3">
SELECT *
FROM getAllCategory
WHERE idOpCategories = 3
</cfquery>
<cfquery dbtype="query" name="cat4">
SELECT *
FROM getAllCategory
WHERE idOpCategories = 4
</cfquery>
<cfoutput query="cat3">
<!--- Do yer category 3 stuff here --->
</cfoutput>
<cfoutput query="cat4">
<!--- Do yer category 4 stuff here --->
</cfoutput>