Eric Bourland
asked on
How to list ParentIDs by title?
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:
Here is the output:
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
Query getPages queries the table for records:
<cfquery name="getPages" datasource="#APPLICATION.dataSource#">
SELECT PageID, PageTitle, DateCreated, DateModified, ParentID, SortOrder
FROM #REQUEST.contentTable#
</cfquery>
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>
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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
I'll go try this. =)
Eric
ASKER
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
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
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.
For sorting the pages you might want to look into using CTE recursion.
Have a good one.
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.
Whoops, I didn't see your response either. Must have sat too long while responding.
ASKER
<cfquery name="getParents" dbtype="query">
SELECT PageID, PageTitle, ParentID
FROM getPages
WHERE PageID = <cfqueryparam value="#val(getPages.Paren
</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