If you can use a stored procedure and a temp table, this may work for your: The whole category tree would be contained in one query with the proper order <cfoutput query="query name"> <a href="browse.cfm?parent_id=#id#>#Category_Name#</a> >> </cfoutput> ---------------------------------------------------------------------------- ---- CREATE PROCEDURE [sp_cat_tree] (@cat_ID int = -1 ) AS /* This procedure returns a table containing all the information of a category tree in proper order The results can then be looped to display the category infromation. */ declare @order_number int select @order_number = 1 -- temp table to store ID and name CREATE TABLE #temp (ID int NOT NULL, Category_Name varchar(80), Parent_ID int Not Null, OrderNum int not NULL) -- Populate temp table with category tree information. The "@order_number < 40" prevents a runaway due to bad tree data. while @order_number != -1 and @order_number < 40 begin if exists(SELECT * FROM category WHERE id = (@cat_ID)) begin -- Insert the categroy information INSERT #temp (ID, Category_Name, Parent_ID, OrderNum) SELECT id, Category_Name, Parent_ID ,@order_number FROM category WHERE (id = @cat_ID) -- Set Cat_ID to the Parent_ID for the next pass through the loop SELECT @cat_ID = isnull(Parent_ID,-1) FROM category WHERE (id = @cat_ID) end else begin SELECT @cat_ID = -1 end SELECT @order_number =@order_number + 1 end -- Return the Category Tree in the proper order select ID, Category_Name, Parent_ID from #temp order by OrderNum desc -- Remove the table Drop table #temp ---------------------------------------------------------------------------- - ----- Original Message ----- From: "Tony Hicks" <[EMAIL PROTECTED]> To: "SQL" <[EMAIL PROTECTED]> Sent: Thursday, May 17, 2001 7:52 AM Subject: Help > I have passed this question through so many cf communities... and it has still left me > > <cf_dumbfounded> > > Suppose you have a db with a table called categories.. > > The fields are: > > ID: Each Category's Unique ID > Parent_ID: This field is the ID of the Parent Category > Category_Name: The text to display to represent the category... > > So when you click a link like this: <a href="browse.cfm?parent_id=5>Test Category</a> (The unprocessed CF for that is <a href="browse.cfm?parent_id=#id#>#Category_Name#</a> with a query that tells it to select just from the parent_id..)... > > Well that all works fine but I want to display the Level history at the top like so: > > Companies >> Brick and Mortar >> United States >> Arkansas >> Sevier County > > And if they were on the page listing all the counties in Arkansas it would be: > > Companies >> Brick and Mortar >> United States >> Arkansas > > Hopefully you all get the idea... does anyone know what the proper cf code would be to display this? Allaire Forums have helped me the best they can but the code people give me either errors out or loops into itself, thus locking up the server... > > Thanks, > Tony Hicks > > I'd be glad to provide as much information as needed. > > > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Structure your ColdFusion code with Fusebox. Get the official book at http://www.fusionauthority.com/bkinfo.cfm Archives: http://www.mail-archive.com/[email protected]/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
