Solved

How to list ParentIDs by title?

Posted on 2012-03-20
7
308 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Understanding Linux Permissions

Linux for beginners: How to view the permissions associated with files and directories and also how you can change them.

 
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

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

CFGRID Custom Functionality Series -  Part 1 Hi Guys, I was once asked how it is possible to to add a hyperlink in the cfgrid and open the window to show the data. Now this is quite simple, I have to use the EXT JS library for this and I achiev…
Hi. There are several upload tutorials using jquery and coldfusion. I found a very interesting one here Upload Your Files using Jquery & ColdFusion and Preview them (http://www.randhawaworld.com/) . I did keep the main js functions but made sever…
In this video, viewers are given an introduction to using the Windows 10 Snipping Tool, how to quickly locate it when it's needed and also how make it always available with a single click of a mouse button, by pinning it to the Desktop Task Bar. Int…
In this video we outline the Physical Segments view of NetCrunch network monitor. By following this brief how-to video, you will be able to learn how NetCrunch visualizes your network, how granular is the information collected, as well as where to f…

635 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