List layout? (messages) 2

[Bramvg]

Hi there,

I have problems with outputting a list of products and 'subproducts'.

My database table looks like this:

ID - ParentID - Description
ID= unique ID
Parent ID = the referring ID if it's a 'subproduct'.

E.g.:

1 - 0 - Software
2 - 0 - Hardware
3 - 1 - Games -> 1 indicates Games is a sub of Software
4 - 1 - Financial

So far no problems, but I also have sub-subcategories.
E.g.:

5 - 3 - Strategy Games -> here the 3 indicates it's a sub category of the subcategory Games.

Presented in another layout:

1. Software
1.1 Games
1.1.1 Strategy Games

How can I create a layout that gives me this layout automatically, knowing that the number of sublevels is NOT restricted to a certain number.

And considering I 'only' have 3 fields in my database table:

ID - Parent - Description.

Any help MORE than welcome!
Bram

 

[ram123]

Using recursion it is possible to display like that.

 

[Bramvg]

Hello ram123,

I don't realy understand what you mean. Could you specify your answer more?

Many thanks in advance.
Bram

 

[ram123]

I dont know the exact solution, but something like this should work. It may take couple of days to write the entire code.

<cfquery name=&quot;toplevelquery&quot; dsn=&quot;mydsn&quot;>
select * from tbl where parentid = 0
</cfquery>


<cfoutput query=&quot;toplevelquery>
#ID#. #DESC#
<!--- call the custom tag and pass this #ID#--->
</cfoutput>



Custom Tag-------------
<cfquery name=&quot;customqry&quot; dsn=&quot;mydsn&quot;>
select * from tbl where parentID = attribute.variable
</cfquery>

<cfoutput query=&quot;customqry&quot;>
display the record here
<cfif customqry.parentid neq 0>
it means its still having subitems, so call the
custom tag again.
<!--- call the custom tag and pass this #ID#--->
</cfif>
</cfoutput>

 

[Bramvg]

Hi Ram123,

One last question!
It looks nice a superb way of resolving my problem, so tnx for that.

The only problem i have now is 'calling' the variable.
I use:

<cfparam name=&quot;Attributes.datascource&quot; default=#caller.dsn#>
because my Datasourcename is stored inside the application.cfm

But
When I do:

<cfparam name=&quot;Attributes.parent&quot; default=#parentID#>
Or:
Default=caller.parentID
Default=request.parentID
default=Attribute.parentID

I get an error message everytime, telling me CF is unable to determine this variable....

Any suggestions?

Tnx
bram

 

[ram123]

can you paste the code, i will try figure out the problem

 

[ram123]

can you paste the code, i will try to figure out the problem

 

[Bramvg]

Hi,

This is my code
----------------------

Main page:
************
<CFQUERY NAME=&quot;List&quot; DATASOURCE=&quot;#DSN#&quot;>
SELECT Categorieen_autoID, Categorieen_Parent, Categorieen_Active, Categorieen_Omschrijving FROM T_Categorieen
WHERE Categorieen_Active = 1
ORDER BY Categorieen_Omschrijving, Categorieen_autoID, Categorieen_Parent ASC
</CFQUERY>

<CF_ctsubcategories>
<ul>
<CFLOOP QUERY=&quot;List&quot; STARTROW=&quot;1&quot; ENDROW=&quot;#list.recordcount#&quot;>

<cfif #Categorieen_Parent# EQ 0>
<CFOUTPUT>
<li>#Categorieen_Omschrijving#<br>
</CFOUTPUT>
</CFIF>

<CFQUERY NAME=&quot;Sub&quot; DATASOURCE=&quot;#DSN#&quot;>
SELECT Categorieen_autoID, Categorieen_Parent, Categorieen_Active, Categorieen_Omschrijving FROM T_Categorieen
WHERE Categorieen_Active = 1 AND Categorieen_Parent IN( #Categorieen_autoID#)
ORDER BY Categorieen_Omschrijving, Categorieen_autoID, Categorieen_Parent ASC
</CFQUERY>
<ul>
<CFOUTPUT query=&quot;sub&quot;>
<LI>#Categorieen_Omschrijving#
<BR><span class=&quot;smalltxt&quot;><A HREF=&quot;update_01.cfm?ID=#Categorieen_autoID#&quot;>Update</A> - <a href=&quot;delete_01.cfm?ID=#Categorieen_autoID#&quot;>Delete</A></span>
</cfoutput>
</ul>


</CFLOOP>
</ul>



My Custom Tag code:
***********************
<cfparam name=&quot;Attributes.datascource&quot; default=#caller.dsn#>

<cfoutput>
#request.Categorieen_autoID#
</cfoutput>


<CFQUERY NAME=&quot;Sub&quot; DATASOURCE=&quot;#Attributes.datascource#&quot;>
SELECT Categoriee, Categorieen_Parent, Categorieen_Active, Categorieen_Omschrijving FROM T_Categorieen
WHERE Categorieen_Active = 1 AND Categorieen_Parent IN( #Categorieen_autoID#)
ORDER BY Categorieen_Omschrijving, Categorieen_autoID, Categorieen_Parent ASC
</CFQUERY>

 

[Bramvg]

ram123, the problem is that in my main page I run a query and when I output this query I get a list of, let's say 5 records.

Record 3 has an unique ID = Categorieen_autoID e.g. 15
Now, my custom tag needs to 'read' this ID (=15).

The problem is I can't determine this number, or get is.
Apparently the custom tag cannot determine/get/... this Categorieen_autoID when I try to 'catch' it from my custom tag.

Help more than welcome!
bram

 

[ram123]

Bramvg,

Try this Code.

If You get any syntax errors, try to figure out any missing tags and spee mistakes.

<CFQUERY NAME=&quot;List&quot; DATASOURCE=&quot;#DSN#&quot;>
SELECT Categorieen_autoID, Categorieen_Parent, Categorieen_Active, Categorieen_Omschrijving FROM T_Categorieen
WHERE Categorieen_Active = 1 and Categorieen_Parent = 0
ORDER BY Categorieen_Omschrijving, Categorieen_autoID, Categorieen_Parent ASC
</CFQUERY>

<!--- <CF_ctsubcategories> --->
<ul>
<!--- This loop displays all items with parent it 0, i.e. top level categories --->
<cfoutput query=&quot;List&quot;>
<li>#Categorieen_Omschrijving#<br>
<!--- Now call the custom tag and passthis parentid --->
<cfmodule template=&quot;ctsubcategories.cfm&quot; curent_autoID=&quot;#Categorieen_autoID#&quot;>
</cfoutput>
</ul>






Your Custom Tag should be like thisie. ctsubcategories.cfm)
***********************
<cfparam name=&quot;Attributes.datascource&quot; default=#caller.dsn#>
<cfparam name=&quot;Attributes.current_autoID&quot; default=&quot;0&quot;>
<!--- <cfoutput>
#request.Categorieen_autoID#
</cfoutput> --->


<CFQUERY NAME=&quot;Sub&quot; DATASOURCE=&quot;#Attributes.datascource#&quot;>
SELECT Categoriee, Categorieen_Parent,
Categorieen_Active, Categorieen_Omschrijving
FROM T_Categorieen
WHERE Categorieen_Active = 1
AND Categorieen_Parent IN( #current_autoID#)
ORDER BY Categorieen_Omschrijving, Categorieen_autoID,
Categorieen_Parent ASC
</CFQUERY>

<!--- IF the record count is then it does not have child items, then display details --->
<cfif sub.RecordCount eq 0>
<span class=&quot;smalltxt&quot;>
<A HREF=&quot;update_01.cfm?ID=#current_autoID#&quot;>Update</A> - <a href=&quot;details.delete_01.cfm?ID=#current_autoID#&quot;>Delete</A>
</span>

<cfelse>
<!--- loop thru child items for this parent items --->
<ul>
<cfoutput query=&quot;sub&quot;>
<LI>#sub.Categorieen_Omschrijving#
<!--- Call the custom tag with this auto id, so it can check sub childs --->
<cfmodule template=&quot;ctsubcategories.cfm&quot; curent_autoID=&quot;#sub.Categorieen_autoID#&quot;>
</cfoutput>
</ul>
</cfif>

If you still get problems let me know.

 

[Bramvg]

Hi Ram,

I tried it, and ideed, with some changes it works fine!
Thank you very much.

btw: I encountered one more problem: the DSN is stored in my application.cfm. The first time it does the loop (so when my custom tag template is called) the DSN is recognized (#caller.DSN#) but the second time it showes me an error! Unfortunately I had to hard code the DSN.

Once again, many thanks

Regards
Bram

 

[ram123]

Cool. You can try using #application.dsn# instead of #caller.dsn#