Solved

How to list ParentIDs by title?

Posted on 2012-03-20
7
307 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
Resolve Critical IT Incidents Fast

If your data, services or processes become compromised, your organization can suffer damage in just minutes and how fast you communicate during a major IT incident is everything. Learn how to immediately identify incidents & best practices to resolve them quickly and effectively.

 
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: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say 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

Suggested Solutions

Title # Comments Views Activity
multi page pdf 4 106
ColdFusion 10 Error 2 84
Error when uploading a document from an IPad 6 26
Attendance Tracking 3 24
Hi, I will be creating today a basic tutorial on how we can create a Mail Custom Function and use it where ever we want. The main advantage about creating a custom function is that we can accommodate a range of arguments to pass to the Function and …
Hi, Even though I have created this Tutorial on My personal Blog, Some people might not able to find my website, So here i am posting it again Today, from the topic it is very clear that i will be showing you here the very basic usage of how we …
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…
Finding and deleting duplicate (picture) files can be a time consuming task. My wife and I, our three kids and their families all share one dilemma: Managing our pictures. Between desktops, laptops, phones, tablets, and cameras; over the last decade…

732 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