Solved

How to list ParentIDs by title?

Posted on 2012-03-20
7
304 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
3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

 
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

Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Airline check in kiosk 4 106
cfpdfform and populate 14 70
On Submit return to same spot 7 92
ColdFusion - Import large XLS files 4 84
This article  is about submitting  form through  ColdFusion.Ajax.submitForm to the action page and send a response back in JSON format which later can be decoded using ColdFusion.JSON.decode. By this way you can avoid the usual page refresh for subm…
The technique is by far very Simple! How we can export the ColdFusion query results to DOC file?  Well before writing this I researched a lot in Internet but did not found a good Answer anyways!  So i thought now i should share my small snippet w…
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…
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…

778 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