-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1
HI, Tony.
I did something very similar with organizing our Streaming Media; what might be
helpful is another table that stores the "tree" information.
So:
CATEGORIES TABLE:
Id parent_id name
1 1 root
2 1 directory
3 2 subdirectory
4 3 subsubdirectory
CATEGORIES_TREE TABLE:
Id tree
1 1
2 1
3 1,2
4 1,2,3
Whenever you create a new category, you'll have to populate it's tree. Then to
output the tree structure, just query the categories table for the tree
information for that category:
<!--- tree_query --->
SELECT tree
FROM CATEGORIES_TREE
WHERE id = url.parent_id <!--- in your case --->
<!--- information about tree --->
SELECT category_name
FROM CATEGORIES
WHERE id IN ('#tree_query.tree#')
If you want to build this tree from existing data (this is *very* expensive,
depending on the depth and size of your category hierarchy:
<cfquery name="folder_full" datasource="#application.datasource#"
username="#application.username#" password="#application.password#">
SELECT DISTINCT parent_id
FROM folder
WHERE folder_id > 1
</cfquery>
<cfloop query="folder_full">
<cfquery name="folder_children" datasource="#application.datasource#"
username="#application.username#" password="#application.password#">
SELECT id, parent_id
FROM folder
WHERE parent_id = #folder_full.parent_id#
</cfquery>
<cfquery name="folder_parent_tree" datasource="#application.datasource#"
username="#application.username#" password="#application.password#">
SELECT folder_id, folder_tree
FROM tree
WHERE folder_id = #folder_full.parent_id#
</cfquery>
<cfloop query="folder_children">
<cfset folder_tree = folder_parent_tree.folder_tree>
<cfset folder_tree = ListAppend(folder_tree,folder_children.folder_id)>
<cfquery name="folder_children_tree_insert"
datasource="#application.datasource#" username="#application.username#"
password="#application.password#">
INSERT INTO video.tree (folder_id, folder_tree)
VALUES (#folder_children.folder_id#,'#folder_tree#')
</cfquery>
</cfloop>
</cfloop>
I'm sure there are better ways to accomplish this, and there's obviously an
upper limit to the tree depth, but if you hover around 5 or 6, a smallish
character field will be enough.
Hope this helps!
Joe
==============================================
Joseph Cruz
Programmer/Analyst
Wharton Computing
3620 Locust Walk, Suite 400
Philadelphia, PA 19104-6302
(215) 898-1220 (w)
(215) 308-0657 (p)
(215) 768-2071 (m)
[EMAIL PROTECTED]
============================================
> -----Original Message-----
> From: Tony Hicks [mailto:[EMAIL PROTECTED]]
> Sent: Thursday, May 17, 2001 7:52 AM
> To: SQL
> 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
