[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Generate dynamic lists with subcategory headers

Posted on 2009-04-16
6
Medium Priority
?
394 Views
Last Modified: 2013-12-24
I am trying to create lists that have subcategory headers using CF with a MSSQL db.

So it would look like

SUBCATEGORY
   list item1
   list item2
   list item3
   list item4

SUBCATEGORY2
   list item1
   list item2
   list item3
   list item4

   
My DB Table and columns are:

Table: Links
Columns: URL, linkID, linkOrder, pageID, pubStatus, linkType, linkLabel, linkSubCatID, linkLabelAcronym

I have another table, LinkSubCategories that corresponds to the linkSubCatIDs. I want to use the names of the Subcategories as the headers to each list.  Does this make sense?
<CFQUERY NAME="qGetListLinks" DATASOURCE="RAA_external">
    SELECT      *
    FROM        Links
    WHERE 		PubStatus IS NOT NULL 
    AND         pageID = '#URL.ID#'
    Order by linkOrder ASC
    </CFQUERY>
<cfinclude template="../breadcrumbs.cfm">
<br />
<br />
<ul>
  <cfoutput query="qGetListLinks">
 
  
  <cfif qGetListLinks.linkType LT "1" >
      <li>There is no content for this page. If you feel this is a mistake, feel free to contact the <a href="contact_form.cfm">webmaster</a>.</li>
	  <cfelseif qGetListLinks.linkSubCatID EQ "5"  >
      <li><a href="#qGetListLinks.URL#" target="_blank">#qGetListLinks.linkLabelAcronym# - #qGetListLinks.linkLabel#</a></li>
    <cfelseif qGetListLinks.linkType EQ "4" >
      <li><a href="#qGetListLinks.URL#" target="_blank">#qGetListLinks.linkLabel#</a></li>
	  <cfelseif qGetListLinks.linkType EQ "3" >
      <li><a href="#qGetListLinks.URL#" target="_blank">#qGetListLinks.linkLabel#</a></li>
	  
      <cfelse>
      <li><a href="index.cfm?ID=#qGetListLinks.pageID#&Type=#qGetListLinks.linkType#" >#qGetListLinks.linkLabel#</a> </li>
    </cfif>
  </cfoutput>
</ul>

Open in new window

0
Comment
Question by:rrattie
  • 3
  • 3
6 Comments
 
LVL 16

Accepted Solution

by:
duncancumming earned 2000 total points
ID: 24160152
You need to amend your query to join to the LinkSubCategories  table.  Then use grouped output.



<CFQUERY NAME="qGetListLinks" DATASOURCE="RAA_external">
	SELECT      Links.*, LinkSubCategories.SubCategory
	FROM        Links
	INNER JOIN LinkSubCategories  
	WHERE               PubStatus IS NOT NULL 
	AND         pageID = '#URL.ID#'
	Order by SubCategory, linkOrder ASC
</CFQUERY>
 
 
<cfoutput query="qGetListLinks" group="SubCategory">
	#qGetListLinks.SubCategory#<br>
	<cfoutput>
		#qGetListLinks.linkLabel#<br>
	</cfoutput>
	<br>
</cfoutput>

Open in new window

0
 
LVL 16

Expert Comment

by:duncancumming
ID: 24160164
I missed out a part of the query.

INNER JOIN LinkSubCategories  
       ON Links.linkSubCatID =  LinkSubCategories.SubCatID
0
 
LVL 2

Author Comment

by:rrattie
ID: 24160316
AWESOME!! I'm 90% there now!

As I said in my original question/statement. I want it to be an unordered list.

I used the code you posted, but I put in a UL, LI..etc...

The only problem I have is that is shows as:

SUBCATEGORY
   list item1
   list item2
   list item3
   list item4

     SUBCATEGORY2
       list item1
       list item2
       list item3
       list item4


Instead of:

SUBCATEGORY
   list item1
   list item2
   list item3
   list item4

SUBCATEGORY2
   list item1
   list item2
   list item3
   list item4

Any ideas?

0
Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
LVL 16

Expert Comment

by:duncancumming
ID: 24160438
Sounds like a problem with an unclosed </li> or </ul>.  Here's a modification of my example to use ul/li


<cfoutput query="qGetListLinks" group="SubCategory">
	#qGetListLinks.SubCategory#
	<ul>
	<cfoutput>
		<li>#qGetListLinks.linkLabel#</li>
	</cfoutput>
	</ul>
</cfoutput>

Open in new window

0
 
LVL 2

Author Comment

by:rrattie
ID: 24160471
LOLOLOL!! Man.. I can't believe I missed that.  Oh well.  100% functioning as I wanted.

As usual, you are super fast and efficient. Less than an hour between me submitting the question and you solving it!
0
 
LVL 2

Author Closing Comment

by:rrattie
ID: 31571099
Fast and Accurate Solutions Always!
0

Featured Post

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This is an updated version of a post made on my blog over 3 years ago. It is unfortunately, still very relevant as we continue to see both SQLi (SQL injection) and XSS (cross site scripting) attacks hitting some of the most recognizable website and …
Lease-to-own eliminates the expenditure of hardware replacement and allows you to pay off the server over time. Usually, this is much cheaper than leasing servers. Think of lease-to-own as credit without interest.
Are you ready to place your question in front of subject-matter experts for more timely responses? With the release of Priority Question, Premium Members, Team Accounts and Qualified Experts can now identify the emergent level of their issue, signal…
Whether it be Exchange Server Crash Issues, Dirty Shutdown Errors or Failed to mount error, Stellar Phoenix Mailbox Exchange Recovery has always got your back. With the help of its easy to understand user interface and 3 simple steps recovery proced…
Suggested Courses

831 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question