Solved

How to list ParentIDs by title?

Posted on 2012-03-20
7
306 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
Space-Age Communications Transitions to DevOps

ViaSat, a global provider of satellite and wireless communications, securely connects businesses, governments, and organizations to the Internet. Learn how ViaSat’s Network Solutions Engineer, drove the transition from a traditional network support to a DevOps-centric model.

 
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: Postgres Monitoring System

A PHP and Perl based system to collect and display usage statistics from PostgreSQL databases.

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

PROBLEM:  How to open a cfwindow or run a function on double click of a cfgrid row. One of my clients wanted to be able to double click on a row item to get more detailed information about a transaction and to be able to modify the line items i…
Today, I was working on some optimization and spam-stopping techniques when I encountered Ben Nadel's post to reduce spam feature using Math (http://www.bennadel.com/blog/197-How-I-Stop-Spammers-On-My-ColdFusion-Blog.htm). While this method is not o…

735 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