I know I have been asking many questions lately. Thanks for your help and patience.
This is an interesting problem I have considered at length today. In my edit page, I have a select menu that displays parent, child, and grandchild pages; the user can select a parent for the page being edited.
I want to <i>exclude</i> grandchild pages from that select menu, because I want only a three-level menu; thus, grandchild pages cannot be parents of other pages.
I've settled on this idea: I added a column, isGrandchild, to the content table, with datatype "bit". When a page is inserted or updated as a <i>grandchild</i> page, this column is set to True for that record. So far, so good.
But (you saw this coming) how do I tell ColdFusion that a page is a grandchild page? What distinguishes a grandchild page from a child page? I need to add something to the insert and the update queries, but I don't know what that could be.
Parent: ParentID = 0
Child: ParentID = PageID of Parent page
Grandchild: ParentID = PageID of Child page (above)
As you see, I cannot (so far) distinguish a Grandchild from a Child using PageID or ParentID.
How can I tell ColdFusion: This is a grandchild; don't display it in the select menu?
This is an ongoing project and I keep expanding it and trying new ideas. It's been really interesting. Thank you for your advice.
<!--- this displays the Select menu --->
<!--- query GetParents: select Parent, Child pages from navigation table --->
<cfquery datasource="#application.datasource#" name="GetParents">
SELECT PageID, PageTitle, ParentID, SortOrder, isGrandchild
WHERE isGrandchild <> '1' <!--- exclude records if isGrandchild = 1 --->
ORDER BY ParentID asc, SortOrder asc
<!--- query getSelectedParents selects SELECTED Parent page from navigation table --->
<cfquery datasource="#application.datasource#" name="getSelectedParents">
SELECT PageTitle, ParentID
WHERE ParentID = <cfqueryparam cfsqltype="cf_sql_integer" value="#val(form.ParentID)#">
<cfset selectedParents = valueList(getSelectedParents.ParentID)>
<!--- use this select menu to display and assign parent page Titles --->
<!--- user should see a human-readable list of parent page titles --->
<cfselect size="8" name="ParentID" value="PageID" display="PageTitle" multiple="no" query="GetParents" queryPosition="below" selected="#selectedParents#" id="ParentID" style="width:250px;">
<!--- when form is processed, the correct ParentID is populated into navigation table --->
<!--- ParentID value 0 means the record / web page is included in top-level, main navigation menu --->
And the insert / update queries are pretty routine:
<cfquery name="InsertPage" datasource="#application.datasource#" result="newPage">
INSERT INTO #REQUEST.contentTable#
<cfqueryparam cfsqltype="cf_sql_varchar" value="#Trim(Left(form.PageTitle,255))#">,
<cfqueryparam cfsqltype="cf_sql_varchar" value="#Trim(form.PageContentLeft)#">,
<cfqueryparam cfsqltype="cf_sql_varchar" value="#Trim(form.PageContentRight)#">,
<cfqueryparam cfsqltype="cf_sql_integer" value="#form.parentID#" null="#form.parentID eq -1#">,
<cfqueryparam cfsqltype="cf_sql_integer" value="#val(form.SortOrder)#">,
<cfqueryparam cfsqltype="cf_sql_timestamp" value="#now()#">,
<cfqueryparam cfsqltype="cf_sql_varchar" value="#Trim(Left(form.safeURL,255))#">