Solved

How to list ParentIDs by title?

Posted on 2012-03-20
7
301 Views
Last Modified: 2012-03-20
Hi. I have what seems like a very basic question, but I can't quite get the solution. I have parent and child PageIDs in a table. I need to list ParentIDs by title. I can't quite get the right list of titles to display.

Query getPages queries the table for records:

<cfquery name="getPages" datasource="#APPLICATION.dataSource#">
SELECT PageID, PageTitle, DateCreated, DateModified, ParentID, SortOrder
FROM #REQUEST.contentTable#
</cfquery>
 

Open in new window


Here is the output:
 
 
  <!--- display record information --->
 <cfoutput query="getPages">
 <table><tr>
  <td>#getPages.PageID#</td>
   <td>#getPages.PageTitle#</td>
   <td>#DateFormat(getPages.DateCreated, "mmmm d, yyyy")#</td>
   <td>#DateFormat(getPages.DateModified, "mmmm d, yyyy")#</td>
   <td>
   
   <!--- if ParentID is 0, then web page is in top-level navigation --->
      <cfif ParentID IS 0>
   Top Level Navigation
   
   <!--- if ParentID is 100000, then web page is omitted from navigation --->   
   <cfelseif ParentID IS 100000>
   Not in Navigation
   
   <!--- otherwise, display the Page Title of the record --->    
   <cfelse>
   #getPages.PageTitle#<!--- here is where I am stuck; this variable obviously displays just the PageTitle of the PageID, but I want to display the PageTitle of the PageID's ParentID ---> 
   </cfif>
   </td>
   <td>#getPages.SortOrder#</td>  
   </tr></table>
   </cfoutput>
   
   

Open in new window

 
It is easy to display the PageTitle of a PageID: #getPages.PageTitle#. But I want to display the PageTitle of the PageID's ParentID.

Example: PageID 2 is web page "About Us", and has ParentID 0 because it is in top level navigation.

PageID 3 is web page "Staff", and has ParentID 2, because it is a child of PageID 2.

I want to display PageTitle "About Us" as the parent page for PageID 3, "Staff".

Do I need to use another query to make this happen? (I think I do.)

Thanks for reading.

Eric
0
Comment
Question by:Eric Bourland
  • 3
  • 2
  • 2
7 Comments
 
LVL 3

Author Comment

by:Eric Bourland
ID: 37745016
I think I got this. I needed a query of a query:

 <cfquery name="getParents" dbtype="query">
SELECT PageID, PageTitle, ParentID
FROM getPages  
WHERE PageID = <cfqueryparam value="#val(getPages.ParentID)#" cfsqltype="cf_sql_integer">
</cfquery>

Output:

#getParents.PageTitle#

Doing some more tests, but I think it is working. I think I just needed to write out the problem.

Eric
0
 
LVL 52

Accepted Solution

by:
_agx_ earned 450 total points
ID: 37745022
Maybe this is too simplistic, but are you saying want to display both? ie  About Us - Staff  

Something like this would return both the child and parent title in the same query

SELECT c.PageID, c.PageTitle, c.DateCreated, c.DateModified, c.ParentID, c.SortOrder
            , p.PageTitle AS ParentPageTitle
FROM   #REQUEST.contentTable# c
                LEFT JOIN #REQUEST.contentTable# p ON p.PageID = c.ParentID
<!--- order by parent  then child title --->
ORDER BY p.PageTitle, c.PageTitle
0
 
LVL 7

Assisted Solution

by:micropc1
micropc1 earned 50 total points
ID: 37745024
So what about doing a join in your sql query (parentid to pageid) to get the parent's title as a separate column?
0
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 3

Author Comment

by:Eric Bourland
ID: 37745035
I think I posted my idea just a couple of minutes ahead of _agx_ and micropc1. My idea is working, but I understand I can make the solution more efficient, using a JOIN.

I'll go try this. =)

Eric
0
 
LVL 3

Author Closing Comment

by:Eric Bourland
ID: 37745070
The JOIN worked really well.

I think both _agx_ and micropc1 suggested the same solution -- obtain the parentID title as a separate column; that column becomes my new variable for Parent Title. _agx_, thank you for the actual code -- I understand it.

My next task is to sort the list of Parent and Child pages by Sort Order, with children arranged under their respective parents. I am going to work on this myself using the ideas I've gained here.

Thank you both very much. Have a great evening. And, happy spring. =)

Eric
0
 
LVL 7

Expert Comment

by:micropc1
ID: 37745116
Yeah - i think we were saying the same thing. i didn't see his solution, otherwise I wouldn't have posted.

For sorting the pages you might want to look into using CTE recursion.

Have a good one.
0
 
LVL 52

Expert Comment

by:_agx_
ID: 37745330
I think I posted my idea just a couple of minutes ahead of _agx_ and micropc1.


Whoops,  I didn't see your response either. Must have sat too long while responding.
0

Featured Post

Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

I spent nearly three days trying to figure out how incorporate OAuth in Coldfusion for the Eventful API. Hopefully, this article will allow Coldfusion Programmers to buzz through the API when they need to. Basically, what this script does is authori…
Sometimes databases have MILLIONS of records and we need a way to quickly query that table to return the results me need. Sure you could use CFQUERY but it takes too long when there are millions of records. That is why SOLR was invented. Please …
Here's a very brief overview of the methods PRTG Network Monitor (https://www.paessler.com/prtg) offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…
This demo shows you how to set up the containerized NetScaler CPX with NetScaler Management and Analytics System in a non-routable Mesos/Marathon environment for use with Micro-Services applications.

708 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now