Problems with nested loops

[Leakyradiator]

I have four tables in my database:
Org, Cat, Subcat and Links

Links is a linking table that contains the primary keys of Org, Cat, and Subcat for each record.

I'm trying to present each org. with all its categories, and present each subcategory for each category.

Here's the code I've been working with (unsuccessfully).
(I haven't even tried working with the subcategories yet).

<cfquery name="link1" datasource="#db#" username="#un#" password="#pw#">
select *
from links
</cfquery>

<cfloop query = "link1">
<cfquery name="org1" datasource="#db#" username="#un#" password="#pw#">
select *
from org
where org_id = #link1.org_id#
order by org_name1
</cfquery>
<cfset thiscatID = link1.cat_id>
<cfoutput>orgname: #org1.org_name1#<br></cfoutput>
<cfloop query="link1"><!---loop through link for org id to find catno--->
<cfquery name="getcat1" datasource="#db#" username="#un#" password="#pw#">
select cat_id, category
from categories
where cat_id = #thiscatID#
</cfquery>
<cfset categ=getcat1.category>
<cfloop query="getcat1">
<cfoutput>
Category #categ#<br>
</cfoutput>
</cfloop>
</cfloop>
</cfloop>

All help is appreciated greatly.
Thanks

 

[TruthInSatire]

you'll want to do some joins in your query to make all your results return with one query. and some nested cfoutputs to loop through each result.

If you don't ask the right questions, you don't get the right answers. A question asked in the right way often points to its own answer. Asking questions is the ABC of diagnosis. Only the inquiring mind solves problems.

-Quote by Edward Hodnett

 

[r937]

any time you find yourself doing a query inside a loop, there's a good chance it is wildly inefficient and can be improved using a join

i know bombboy already said the same thing, i just wanted to emphasize it

also, the fact that you have separate category and subcategory tables may need looking at -- best practice is to use the same table for these

rudy | r937.com | Ask the Expert | Premium SQL Articles
SQL for Database-Driven Web Sites (next course starts March 6 2005)

 

[TruthInSatire]

Rudy,

How would you personally do that?

cats to subCats is one to many, why/how would you put them in the same table?



If you don't ask the right questions, you don't get the right answers. A question asked in the right way often points to its own answer. Asking questions is the ABC of diagnosis. Only the inquiring mind solves problems.

-Quote by Edward Hodnett

 

[r937]

create table Categories
( catid integer not null
, catdesc varchar(25) not null
, catparent integer null
, primary key (catid)
, foreign key (catparent)
references Categories (catid)
)

rudy | r937.com | Ask the Expert | Premium SQL Articles
SQL for Database-Driven Web Sites (next course starts March 6 2005)

 

[ecobb]

Cool! The "catparent" could be NULL or maybe 0 for the parents and all other subCats would just point to the ID of the parent. I've done this before with product lists and such. Works nicely.



Hope This Helps!

Ecobb
Beer Consumption Analyst

"My work is a game, a very serious game." - M.C. Escher