Solved

How to NOT display grandchild pages in a Select menu?

Posted on 2012-04-03
43
364 Views
Last Modified: 2012-04-12
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.

Eric

<!--- 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
        FROM    #REQUEST.contentTable#
        WHERE isGrandchild <> '1' <!--- exclude records if isGrandchild = 1 --->
        ORDER BY ParentID asc, SortOrder asc
</cfquery>
  
  
<!--- query getSelectedParents selects SELECTED Parent page from navigation table --->
  <cfquery datasource="#application.datasource#" name="getSelectedParents">
        SELECT  PageTitle, ParentID
        FROM    #REQUEST.contentTable#
        WHERE ParentID = <cfqueryparam cfsqltype="cf_sql_integer" value="#val(form.ParentID)#">
</cfquery>
  
  
  <cfset selectedParents = valueList(getSelectedParents.ParentID)>
  
      <p>Choose Parent:</p>
  
  <!--- 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;">
      
    	</cfselect>                
  <!--- 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 --->

  

Open in new window


And the insert / update queries are pretty routine:

			<cfquery name="InsertPage" datasource="#application.datasource#" result="newPage">
				 INSERT INTO #REQUEST.contentTable#
     					(
                        PageTitle,
                        PageContentLeft,
                        PageContentRight,
                        ParentID,
                        SortOrder,
                        DateCreated,
                        safeURL
                        )
			     VALUES(
	                <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))#">
				         )         
					</cfquery>

Open in new window

0
Comment
Question by:Eric Bourland
  • 21
  • 20
  • 2
43 Comments
 
LVL 39

Expert Comment

by:gdemaria
ID: 37804276
Hey Eric - a grandchild is an item that has a parent, who has a parent... you should be able to determine that using a query.

But I don't really fully understand your reasoning.   The grandchild IS the third level.  If you want three levels - it is parent, child and grandchild, so what is it you're leaving out?

Do you have a link to the page you're working on?   I think we should look at the big picture to best help you in whatever action you're trying to do.  

I do recall your structure and the over-all mission, just need to know what task you're working on..
0
 
LVL 3

Author Comment

by:Eric Bourland
ID: 37804318
>>>a grandchild is an item that has a parent, who has a parent... you should be able to determine that using a query.

I think this is the answer. But I am not sure how to implement.

>>The grandchild IS the third level.

Yes.

>>>If you want three levels - it is parent, child and grandchild, so what is it you're leaving out?
Well, I want to omit the grandchildren from the select menu. This is because I don't want grandchildren to be parents, or to have child pages themselves. =) If I constrain the menu to three levels, that means grandparents cannot have child pages -- correct?

I went ahead and quickly set up a sandbox page to show you. You can view it without login credentials. You (or anyone) can make any updates or edits you want.

http://test.ebwebwork.com/admin/managePages.cfm

That is the menu of pages, and a link to the edit page. It is very simple (and I am trying to keep it simple).

Can you see the grandchild pages in the Parent Select menu?

Eric
0
 
LVL 52

Expert Comment

by:_agx_
ID: 37804384
I think this is the answer. But I am not sure how to implement.

My $0.02, if you'll need this flag frequently or on many pages, I'd lean towards adding a column in the table. If it's just used on 1 or 2 pages, I'd probably go with a query as gd suggested.  

The basic structure is almost the same as the other query used to generate the menu.  Since you only want to know if something IS a grandchild use inner joins. If no results are returned, you know it's not already a grandchild page.

SELECT      gc.PageID
FROM    Table gc
                  INNER JOIN Table c ON c.PageID = gc.ParentID  <!--- this page has a parent --->
                  INNER JOIN Table p ON p.PageID = c.ParentID    <!--- this page has a grandparent --->
WHERE   gc.PageID = #val(ThePageYouWantToCheck)#
0
 
LVL 39

Expert Comment

by:gdemaria
ID: 37804404
Yeah, I might suggest using the main query you wrote for the menu page.... you could even make a database View of that query and use it in all these locations.   Adding a simple where clause to that view could give you just what you wanted...
0
 
LVL 52

Expert Comment

by:_agx_
ID: 37804490
A view would be simpler, but I don't know if you want to use the main menu query because then you'd have to change existing code and filter to filter on the right column: parentPageID, childPageID or grandchildPageid instead of just
             SELECT  Columns
             FROM   TableName
             WHERE pageId = #something#.  

To avoid changing existing code, a different structure might be better.
0
 
LVL 3

Author Comment

by:Eric Bourland
ID: 37804670
Good to hear from you both.

I did add a column to the table, column "isGrandchild" -- datatype "bit".  I understand I might not need it, after reading your notes, above, a few times. At this point I do not mind changing code or table structure; I changed both a lot today.

So, I think I understand, in the edit page "editPages.cfm", that I can replace this query:

<cfquery datasource="#application.datasource#" name="editPage">
SELECT PageID, PageTitle, PageContentLeft, PageContentRight, DateCreated, DateModified, ParentID, SortOrder, safeURL
FROM #REQUEST.contentTable#
WHERE PageID = <cfqueryparam cfsqltype="cf_sql_integer" value="#val(form.PageID)#">
</cfquery>

Open in new window


with the query I use to display all pages in the "manage" page, managePages.cfm:

<!--- get table columns for parent, child, grandchild records--->
<cfquery name="getPages" datasource="#APPLICATION.dataSource#">
SELECT
       p.PageID     AS PageIDLevel1
      ,p.PageTitle  AS PageTitleLevel1
      ,p.SortOrder  AS SortOrderLevel1
      ,p.ParentID   AS ParentIDLevel1
      ,c.PageID     AS PageIDLevel2
      ,c.PageTitle  AS PageTitleLevel2
      ,c.SortOrder  AS SortOrderLevel2
      ,c.parentID   AS ParentIDLevel2
      ,gc.PageID    AS PageIDLevel3
      ,gc.PageTitle AS PageTitleLevel3
      ,gc.SortOrder AS SortOrderLevel3
      ,gc.parentID  AS ParentIDLevel3
      ,p.DateCreated AS DateCreatedLevel1
      ,p.DateModified AS DateModifiedLevel1
      ,c.DateCreated AS DateCreatedLevel2
      ,c.DateModified AS DateModifiedLevel2
      ,gc.DateCreated AS DateCreatedLevel3
      ,gc.DateModified AS DateModifiedLevel3
     
FROM #REQUEST.contentTable# p
                  LEFT JOIN #REQUEST.contentTable#  c ON c.parentID = p.pageID
                  LEFT JOIN #REQUEST.contentTable#  gc ON gc.parentID = c.PageID
WHERE   ISNULL(p.parentID, 0) = 0
ORDER BY p.SortOrder, c.SortOrder, gc.SortOrder
</cfquery>

Open in new window


but add a clause after the WHERE clause:

AND PageID = <cfqueryparam cfsqltype="cf_sql_integer" value="#val(form.PageID)#">

I will try that, and also mess around with the SELECT. This is a really interesting task. I am learning a lot. For now though I think I will get some sleep. =) Good night to you both.

Eric
0
 
LVL 52

Expert Comment

by:_agx_
ID: 37807329
in the edit page "editPages.cfm", that I can replace this query:
with the query I use to display all pages in the "manage" page, managePages.cfm:

...
WHERE   ISNULL(p.parentID, 0) = 0


Not quite.  That WHERE clause only pulls the top level pages (and the children and grand children are returned in different columns)

                       Level1(parent)               Level2 (child)     Level3 (grandchild)
                       Home                             About                Contact
                       Parent X                         Child  X              NULL
                       Parent Y                         NULL                  NULL
   
Thinking about it, you could remove the WHERE filter and put it into a view.  Then use one where clause for the menu and another for the edit page.  Having a single view is easier ... but some people might find it confusing.   You'd have to remember that "Level1" means something different when you're generating the menu (ie from top down) than when you're doing a page edit.  

If you want to try that approach, you need to do 3 things:

1) Create a VIEW.  Your view would consist of this.  Don't include a where clause or order by:

       CREATE VIEW YourViewName
                 SELECT
                                p.PageID     AS PageIDLevel1
                               , p.PageTitle  AS PageTitleLevel1
                               , p.SortOrder  AS SortOrderLevel1
                              ....
                  FROM YourTableName p
                                   LEFT JOIN YourTableName   c ON c.parentID = p.pageID
                                   LEFT JOIN YourTableName   gc ON gc.parentID = c.PageID

Open in new window


2) To generate the menu, query the view. Just change the column names so they match the ones in your view, not the table

            SELECT   PageIDLevel1
                         , PageTitleLevel1
                         , .....
            FROM     YourViewName
            WHERE   ISNULL(ParentIDLevel1, 0) = 0
            ORDER BY SortOrderLevel1, SortOrderLevel2, SortOrderLevel2

3) On your your edit page keep the original query, but use the view instead of the table

         SELECT  PageIDLevel1, PageTitleLevel1,....PageIDLevel3 AS GrandParentID
         FROM   YourViewName
         WHERE  PageIDLevel1  = <cfqueryparam cfsqltype="cf_sql_integer" value="#val(form.PageID)#">

Then you can use the GrandParentID to determine if the page is level 3.  

            <cfif val(yourQuery.GrandParentID) gt 0>
                   this page is level 3. don't allow children
            <cfelse>
                   it's level 1 or 2. so allow children
           </cfif>
0
 
LVL 3

Author Comment

by:Eric Bourland
ID: 37808977
_agx_, thanks for this! Views are a new concept for me. I was reading up on them last night, and again today at the T-SQL link that you sent.

Here is what I have got so far:

<!--- create the view --->
<cfquery name="createView" datasource="#application.datasource#">
      CREATE VIEW ViewEditPage
      SELECT
       p.PageID     AS PageIDLevel1
      ,p.PageTitle  AS PageTitleLevel1
      ,p.SortOrder  AS SortOrderLevel1
      ,p.ParentID   AS ParentIDLevel1
      ,c.PageID     AS PageIDLevel2
      ,c.PageTitle  AS PageTitleLevel2
      ,c.SortOrder  AS SortOrderLevel2
      ,c.parentID   AS ParentIDLevel2
      ,gc.PageID    AS PageIDLevel3
      ,gc.PageTitle AS PageTitleLevel3
      ,gc.SortOrder AS SortOrderLevel3
      ,gc.parentID  AS ParentIDLevel3
      ,p.DateCreated AS DateCreatedLevel1
      ,p.DateModified AS DateModifiedLevel1
      ,c.DateCreated AS DateCreatedLevel2
      ,c.DateModified AS DateModifiedLevel2
      ,gc.DateCreated AS DateCreatedLevel3
      ,gc.DateModified AS DateModifiedLevel3
     
FROM #REQUEST.contentTable# p
LEFT JOIN YourTableName   c ON c.parentID = p.pageID
LEFT JOIN YourTableName   gc ON gc.parentID = c.PageID
</cfquery>


<!--- query the view, to generate navigation menu on the index.cfm page --->
<cfquery name="queryView" datasource="#application.datasource#">
SELECT PageIDLevel1
      ,PageTitleLevel1
      ,SortOrderLevel1
      ,ParentIDLevel1
      ,PageIDLevel2
      ,PageTitleLevel2
      ,SortOrderLevel2
      ,ParentIDLevel2
      ,PageIDLevel3
      ,PageTitleLevel3
      ,SortOrderLevel3
      ,ParentIDLevel3
      ,DateCreatedLevel1
      ,DateModifiedLevel1
      ,DateCreatedLevel2
      ,DateModifiedLevel2
      ,DateCreatedLevel3
      ,DateModifiedLevel3
                   
            FROM     ViewEditPage
            WHERE   ISNULL(ParentIDLevel1, 0) = 0
            ORDER BY SortOrderLevel1, SortOrderLevel2, SortOrderLevel2
</cfquery>

<!--- query editPage tells ColdFusion which page to edit--->
<cfquery datasource="#application.datasource#" name="editPage">
SELECT p.PageID     AS PageIDLevel1
      ,p.PageTitle  AS PageTitleLevel1
      ,p.SortOrder  AS SortOrderLevel1
      ,p.ParentID   AS ParentIDLevel1
      ,c.PageID     AS PageIDLevel2
      ,c.PageTitle  AS PageTitleLevel2
      ,c.SortOrder  AS SortOrderLevel2
      ,c.parentID   AS ParentIDLevel2
      ,gc.PageID    AS PageIDLevel3
      ,gc.PageTitle AS PageTitleLevel3
      ,gc.SortOrder AS SortOrderLevel3
      ,gc.parentID  AS ParentIDLevel3
      ,p.DateCreated AS DateCreatedLevel1
      ,p.DateModified AS DateModifiedLevel1
      ,c.DateCreated AS DateCreatedLevel2
      ,c.DateModified AS DateModifiedLevel2
      ,gc.DateCreated AS DateCreatedLevel3
      ,gc.DateModified AS DateModifiedLevel3
FROM ViewEditPage
WHERE PageID = <cfqueryparam cfsqltype="cf_sql_integer" value="#val(form.PageID)#">
</cfquery>

Open in new window


So far, so good. The first three steps make sense to me. I think I set them up correctly.

I am still not sure how to filter grandchild pages from the select menu. Here is what I have:

<p>Choose parent page from the Select menu:</p>
    
<!--- query GetParents selects Parent pages from the content table --->
<!--- but excludes grandchild pages (ParentIDLevel3) --->
  <cfquery datasource="#application.datasource#" name="GetParents">
        SELECT  PageID, PageTitle, ParentID, SortOrder
        FROM    #REQUEST.contentTable#
		WHERE ParentID <> <cfqueryparam cfsqltype="cf_sql_integer" value="val(#editPage.ParentIDLevel3#)">
        ORDER BY ParentID asc, SortOrder asc
</cfquery>
  
<!--- query getSelectedParents selects SELECTED Parent page from content table --->
  <cfquery datasource="#application.datasource#" name="getSelectedParents">
        SELECT  PageTitle, ParentID
        FROM    #REQUEST.contentTable#
        WHERE ParentID = <cfqueryparam cfsqltype="cf_sql_integer" value="#val(form.ParentID)#">
</cfquery>

<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;">
      
    	</cfselect>                

Open in new window


But when I process that page: http://test.ebwebwork.com/admin/editPagesview.cfm?PageID=110

I get a syntax error:


Error Executing Database Query.
[Macromedia][SQLServer JDBC Driver][SQLServer]Incorrect syntax near the keyword 'SELECT'.
 
The error occurred in C:/websites/test.ebwebwork.com/admin/editPagesView.cfm: line 54
Called from C:/websites/test.ebwebwork.com/admin/editPagesView.cfm: line 1
Called from C:/websites/test.ebwebwork.com/admin/editPagesView.cfm: line 54
Called from C:/websites/test.ebwebwork.com/admin/editPagesView.cfm: line 1
52 :       ,gc.DateModified AS DateModifiedLevel3
53 :      
54 : FROM #REQUEST.contentTable# p
55 : LEFT JOIN YourTableName   c ON c.parentID = p.pageID
56 : LEFT JOIN YourTableName   gc ON gc.parentID = c.PageID

I am reviewing your instructions and code, and going over mine.

Thank you for this idea. I look forward to making it work.

Hope your day was good. Mine has been busy.... =)

Eric
0
 
LVL 52

Expert Comment

by:_agx_
ID: 37809300
Let's take it one step at a time.

FROM #REQUEST.contentTable# p
                 LEFT JOIN YourTableName   c ON c.parentID = p.pageID
                 LEFT JOIN YourTableName   gc ON gc.parentID = c.PageID


In the view definition, you need to use the variable in all 3 JOINS. Otherwise you'll get an error because you don't have a table named "YourTableName". Also, it looks like you dropped the keyword "as" before the SELECT. One you fix those things, do a SELECT * FROM ViewEditPage to verify it works.

      // If it already exists, use ALTER VIEW
      CREATE VIEW ViewEditPage
      AS
      SELECT
                   p.PageID     AS PageIDLevel1
                  ,p.PageTitle  AS PageTitleLevel1
                  .....
      FROM #REQUEST.contentTable# p
                 LEFT JOIN #REQUEST.contentTable# c ON c.parentID = p.pageID
                 LEFT JOIN #REQUEST.contentTable# gc ON gc.parentID = c.PageID

The 2nd query for the main menu looks good.

The 3rd query's off. Remember you're selecting from the "view" now, and it's column names are different.  You need to use the aliases ie "PageIDLevel1" not the underlying column name ie "PageID"

        SELECT    PageIDLevel1, PageTitleLevel1, .....
        FROM     ViewEditPage
        WHERE   PageIDLevel1 = ....
0
 
LVL 3

Author Comment

by:Eric Bourland
ID: 37809426
Working on this!
0
 
LVL 52

Expert Comment

by:_agx_
ID: 37809444
Ok good. Btw, I meant to mention views are just like tables in that you only create them once. After that, you don't need to touch them unless you need to make a change. So after you successfully create the view, you can remove that cfquery. You won't need it again.
0
 
LVL 3

Author Comment

by:Eric Bourland
ID: 37809451
>>So after you successfully create the view, you can remove that cfquery. You won't need it again.

Huh.

That is ... pretty cool.
0
 
LVL 52

Expert Comment

by:_agx_
ID: 37809467
Yep. The sql's stored in the db, like with a stored procedure. Behold the wonder of virtual tables ;-) Write it once, reuse it many times.
0
 
LVL 3

Author Comment

by:Eric Bourland
ID: 37814548
>>>Otherwise you'll get an error because you don't have a table named "YourTableName".

That's what I get for doing things in a hurry.

I edited the "create view" query to this:

<!--- create the view --->
<cfquery name="createView" datasource="#application.datasource#">
      CREATE VIEW ViewEditPage
      AS
      SELECT
       p.PageID     AS PageIDLevel1
      ,p.PageTitle  AS PageTitleLevel1
      ,p.SortOrder  AS SortOrderLevel1
      ,p.ParentID   AS ParentIDLevel1
      ,c.PageID     AS PageIDLevel2
      ,c.PageTitle  AS PageTitleLevel2
      ,c.SortOrder  AS SortOrderLevel2
      ,c.parentID   AS ParentIDLevel2
      ,gc.PageID    AS PageIDLevel3
      ,gc.PageTitle AS PageTitleLevel3
      ,gc.SortOrder AS SortOrderLevel3
      ,gc.parentID  AS ParentIDLevel3
      ,p.DateCreated AS DateCreatedLevel1
      ,p.DateModified AS DateModifiedLevel1
      ,c.DateCreated AS DateCreatedLevel2
      ,c.DateModified AS DateModifiedLevel2
      ,gc.DateCreated AS DateCreatedLevel3
      ,gc.DateModified AS DateModifiedLevel3
     
FROM #REQUEST.contentTable# p
LEFT JOIN #REQUEST.contentTable#   c ON c.parentID = p.pageID
LEFT JOIN #REQUEST.contentTable#   gc ON gc.parentID = c.PageID
</cfquery>

Open in new window


and changed the edit query to this:

<!--- query editPage tells ColdFusion which page to edit--->
<cfquery datasource="#application.datasource#" name="editPage">
SELECT p.PageID     AS PageIDLevel1
      ,p.PageTitle  AS PageTitleLevel1
      ,p.SortOrder  AS SortOrderLevel1
      ,p.ParentID   AS ParentIDLevel1
      ,c.PageID     AS PageIDLevel2
      ,c.PageTitle  AS PageTitleLevel2
      ,c.SortOrder  AS SortOrderLevel2
      ,c.parentID   AS ParentIDLevel2
      ,gc.PageID    AS PageIDLevel3
      ,gc.PageTitle AS PageTitleLevel3
      ,gc.SortOrder AS SortOrderLevel3
      ,gc.parentID  AS ParentIDLevel3
      ,p.DateCreated AS DateCreatedLevel1
      ,p.DateModified AS DateModifiedLevel1
      ,c.DateCreated AS DateCreatedLevel2
      ,c.DateModified AS DateModifiedLevel2
      ,gc.DateCreated AS DateCreatedLevel3
      ,gc.DateModified AS DateModifiedLevel3
FROM ViewEditPage
WHERE PageIDLevel1 = <cfqueryparam cfsqltype="cf_sql_integer" value="#val(form.PageIDLevel1)#">
</cfquery>

Open in new window


I think that is what you had in mind.

I am getting this error now:

Error Executing Database Query.
[Macromedia][SQLServer JDBC Driver][SQLServer]There is already an object named 'ViewEditPage' in the database.
 
The error occurred in C:/websites/test.ebwebwork.com/admin/editPages.cfm: line 57
Called from C:/websites/test.ebwebwork.com/admin/editPages.cfm: line 1
Called from C:/websites/test.ebwebwork.com/admin/editPages.cfm: line 57
Called from C:/websites/test.ebwebwork.com/admin/editPages.cfm: line 1
55 : FROM #REQUEST.contentTable# p
56 : LEFT JOIN #REQUEST.contentTable#   c ON c.parentID = p.pageID
57 : LEFT JOIN #REQUEST.contentTable#   gc ON gc.parentID = c.PageID
58 : </cfquery>

I think that means I can delete the query that creates the view. Am I correct?

Hope your day is going well.

Eric
0
 
LVL 52

Expert Comment

by:_agx_
ID: 37814582
Hey Eric, I'm good and hope you are too.

I think we're trying to do too much at once.  Since the VIEW is a one time operation.  Work on getting it up and running on first.  Do not move on to the next query until you can run successfully run a SELECT * on your view.

There is already an object named 'ViewEditPage' in the database.

Ok, so we know the view exists. But we don't know if it's using the good tables names. Run this query and dump it:

           SELECT * FROM ViewEditPage.  

If the view's using the wrong table names it won't work. You'll need to modify it, using ALTER, instead of create. (Like a table you can only "create" views once. After that you have to alter or drop it)

ie   ALTER VIEW ViewEditPage
      AS
      SELECT  ... etc...
0
 
LVL 3

Author Comment

by:Eric Bourland
ID: 37814607
Got it.

I dumped the results but am not sure if the column names are correct:

http://test.ebwebwork.com/admin/editPages.cfm?PageID=106

I think i want to alter the View anyway; in effect, starting over to make sure I have the right view.

I'll do this in a little while. I need to run out to a meeting. Back later this evening. thanks!

Eric
0
 
LVL 52

Expert Comment

by:_agx_
ID: 37814739
The dump looks right. But go ahead and alter it again. It won't break anything.

and changed the edit query to this:

Not quite.  Notice when you dump the view on your test page:

     http://test.ebwebwork.com/admin/editPages.cfm?PageID=106

..  it doesn't contain any of underlying column names like "PageID" or "PageTitle". Only the aliases  "PageIDLevel1", "PageTitleLevel1", etc... ?  When you defined the view it was like creating a new table with totally different column names:

                   PageIDLevel1                      (instead of "PageID")
                   PageTitleLevel1                   (instead of "PageTitle"...)
                   SortOrderLevel1
                   etc...

When you select from the view, you need to use the new column names, not the old ones.

        SELECT    PageIDLevel1, PageTitleLevel1, .....
        FROM     ViewEditPage
        WHERE   PageIDLevel1 = ....
0
 
LVL 3

Author Comment

by:Eric Bourland
ID: 37814954
Got it! This is making a lot more sense now.

One hiccup: It looks like I am not permitted to ALTER the View. (I use a shared MS SQL Server database. I might change that later, and move everything to SQL Server Express 2012 on my VPS at Viviotech. But, that is a task for later.)

But I can login using Studio Manager and delete the view, so I did. I can delete and create the view as often as I need, until I get it exactly right.

I think I have the create View query correct. The view queries make sense. And I believe I have set up the edit query correctly:

<!--- query editPage tells ColdFusion which page to edit--->
<cfquery datasource="#application.datasource#" name="editPage">
SELECT PageIDLevel1
      ,PageTitleLevel1
      ,SortOrderLevel1
      ,ParentIDLevel1
      ,PageIDLevel2
      ,PageTitleLevel2
      ,SortOrderLevel2
      ,ParentIDLevel2
      ,PageIDLevel3
      ,PageTitleLevel3
      ,SortOrderLevel3
      ,ParentIDLevel3
      ,DateCreatedLevel1
      ,DateModifiedLevel1
      ,DateCreatedLevel2
      ,DateModifiedLevel2
      ,DateCreatedLevel3
      ,DateModifiedLevel3
FROM ViewEditPage
WHERE PageIDLevel1 = <cfqueryparam cfsqltype="cf_sql_integer" value="#val(form.PageID)#">
</cfquery>

Now I have to include the other columns in this view: SafeURL, PageContentLeft, PageContentRight. I think this also means I need to change the CFPARAM default variables and the XSS protection at the top of the edit page:

<!--- set default value for PageID in scope URL --->
<cfparam name="url.PageID" default="">

<!--- define PageID in scope FORM, then set form.PageID equal to the PageID passed in the URL --->
<cfparam name="form.PageID" default="#url.PageID#">
<cfparam name="form.PageTitle" default="">
<cfparam name="form.PageContentLeft" default="">
<cfparam name="form.PageContentRight" default="">
<cfparam name="DateCreated" default="">
<cfparam name="DateModified" default="">
<cfparam name="form.SortOrder" default="0">

<!--- in user-editable fields, set up protection against XSS  --->
<cfset form.PageTitle = ReReplaceNoCase (form.PageTitle, "<script.*?>.*?</script>", "", "all")>
<cfset form.PageContentLeft = ReReplaceNoCase (form.PageContentLeft, "<script.*?>.*?</script>", "", "all")>
<cfset form.PageContentRight = ReReplaceNoCase (form.PageContentRight, "<script.*?>.*?</script>", "", "all")>
<cfset form.SortOrder = ReReplaceNoCase (form.SortOrder, "<script.*?>.*?</script>", "", "all")>

Open in new window


I have to change all of those columns to the new columns defined in the view, correct?

For example, <cfparam name="form.PageID" default="#url.PageID#"> becomes
<cfparam name="form.PageIDLevel1" default="#url.PageID#">

and <cfset form.PageTitle = ReReplaceNoCase (form.PageTitle, "<script.*?>.*?</script>", "", "all")> becomes:

<cfset form.PageTitleLevel1 = ReReplaceNoCase (form.PageTitleLevel1, "<script.*?>.*?</script>", "", "all")>

Because the old columns will no longer be used.
0
 
LVL 52

Expert Comment

by:_agx_
ID: 37816421
I don't know what's happening.  I submitted a response to this yesterday .. twice ... and neither is showing up.
0
 
LVL 52

Expert Comment

by:_agx_
ID: 37816446
(ok, once more with feeling ...)

believe I have set up the edit query correctly:

Yeah, that looks right.

I think this also means I need to change the CFPARAM default variables and the XSS protection at the top of the edit page:

Well you don't have to change the field names. Nothing says they have to match the query columns. It is just more intuitive most of the time. But you could call them "cat" and "dog" if you wanted ;-)

As for the XSS stuff, with so many fields you might want to do it dynamically. Just loop through the FORM collection.  You could exclude specific fields if needed.

        <cfloop collection="#FORM#" item="field">
             <cfset FORM[ field ]  = ReReplaceNoCase FORM[ field ], "<script.*?>.*?</script>", "", "all")>
        </cfloop>

Not to switch gears midstream, but keep in mind you could always create separate views (with more context sensitive column names),  or even go back to separate cfquery's, if that makes it easier.  It's duplication, but keeping it easy for you to understand/maintain is more important.
0
 
LVL 3

Author Comment

by:Eric Bourland
ID: 37818053
_agx_,

First of all -- ugh -- that you had to repeat your message here at Experts Exchange. Sometimes EE eats my posts too.

That is good advice about the XSS stuff and I will work on that.

>>>keep in mind you could always create separate views (with more context sensitive column names),  or even go back to separate cfquery's, if that makes it easier.  It's duplication, but keeping it easy for you to understand/maintain is more important.

I was considering this. I guess I do feel a little wary about creating a View and storing them in a database. What if something happens to the View? A View seems somehow less substantial than a real table. Like a View is something transitory and ephemeral. I feel a little unconfident about basing a web site on a view. Maybe this is only superstition and ignorance on my part. =)

So, is there a way I can achieve my goal -- just to exclude the grandchildren from the SELECT menu -- using only queries and tables, like I am used to doing?

Or is that ridiculous, and is it perfectly safe and normal to use a view to query a database for a web site's data?

Thank you as always for any advice.

I am currently on a bus to Michigan to spend a few days focusing on a writing project. (The bus is one of those new-fangled buses that have wi-fi.) I will also be able to work on this task -- but there is no hurry. I hope you have a relaxing weekend.

Eric
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 52

Expert Comment

by:_agx_
ID: 37818238
Hi Eric.

(The bus is one of those new-fangled buses that have wi-fi.)

That's totally cool. I didn't even know they had those. Nice to remain connected while you're traveling. Hope you have a safe trip.

Or is that ridiculous, and is it perfectly safe and normal to use a view to query a database for a web site's data?

No, I think it's a normal reaction at first. At least until you get comfortable with them.  I felt the same way, but now I couldn't live without them. I've used them since back in the days when I was once forced to use {gasp} MS Access ;-) I love them because they save me from having to copy the same complex sql over and over, hoping I don't drop or change something accidentally. Which is easy to do (and unfortunately, I have done it). Then end up wasting an hour trying to figure out why the results are different on page X than page Y and Z.

What if something happens to the View? A View seems somehow less substantial than a real table. Like a View is something transitory and ephemeral.


Lol, that is a great way to express it. Honestly I think it just the "newness" factor.  If you think about it, everything in a db is intangible: tables included.  So there's no greater risk of using a view than a table.  You can still view it's properties in using the management studio GUI (or system metadata)  just like a regular table.

I'm not saying any of this to try and force you to use VIEWs, just to reassure you that they're a powerful tool and it's quite common to use them in applications.

So, is there a way I can achieve my goal -- just to exclude the grandchildren from the SELECT menu -- using only queries and tables, like I am used to doing?

Sure. But refresh my memory. What does the query for the SELECT menu look like now?
0
 
LVL 3

Author Comment

by:Eric Bourland
ID: 37824776
Hi, _agx_,

I hope your holiday weekend was great. I took a break and hung out with friends and dyed easter eggs. =)

I've been slowing working through this Edit Page application. I feel like I am starting from scratch. I will recapitulate my work so far:

<!--- create a view of the columns we will work with --->
<cfquery name="createView" datasource="#application.datasource#">
      CREATE VIEW ViewEditPage
      AS
      SELECT
       p.PageID     AS PageIDLevel1
      ,p.PageTitle  AS PageTitleLevel1
      ,p.safeURL AS safeURLLevel1
      ,p.PageContentLeft AS PageContentLeftLevel1
      ,p.PageContentRight AS PageContentRightLevel1
      ,p.SortOrder  AS SortOrderLevel1
      ,p.ParentID   AS ParentIDLevel1
      ,p.DateCreated AS DateCreatedLevel1
      ,p.DateModified AS DateModifiedLevel1
      ,c.PageID     AS PageIDLevel2
      ,c.PageTitle  AS PageTitleLevel2
      ,c.safeURL AS safeURLLevel2
      ,c.PageContentLeft AS PageContentLeftLevel2
      ,c.PageContentRight AS PageContentRightLevel2
      ,c.SortOrder  AS SortOrderLevel2
      ,c.parentID   AS ParentIDLevel2
      ,c.DateCreated AS DateCreatedLevel2
      ,c.DateModified AS DateModifiedLevel2
      ,gc.PageID    AS PageIDLevel3
      ,gc.PageTitle AS PageTitleLevel3
      ,gc.safeURL AS safeURLLevel3
      ,gc.PageContentLeft AS PageContentLeftLevel3
      ,gc.PageContentRight AS PageContentRightLevel3
      ,gc.SortOrder AS SortOrderLevel3
      ,gc.parentID  AS ParentIDLevel3
      ,gc.DateCreated AS DateCreatedLevel3
      ,gc.DateModified AS DateModifiedLevel3
     
FROM #REQUEST.contentTable# p
LEFT JOIN #REQUEST.contentTable#   c ON c.parentID = p.pageID
LEFT JOIN #REQUEST.contentTable#   gc ON gc.parentID = c.PageID
</cfquery>


<!--- query the view, to generate navigation menu on the index.cfm page --->
<!--- we need only PageID, PageTitle, SortOrder, and ParentID for each level --->
<cfquery name="queryView" datasource="#application.datasource#">
SELECT PageIDLevel1
      ,PageTitleLevel1
      ,SortOrderLevel1
      ,ParentIDLevel1
      ,PageIDLevel2
      ,PageTitleLevel2
      ,SortOrderLevel2
      ,ParentIDLevel2
      ,PageIDLevel3
      ,PageTitleLevel3
      ,SortOrderLevel3
      ,ParentIDLevel3
                   
            FROM     ViewEditPage
            WHERE   ISNULL(ParentIDLevel1, 0) = 0
            ORDER BY SortOrderLevel1, SortOrderLevel2, SortOrderLevel3
</cfquery>

<!--- query editPage tells ColdFusion which page to edit--->
<cfquery datasource="#application.datasource#" name="editPage">
SELECT PageIDLevel1
      ,PageTitleLevel1
      ,safeURLLevel1
      ,PageContentLeftLevel1
      ,PageContentRightLevel1
      ,SortOrderLevel1
      ,ParentIDLevel1
      ,DateCreatedLevel1
      ,DateModifiedLevel1
      ,PageIDLevel2
      ,PageTitleLevel2
      ,safeURLLevel2
      ,PageContentLeftLevel2
      ,PageContentRightLevel2
      ,SortOrderLevel2
      ,ParentIDLevel2
      ,DateCreatedLevel2
      ,DateModifiedLevel2
      ,PageIDLevel3
      ,PageTitleLevel3
      ,safeURLLevel3
      ,PageContentLeftLevel3
      ,PageContentRightLevel3
      ,SortOrderLevel3
      ,ParentIDLevel3
      ,DateCreatedLevel3
      ,DateModifiedLevel3
FROM ViewEditPage
WHERE PageIDLevel1 = <cfqueryparam cfsqltype="cf_sql_integer" value="#val(form.PageID)#">
</cfquery>

Open in new window


I think all of that is correct. I am confused about a few things:

In query editPage, is this WHERE clause correct?

WHERE PageIDLevel1 = <cfqueryparam cfsqltype="cf_sql_integer" value="#val(form.PageID)#">

should it not be

WHERE PageID = <cfqueryparam cfsqltype="cf_sql_integer" value="#val(form.PageID)#">

(because I will be editing pages from levels 1, 2, and 3)?

And, I am not sure just how the looping of the XSS protection will work:

        <cfloop collection="#FORM#" item="field">
             <cfset FORM[ field ]  = ReReplaceNoCase FORM[ field ], "<script.*?>.*?</script>", "", "all")>
        </cfloop>

What values do I use for FORM and field? Sorry to be dense about that.

Thank you again. I know this question is getting loooong. I feel like I am rebuilding my Edit Page from scratch, and everything I have learned has gone out the window.

Eric
0
 
LVL 52

Expert Comment

by:_agx_
ID: 37824925
Sounds like you had a fun and relaxing weekend :)

should it not be
...
FROM ViewEditPage
WHERE PageID = <cfqueryparam cfsqltype="cf_sql_integer" value="#val(form.PageID)#">


No,  because remember the view doesn't contain a column named "PageID". Try it and see. You'll get an error.

While I think a VIEW is a good approach, that's just my opinion.  Honestly it seems like the concept is confusing to you. Like I said earlier: "some people might find [a view] confusing.  Because you'd have to remember that 'Level1' means something different when you're generating the menu (ie from top down) than when you're doing a page edit."  While using a view has benefits, they don't amount to much if it means you no longer understand the code ;-) It might be easier for you to stick with the old approach ie without the VIEW


What values do I use for FORM and field? Sorry to be dense about that.
   

FORM and "field" ;-)  You don't have to change anything.  Try this example on a test page and see if it makes things more clear
         <!---- simulate a form post --->
         <cfset FORM.firstname = "john">
         <cfset FORM.lastName = "smith">
         <cfset FORM.address = "123<script>alert('test');</script>">

         <cfloop collection="#FORM#" item="field">
             <cfoutput>Debug: Cleaning form field form.#field#<br>
             <cfset FORM[ field ]  = ReReplaceNoCase FORM[ field ], "<script.*?>.*?</script>", "", "all")>
             <cfoutput>Debug: New value of field form.#field# is:  #form[ Field ]#<br>
        </cfloop>
0
 
LVL 3

Author Comment

by:Eric Bourland
ID: 37825121
_agx_,

I think if I can see it working, I will understand it. So, you are saying that, in my Edit query, this WHERE clause:

WHERE PageIDLevel1 = <cfqueryparam cfsqltype="cf_sql_integer" value="#val(form.PageID)#">


is all I need? And if I need to edit pages from levels 2 or 3, it will work?

I think I need to see that in action to reify it in my brain. I will try it.

>>>'Level1' means something different when you're generating the menu (ie from top down) than when you're doing a page edit.

I admit I do not understand that on an intuitive level right now. Which makes me really want to understand it, rather than give up. =) I need to see this working; then I will understand it.

I noticed in this code:

         <!---- simulate a form post --->
         <cfset FORM.firstname = "john">
         <cfset FORM.lastName = "smith">
         <cfset FORM.address = "123<script>alert('test');</script>">

         <cfloop collection="#FORM#" item="field">
             <cfoutput>Debug: Cleaning form field form.#field#<br>
             <cfset FORM[ field ]  = ReReplaceNoCase FORM[ field ], "<script.*?>.*?</script>", "", "all")>
             <cfoutput>Debug: New value of field form.#field# is:  #form[ Field ]#<br>
        </cfloop>

That the cfoutputs are not closed. I went ahead and closed them, thus:

       <!---- simulate a form post --->
         <cfset FORM.firstname = "john">
         <cfset FORM.lastName = "smith">
         <cfset FORM.address = "123<script>alert('test');</script>">

         <cfloop collection="#FORM#" item="field">
             <cfoutput>Debug: Cleaning form field form. #field#<br /></cfoutput>
             <cfset FORM[ field ]  = ReReplaceNoCase FORM[ field ], "<script.*?>.*?</script>", "", "all")>
             <cfoutput>Debug: New value of field form.#field# is:  #form[ Field ]#<br /></cfoutput>
        </cfloop>

and set up a test page here:

http://test.ebwebwork.com/xssTest.cfm

and I get an invalid CFML construct error.

I really very much want to understand all of this. I am going back through this long question and reading again everything you have written.

Eric
0
 
LVL 52

Expert Comment

by:_agx_
ID: 37825162
So, you are saying that, in my Edit query, this WHERE clause: ... is all I need? And if I need to edit pages from levels 2 or 3, it will work?

Exactly, because unlike the original menu query (which only contained level1), the VIEW actually contains all pages of all levels.

I think I need to see that in action to reify it in my brain.

I'm like that too. I just want to make sure I'm not pushing you to use something that's way outside your comfort zone. I'm a big believer in understanding whatever tool/code you use.

and I get an invalid CFML construct error.

Ugh.. I typed it out really quickly. Sorry for the typos.  I can't test it, but it looks like it's just missing an open parenthesis after the function name
            ie ....
             <cfset FORM[ field ]  = ReReplaceNoCase( FORM[ field ], "<script.*?>.*?</script>", "", "all")>
0
 
LVL 3

Author Comment

by:Eric Bourland
ID: 37825300
The XSS makes perfect sense now: http://test.ebwebwork.com/xssTest.cfm

I see what's going on. =)

>>>I'm not pushing you to use something that's way outside your comfort zone. I'm a big believer in understanding whatever tool you use

I believe in stepping outside my comfort zone often and with mad glee. I also believe in paying attention and being studious. So, if you have patience with my slowness, I will proceed. =)

<!--- in user-editable fields, set up protection against XSS  --->
    <cfloop collection="#FORM#" item="field">
             <cfset FORM[ field ] = ReReplaceNoCase (FORM[ field ], "<script.*?>.*?</script>", "", "all")>
    </cfloop>

Open in new window


This gibes with the syntax that I read about here:
http://help.adobe.com/en_US/ColdFusion/9.0/CFMLRef/WSc3ff6d0ea77859461172e0811cbec22c24-7e98.html

and makes sense to me.

I think you are saying that my edit query is A-OK:

<!--- query editPage tells ColdFusion which page to edit--->
<cfquery datasource="#application.datasource#" name="editPage">
SELECT PageIDLevel1
      ,PageTitleLevel1
      ,safeURLLevel1
      ,PageContentLeftLevel1
      ,PageContentRightLevel1
      ,SortOrderLevel1
      ,ParentIDLevel1
      ,DateCreatedLevel1
      ,DateModifiedLevel1
      ,PageIDLevel2
      ,PageTitleLevel2
      ,safeURLLevel2
      ,PageContentLeftLevel2
      ,PageContentRightLevel2
      ,SortOrderLevel2
      ,ParentIDLevel2
      ,DateCreatedLevel2
      ,DateModifiedLevel2
      ,PageIDLevel3
      ,PageTitleLevel3
      ,safeURLLevel3
      ,PageContentLeftLevel3
      ,PageContentRightLevel3
      ,SortOrderLevel3
      ,ParentIDLevel3
      ,DateCreatedLevel3
      ,DateModifiedLevel3
FROM ViewEditPage
WHERE PageIDLevel1 = <cfqueryparam cfsqltype="cf_sql_integer" value="#val(form.PageID)#">
</cfquery>

Open in new window


I will try it out. More in a little while. Thank you again. =)

Eric
0
 
LVL 52

Expert Comment

by:_agx_
ID: 37825433
I think you are saying that my edit query is A-OK:

Yep, looks good to me.



I believe in stepping outside my comfort zone often and with mad glee. I also believe in paying attention and being studious. So, if you have patience with my slowness, I will proceed. =)


Sure. Though I think I'm hearing the laughter of a mad scientist now .. oh wait.. no, that was just me ;-)
0
 
LVL 3

Author Comment

by:Eric Bourland
ID: 37825480
I've made progress. I think the View is working correctly. I think I need to change my form, though, on the edit page.

If I go to the Manage page: http://test.ebwebwork.com/admin/managePages.cfm

and edit any page, the existing page values do not fill in to the form fields.

So I am not calling the existing form variables back into the form. You say my edit query is OK, so I guess I need to edit the form fields, for example:

         <cfinput type="Text"
      name="PageTitle"
      value="#Trim(Left(form.PageTitle,255))#"
                 message="Please enter a Page Title; maximum length is 255 characters."
                 required="Yes"
                 validateAt="onSubmit,onServer"
      size="80"
      maxlength="255" />

Should variable PageTitle become PageTitleLevel1?
0
 
LVL 52

Expert Comment

by:_agx_
ID: 37825542
I see no reason to change the form field names.   Since only the names of columns in the query changed - only code using that query need to be should change.   Can you post the edit page code? I'm not sure the latest was posted here.
0
 
LVL 3

Author Comment

by:Eric Bourland
ID: 37825786
Well, here is the code. I hate to post this much code. I have commented extensively. I see no reason to change form field names either, since the form still updates the same old content table.

(please don't spend too much time on this)

Thank you again.

Eric

<!-----
Name:        editPages.cfm
Author:      Eric Bourland / gdemaria / _agx_
Description: add, update web pages; create SEO-friendly URLs for web pages; make this code resuable and portable
Created:     September 2009
Revised: April 2012, to include a database view, with a lot of help from _agx_
ColdFusion Version 9
MS SQL Server 2005
----->

<!--- set a default value "" for PageID in scope URL --->
<cfparam name="url.PageID" default="">

<!--- define the PageID in scope FORM, then set form.PageID equal to the PageID passed in the URL --->
<cfparam name="form.PageID" default="#url.PageID#">

<!--- set default values for other user-editable fields --->
<cfparam name="form.PageTitle" default="">
<cfparam name="form.PageContentLeft" default="">
<cfparam name="form.PageContentRight" default="">
<cfparam name="form.DateCreated" default="">
<cfparam name="form.safeURL" default="">
<cfparam name="form.ParentID" default="">
<cfparam name="form.SortOrder" default="0">

<!--- in user-editable fields, set up protection against XSS  --->
    <cfloop collection="#FORM#" item="field">
             <cfset FORM[ field ] = ReReplaceNoCase (FORM[ field ], "<script.*?>.*?</script>", "", "all")>
    </cfloop>

<!--- query the view, to generate navigation menu on the index.cfm page --->
<!--- we need only PageID, PageTitle, SortOrder, and ParentID for each level --->
<!--- the view is called ViewEditPage--->
<cfquery name="queryView" datasource="#application.datasource#">
SELECT PageIDLevel1
      ,PageTitleLevel1
      ,SortOrderLevel1
      ,ParentIDLevel1
      ,PageIDLevel2
      ,PageTitleLevel2
      ,SortOrderLevel2
      ,ParentIDLevel2
      ,PageIDLevel3
      ,PageTitleLevel3
      ,SortOrderLevel3
      ,ParentIDLevel3
                   
            FROM     ViewEditPage
            WHERE   ISNULL(ParentIDLevel1, 0) = 0
            ORDER BY SortOrderLevel1, SortOrderLevel2, SortOrderLevel3
</cfquery>

<!--- query editPage tells ColdFusion which page to edit--->
<cfquery datasource="#application.datasource#" name="editPage">
SELECT PageIDLevel1
      ,PageTitleLevel1
      ,safeURLLevel1
      ,PageContentLeftLevel1
      ,PageContentRightLevel1
      ,SortOrderLevel1
      ,ParentIDLevel1
      ,DateCreatedLevel1
      ,DateModifiedLevel1
      ,PageIDLevel2
      ,PageTitleLevel2
      ,safeURLLevel2
      ,PageContentLeftLevel2
      ,PageContentRightLevel2
      ,SortOrderLevel2
      ,ParentIDLevel2
      ,DateCreatedLevel2
      ,DateModifiedLevel2
      ,PageIDLevel3
      ,PageTitleLevel3
      ,safeURLLevel3
      ,PageContentLeftLevel3
      ,PageContentRightLevel3
      ,SortOrderLevel3
      ,ParentIDLevel3
      ,DateCreatedLevel3
      ,DateModifiedLevel3
FROM ViewEditPage
WHERE PageIDLevel1 = <cfqueryparam cfsqltype="cf_sql_integer" value="#val(form.PageID)#">
</cfquery>

		   
<!---- begin CFTRY; catch errors ---->
<cftry>  
 
<!---- populate cftry with error message ---->
<cfset variables.error = ""> 
 
<!--- BEGIN: Save action --->

<!--- begin form.doSave --->

<cfif IsDefined("FORM.doSave")>


 <!--- in this query select NOTHING from the content table and simply check if PageTitle exists --->
 
 <cfquery datasource="#APPLICATION.dataSource#" name="CheckPageTitle">
  SELECT 'Nothing' FROM #REQUEST.contentTable#
  WHERE PageTitle = <CFQUERYPARAM CFSQLTYPE="cf_sql_varchar" VALUE="#Form.PageTitle#">
  AND pageID <> <CFQUERYPARAM CFSQLTYPE="cf_sql_integer" VALUE="#val(Form.pageID)#">
  </cfquery>
   
  
   <!--- if PageTitle exists, display error --->
   
<cfif CheckPageTitle.recordcount GT 0>
   <cfthrow message="Page title <em>'#PageTitle#'</em> is already taken; you must enter a unique page title.">
</cfif>


 <!--- set form.safeURL = form.PageTitle  --->
<cfset form.safeURL = replace(trim(form.PageTitle)," ","-","all")>
<cfset form.safeURL = rereplace(form.safeURL ,"[^[0-9a-zA-Z-]]*","","all")>


<!--- when a PageID Exists, the action is UPDATE --->
   
<cfif val(form.PageID)>

<!--- create SEO-Safe URL for this web page --->

<!--- set form.NEW_SafeURL equal to value of form.PageTitle --->
<cfset form.NEW_SafeURL = replace(trim(form.PageTitle)," ","-","all")>
<cfset form.NEW_SafeURL = rereplace(form.NEW_SafeURL ,"[^[0-9a-zA-Z-]]*","","all")>

<!--- Set the value of form.OLD_SafeURL equal to output of query editPage --->
<cfset form.OLD_SafeURL = editPage.safeURL>

<!--- delete the existing safeURL file from /pages/ --->
     <cffile action="delete"
             file = "c:\websites\#REQUEST.companyURL#\pages\#form.OLD_SafeURL#.cfm">

<!--- write the new safeURL file to /pages/ using value of form.NEW_SafeURL --->
     <cffile action = "write"
    	     nameconflict="overwrite"
             file = "c:\websites\#REQUEST.companyURL#\pages\#FORM.NEW_SafeURL#.cfm"
             output='<cfset url.pageID = #val(FORM.PageID)#><cfinclude template="/index.cfm">'>


<!--- Finished creating SEO-Safe URL for this web page --->
             
<!--- query UpdatePage updates a page record in content table --->
            <cfquery name="UpdatePage" datasource="#application.datasource#">
				  UPDATE #REQUEST.contentTable#
				  SET
           PageTitle = <cfqueryparam cfsqltype="cf_sql_varchar" value="#Trim(Left(form.PageTitle,255))#">,   
           PageContentLeft = <cfqueryparam cfsqltype="cf_sql_varchar" value="#Trim(form.PageContentLeft)#">,
           PageContentRight = <cfqueryparam cfsqltype="cf_sql_varchar" value="#Trim(form.PageContentRight)#">,
           DateModified = <cfqueryparam cfsqltype="cf_sql_timestamp" value="#now()#">,
           ParentID = <cfqueryparam cfsqltype="cf_sql_integer" value="#form.parentID#" null="#form.parentID eq -1#">,
           SortOrder = <cfqueryparam cfsqltype="cf_sql_integer" value="#val(form.SortOrder)#">,
           safeURL = <cfqueryparam cfsqltype="cf_sql_varchar" value="#Trim(Left(form.safeURL,255))#"> 
           WHERE PageID = <cfqueryparam cfsqltype="cf_sql_integer" value="#val(form.PageID)#">
			</cfquery>
     

<!--- CFELSE: if PageID does not exist, then create new Page --->
				<cfelse> 
                
                <!--- create SEO-Safe URL for this web page --->

<!--- set form.NEW_SafeURL equal to value of form.PageTitle --->
<cfset form.NEW_SafeURL = replace(trim(form.PageTitle)," ","-","all")>
<cfset form.NEW_SafeURL = rereplace(form.NEW_SafeURL ,"[^[0-9a-zA-Z-]]*","","all")>

  <!--- write the new safeURL file to /pages/ using value of form.NEW_SafeURL --->
     <cffile action = "write"
    	     nameconflict="overwrite"
             file = "c:\websites\#REQUEST.companyURL#\pages\#FORM.NEW_SafeURL#.cfm"
             output='<cfset url.pageID = #val(FORM.PageID)#><cfinclude template="/index.cfm">'>

<!--- Finished creating SEO-Safe URL for this web page --->
                
                
<!--- query to insert new user record into content table --->
			<cfquery name="InsertPage" datasource="#application.datasource#" result="newPage">
				 INSERT INTO #REQUEST.contentTable#
     					(
                        PageTitle,
                        PageContentLeft,
                        PageContentRight,
                        ParentID,
                        SortOrder,
                        DateCreated,
                        safeURL
                        )
			     VALUES(
	                <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))#">
				         )         
					</cfquery>
        
        
                   
<!--- use the result attribute value (newPage) to set form field value --->
      <cfset form.PageID = newPage.IDENTITYCOL>
              
<!--- END queries to update or insert database records ---> 

<!--- END cfif val(form.PageID) -- if a topic needed to be updated or added, then it was done --->
					    </cfif>  


       <!--- done? relocate --->

<cfif val(url.PageID)>
<cflocation url="/admin/editPages.cfm?PageID=#val(url.PageID)#" addtoken="yes">

<cfelse>                     
<cflocation url="/admin/managePages.cfm" addtoken="no">
				     
</cfif>

             
<!--- END: Save action --->

<!--- END form.doSave --->
                    </cfif>
       
<!--- END queries to update or insert database records ---> 
        

<!--- this CFCATCH will trap errors -- the ones you threw or just regular database issues --->
            <cfcatch type="Any">
                 <cfset variables.error = cfcatch.message>
            </cfcatch>

<!--- END CFTRY --->  
			</cftry>
       
       
     
<!--- fetch the data from the database when there are no errors; let the form variables pass back from the data table into the form to display ---->
 
<cfif len(variables.error) eq 0>

  			<cfloop index="aCol" list="#editPage.columnList#">
			       <cfset "form.#aCol#" = editPage[aCol][editPage.currentRow]>
			  </cfloop>
    
</cfif>



<!----- if PageID exists then display Update Page; else, display Create Page ----->
				<cfif val(url.PageID)>
					  <cfset FormTitle="Update Page">
					  <cfset ButtonText="Update This Page">
				<cfelse>
						<cfset FormTitle="Create Page">
						<cfset ButtonText="Create Page">
				</cfif>

       
<cfinclude template="/admin/admin_header.cfm">
<div class="align-center">

  <ul class="button-bar">
    <li><a href="#"><strong>Manage:</strong></a></li>
    <li><a href="/admin/managePages.cfm"><span class="icon small blue" data-icon="f"></span> Web Pages</a></li>
        

    <li><a href="/logout.cfm"><span class="icon red small" data-icon="M"></span> Log out</a></li>
  </ul>


<hr />

</div><!--- /align-center--->

<!--- if there is an error, display it in readable form, then abort mission --->

<cfif len(variables.error)> 
    <cfoutput>
	    <div class="errorbox">#variables.error#</div>
    </cfoutput>
   
   <br /><!--- clean up this HTML / CSS later --->

             <div class="align-center">
               <input type=button value="Go Back" onClick="history.go(-1)">
             </div>
             
             <cfabort>
</cfif>


<!--- if cftoken exists in scope URL after record update, then display Update Succeeded --->
<cfparam name="url.cftoken" default="">

<cfif len(url.cftoken)> 

<div class="notice success width150px align-center">Update Succeeded.</div>

</cfif>
<!--- open window for TinyMCE help content --->
<script language="JavaScript" type="application/javascript">
<!-- Begin
function popUp(URL) {
day = new Date();
id = day.getTime();
eval("page" + id + " = window.open(URL, '" + id + "', 'toolbar=0,scrollbars=1,location=0,statusbar=0,menubar=0,resizable=0,width=440,height=500,left = 383,top = 84');");
}
// End -->
</script>


	<!--- Add or Update record Form begins here --->
<cfform method="post" enctype="multipart/form-data" scriptsrc="#Request.CFFORM_JS_LIB#">
                
 
 <!--- define PageID in scope form --->
 <cfoutput>
<input type="hidden" name="PageID" value="#form.PageID#" />
   </cfoutput>



<cfoutput>
<h2>#FormTitle#</h2>
</cfoutput>



<p><strong>Page Title:</strong><br />
<span class="caption">Choose a descriptive page title. Use keywords that visitors are likely to enter in a search interface like Bing, Google, or Yahoo.</span><br />

  	 <cfinput
     		type="Text"
			name="PageTitle"
			value="#Trim(Left(form.PageTitle,255))#"
            message="Please enter a Page Title; maximum length is 255 characters."
            required="Yes"
            validateAt="onSubmit,onServer" 
			size="80"
			maxlength="255" /> <span class="caption red">255 characters maximum.</span></p>

<div class="clear-both"></div>

<!--- if page exists, it will already have an SEO-Friendly web address (URL) --->
<cfif val(url.PageID)>

<p><strong>The SEO-Friendly web address (URL) of this page is:</strong></p>
<div class="clear-both greensafebox">
   
  <p><span class="bold green"><cfoutput>http://#request.companyurl#/pages/#form.safeURL#.cfm</cfoutput></span><br />
<span class="caption">This is the SEO-friendly web address (URL) of this page. An SEO-friendly URL will result in a higher rank in search results from Bing, Google, and Yahoo. The SEO-friendly URL is derived automatically from the Page Title.  <a href="http://ebwebwork.com/seo.cfm" target="_blank">What is SEO?</a></span></p>
</div>
</cfif>

   

<h2>Left Column</h2>


              
   <textarea name="PageContentLeft"
   			width="770"
			height="500"
            style="width:770px;height:500px;" wrap="virtual">
 
            <cfoutput>#form.PageContentLeft#</cfoutput>
   
			</textarea> 

       
          
<h2>Right Column</h2>
               
              
   <textarea name="PageContentRight"
   			width="770"
			height="500"
            style="width:770px;height:500px;" wrap="virtual">
 
            <cfoutput>#form.PageContentRight#</cfoutput>
   
			</textarea> 
 
 
 <hr />           
         

<h2 class="align-center">How should this page fit in the web site navigation menu?</h2>

<p><strong>You have three options:</strong></p>

<h4>1. Make this page a parent page. <span class="tooltip question" title="A parent page is a menu item in main, top-level navigation.">?</span></h4>


<p><cfinput type="radio" value="0" name="ParentID" checked="#editPage.ParentIDLevel1 eq 0#" />
Make this page a "Parent" page in the main navigation menu.</p>


<h4>2. Make this page a child page; choose a parent page under which this page should appear. <span class="tooltip question" title="A child page is a sub-menu item that appears beneath a parent page.">?</span></h4>

 
<p>Choose parent page from the Select menu:</p>
    
<!--- query GetParents selects Parent pages from the content table --->
<!--- but excludes grandchild pages (ParentIDLevel3) --->
  <cfquery datasource="#application.datasource#" name="GetParents">
        SELECT  PageID, PageTitle, ParentID, SortOrder
        FROM    #REQUEST.contentTable#
        ORDER BY ParentID asc, SortOrder asc
</cfquery>
  
<!--- query getSelectedParents selects SELECTED Parent page from content table --->
  <cfquery datasource="#application.datasource#" name="getSelectedParents">
        SELECT  PageTitle, ParentID
        FROM    #REQUEST.contentTable#
        WHERE ParentID = <cfqueryparam cfsqltype="cf_sql_integer" value="#val(form.ParentID)#">
</cfquery>

<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;">
      
    	</cfselect>                
  <!--- when form is processed, the correct ParentID is populated into content table --->
  <!--- ParentID value 0 means the record / web page is included in top-level, main navigation menu --->
  <!--- ParentID value 100000 means the record / web page is EXCLUDED from navigation menu --->
  
  <h4>Choose a "sort order" for this page.  
  <span class="tooltip question" title="For example, if you want this page to appear third in a series of menu items, enter a value of 3.">?</span><br />

    
    <cfinput type="text" name="SortOrder" value="#editPage.SortOrderLevel1#" size="1" maxlength="2" />
    </h4>
  

<h4>3. Exclude this page from navigation.
<span class="tooltip question" title="You can exclude this page from navigation. Not all pages need to appear in the  navigation menu.">?</span></h4>

<p><cfinput type="radio" value="100000" name="ParentID" checked="#editPage.ParentIDLevel1 eq 100000#" />
Exclude this page from the navigation menu.</p>
      


<h4>Notes:</strong></h4>

   <ul class="checks">
    <li>A parent page is a menu item in main, top-level navigation.</li>

    <li>A child page is a sub-menu item that appears beneath a parent page.</li>
    
    <li>A page can be excluded from the navigation menu. This action does not delete the page.</li>
    
  </ul>

                    

<!--- submit form to ColdFusion for processing; this is the DoSave function, which will add or edit a record --->

   <div class="align-center"> <cfoutput>
  
   <button name="doSave" type="submit" class="medium orange">#ButtonText#</button>
  
   </cfoutput>
   </div>

</cfform>



<!--- Page footer --->
<cfinclude template="/admin/admin_footer.cfm">

Open in new window

0
 
LVL 52

Expert Comment

by:_agx_
ID: 37828039
Ohh .. I see the problem.  I didn't realize the code copies the query values into the FORM scope dynamically.

       <cfif len(variables.error) eq 0>
            <cfloop index="aCol" list="#editPage.columnList#">
                   <cfset "form.#aCol#" = editPage[aCol][editPage.currentRow]>
              </cfloop>
       </cfif>

So it doesn't work because the names no longer don't match.  ie The form uses the old names:
         form.PageID, form.PageTitle, ...

.. and the query returns the new names:
          PageIDLevel1, PageTitleLevel1, ...

If you want to use the old names,  alias the "Level1" columns in your editPage query.  So it returns the old names (PageID, PageTitle, ...)  instead of (PageIDLevel1, PageTitleLevel1, ...)

You can leave the "Level2" and "Level3" columns as is -OR- rename them as "ParentColumnX" and "GrandParentColumnX" if its easier to follow.  Though you probably don't need all the parent and grandparent columns, maybe just parent/grandparent pageID (I think)?  

Whichever name you choose, be sure to use it consistently throughout the edit page CF code.

ie   Example of rename
       SELECT    PageIDLevel1                   AS  PageID                <=== alias to use old names
                       , PageTitleLevel1             AS  PageTitle
                       , ....
                       , PageIDLevel2               AS ParentPageID
                       , PageIDLevel3               AS GrandParentPageID
        FROM   ViewEditPage
        WHERE PageIDLevel1 = <cfqueryparam cfsqltype="cf_sql_integer" value="#val(form.PageID)#">
0
 
LVL 3

Author Comment

by:Eric Bourland
ID: 37829894
_agx_, it's working really well. I chose to leave the Edit query as so:

<!--- query editPage tells ColdFusion which page to edit--->
<cfquery datasource="#application.datasource#" name="editPage">
SELECT PageIDLevel1 AS PageID
      ,PageTitleLevel1 AS PageTitle
      ,safeURLLevel1 AS safeURL
      ,PageContentLeftLevel1 AS PageContentLeft
      ,PageContentRightLevel1 AS PageContentRight
      ,SortOrderLevel1 AS SortOrder
      ,ParentIDLevel1 AS ParentID
      ,DateCreatedLevel1 AS DateCreated
      ,DateModifiedLevel1 AS DateModified
      ,PageIDLevel2
      ,PageTitleLevel2
      ,safeURLLevel2
      ,PageContentLeftLevel2
      ,PageContentRightLevel2
      ,SortOrderLevel2
      ,ParentIDLevel2
      ,DateCreatedLevel2
      ,DateModifiedLevel2
      ,PageIDLevel3
      ,PageTitleLevel3
      ,safeURLLevel3
      ,PageContentLeftLevel3
      ,PageContentRightLevel3
      ,SortOrderLevel3
      ,ParentIDLevel3
      ,DateCreatedLevel3
      ,DateModifiedLevel3
FROM ViewEditPage
WHERE PageIDLevel1 = <cfqueryparam cfsqltype="cf_sql_integer" value="#val(form.PageID)#">
</cfquery>

I have created and utilized a View. I see it working now, and I see the View in the database itself; it looks and works just like a table.

I realize also that I can use this View for only one web site. Each web site that I set up will need its own content table, and its own View.

>>You'd have to remember that "Level1" means something different when you're generating the menu (ie from top down) than when you're doing a page edit.  

This makes sense now because I can edit all levels of pages using just the PageID; the levels are irrelevant.

So, I have learned a great deal. Thank you very much for your patient tutelage. =)

In all of this, my purpose has been:

1) to set up three levels of navigation -- displayed in web site navigation, and in the Manage Pages menu
2) in the Edit Page, to constrain the user to be able to assign "child" pages to levels 1 (parent) and 2 (child) pages, but not to level 3 (grandchild). Grandchild pages do not get children.

I am not sure that I like the added complexity of the view. I am still thinking about this. I used to have just the content table to worry about. Now I have a view.

Do you think this is OK? Do you think I have made a good system here?

Could I do this in a better way?

Thank you again, as always.

Eric
0
 
LVL 52

Expert Comment

by:_agx_
ID: 37830428
I realize also that I can use this View for only one web site. Each web site that I set up will need its own content table, and its own View.
Why? How were you handling it before? I guess  I don't understand what difference the addition of a view makes.

I am not sure that I like the added complexity of the view. I am still thinking about this. I used to have just the content table to worry about. Now I have a view.

Do you think this is OK? Do you think I have made a good system here?

Well I don't know much about the system beyond the menu and edit page query. Edit: Though it does sounds like you need info about all 3 levels in both places. But what is it you think you have to worry about now that you have a view + table, instead of just a table.  From my POV it's not much different than having multiple tables in a db.

Personally I dislike having complex queries duplicated across many pages, because each time you copy it there's a) the possibility something will be changed/lost in one of the queries and b) if you need to make a general change it then requires modifying X code sections instead of just 1. So my personal preference is to keep complex SQL queries in one place for consistency and maintainability.

That said, as long as the SQL is the correct, either approach would be fine. I guess I'm just not seeing the benefit of eliminating the VIEW. Unless it's a matter of comfort, in which case "understandability" usually trumps all. Because if you perceive it as cryptic, then it will take longer every time you need to work with the code, cancelling out any centralization benefits.
0
 
LVL 3

Author Comment

by:Eric Bourland
ID: 37830781
>>>>I realize also that I can use this View for only one web site. Each web site that I set up will need its own content table, and its own View.

>>>Why? How were you handling it before adding the view?

<ot>Well, I build and maintain a handful of web sites for (very) small businesses, a few artist types who never pay me but whom I support, =) and a few shoestring do-gooder not-for-profits. (I make ends meet by doing other, various IT work.)</ot> Each web site has a separate content table, with columns PageID, PageTitle, LeftContent, RightContent, safeURL, etc. I name the content table in application.cfc in the request scope: #REQUEST.ContentTable#. And I re-use almost the exact code in all of the edit pages. gdemaria and you have made very good points to me about re-using code.

I suppose I could have one master table, with all of those usual columns (PageID, PageTitle, and so on), and then another table with a list of client web sites, and each web site had a unique WebSiteID, and I could join the two tables as needed for each web site. I've thought about doing this and it is still something I might do. The problem is, some of the web sites I build do not use all of the same, exact data table columns.

So, in part, this question has become a (much-needed) discussion about information architecture.

I definitely want to reuse as much code as possible, and to not duplicate queries. The systems I set up are very simple. There is the manage page, like this:

http://test.ebwebwork.com/admin/managePages.cfm

and the edit page, for example:  http://test.ebwebwork.com/admin/editPages.cfm?PageID=106

and maybe a few other pages that I built to help clients send HTML-formatted newsletters to mailing lists, or manage news items.

Almost all code stays the same across almost all of the web sites. And there is a single content table for each web site.

So far that has been intuitive, and it has worked well.

I am comfortable with using the view. If a view is the optimal way to offer clients three levels of navigation, and also <i>constrain</i> them to three levels of navigation, then I will learn it and be comfortable with it.

>>>Unless it's a matter of comfort, in which case understanding usually trumps all.

I agree.

So my question was, is this OK? Could I be smarter about this? It does seem like I will need to create a view, and create a content table, for each web site going forward. Unless I can use the "ViewEditPage" view for multiple web sites -- but I don't think I can. Am I correct?

Thanks again for reading this and helping me learn. I really appreciate your time.

Eric
0
 
LVL 52

Expert Comment

by:_agx_
ID: 37833782
If you're using a separate table for each site, then yes you'd need to create a separate VIEW for each one.

The systems I set up are very simple... and maybe a few other pages that I built to help clients send HTML-formatted newsletters to mailing lists, or manage news items.

If you're only using the "level1/2/3" type queries in 2 places, it's 6 of one and half-dozen of the other. Usually the tipping point is higher. For example, I worked on a legacy app that duplicated a big complex query in 9+ places.  So using a view was a no-brainer. But your structure sounds much simpler. If the goal is to keep it as minimal as possible (and the sql's only used twice anyway)  there's less reason to add a view to the mix.

I suppose I could have one master table, with ...The problem is, some of the web sites I build do not use all of the same, exact data table columns.

Well it depends how much customization you're ultimately going to add to the basic template.  If you're only supporting generic features (ie could potentially be used by all sites)  a master table makes more sense. It's also slightly easier to maintain. On the other hand, if you foresee supporting a lot of highly localized features and customizations, separate tables may a better choice.  It's more moving pieces,  but it's better than ending up with one monster tables of 150+ columns because you were trying to fit a bunch of disparate attributes into a single model.  It's also easier to make changes without impacting other sites.  So again, it all depends.
0
 
LVL 3

Author Comment

by:Eric Bourland
ID: 37834520
_agx_

I think having a master table will create complications in the end, as I add more columns to accommodate different web site designs. I'm working with a new designer who likes to build artistic designs that challenge my CSS skills (which is cool) and require me to use new content areas -- which require new and different database columns.

>>>If the goal is to keep it as minimal as possible (and the sql's only used twice anyway)  there's less reason to add a view to the mix.

This was my thought too. My original task was to omit Grandchild pages from the "Choose Parent" select menu on the Edit Page, for example:

http://test.ebwebwork.com/admin/editPages.cfm?PageID=122

Is there a way to do that without using a view? Even if it means using an extra query on the edit page?

I liked learning about using views. If using a view is the best way to omit a grandchild from that select menu, then I'll use it.

Thank you again.

Eric
0
 
LVL 52

Expert Comment

by:_agx_
ID: 37834784
Is there a way to do that without using a view? Even if it means using an extra query on the edit page?

Sure. The sole purpose of the view is centralization.  But remember it's just a wrapper for a SELECT. So you can just as easily take the SELECT that's inside the view - and run it w/in a cfquery instead. It'll do exactly the same thing.
0
 
LVL 3

Author Comment

by:Eric Bourland
ID: 37835342
>>>So you can just as easily take the SELECT that's inside the view - and run it w/in a cfquery instead.

I've been running this through my head for a while. So, I make a new query:

<!--- query to get grandchild pages, so we can omit them from SELECT MENU --->
<cfquery name="getGrandChildren" datasource="#application.datasource#">
      SELECT
       p.PageID     AS PageIDLevel1
      ,p.PageTitle  AS PageTitleLevel1
      ,p.SortOrder  AS SortOrderLevel1
      ,p.ParentID   AS ParentIDLevel1
      ,c.PageID     AS PageIDLevel2
      ,c.PageTitle  AS PageTitleLevel2
      ,c.SortOrder  AS SortOrderLevel2
      ,c.parentID   AS ParentIDLevel2
      ,gc.PageID    AS PageIDLevel3
      ,gc.PageTitle AS PageTitleLevel3
      ,gc.SortOrder AS SortOrderLevel3
      ,gc.parentID  AS ParentIDLevel3
     
FROM #REQUEST.contentTable# p
LEFT JOIN #REQUEST.contentTable#   c ON c.parentID = p.pageID
LEFT JOIN #REQUEST.contentTable#   gc ON gc.parentID = c.PageID
</cfquery>

Then, I need to omit grandchild pages from the parent pages SELECT menu. I've been thinking about this for several days. =) I need to add a clause to the query that requests parent pages to display in the SELECT menu:

<p>Choose parent page from the Select menu:</p>
   
<!--- query GetParents selects Parent pages from the content table --->
<!--- but excludes grandchild pages (ParentIDLevel3) --->
  <cfquery datasource="#application.datasource#" name="GetParents">
        SELECT  PageID, PageTitle, ParentID, SortOrder
        FROM    #REQUEST.contentTable#
        WHERE PageID <> <cfqueryparam cfsqltype="cf_sql_integer" value="#val(getGrandChildren.PageIDLevel3)#">
        ORDER BY ParentID asc, SortOrder asc
</cfquery>
 
<!--- query getSelectedParents selects SELECTED Parent page from content table --->
  <cfquery datasource="#application.datasource#" name="getSelectedParents">
        SELECT  PageTitle, ParentID
        FROM    #REQUEST.contentTable#
        WHERE ParentID = <cfqueryparam cfsqltype="cf_sql_integer" value="#val(form.ParentID)#">
</cfquery>

<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;">
     
          </cfselect>                

I'm trying this ... hmm. Darn. The grandchild pages still display in the dropdown menu. So I need to alter my query ... or both queries. What am I missing?

Thanks again. Sorry this has dragged on so long.

Eric
0
 
LVL 52

Accepted Solution

by:
_agx_ earned 500 total points
ID: 37835554
Disclaimer, it's been a long day. But for the select list query - all you need to do is exclude any records where the grandparent is NOT null. And of course exclude the pageID being edited, since a page can't be it's own parent.  So something like this would return all pages that aren't grandchildren

SELECT        base.PageID  
                 , base.PageTitle
                 , base.SortOrder
                 , base.ParentID  
     
FROM yourTable base
            LEFT JOIN yourTable p ON p.pageID = base.parentID   <!--- find it's parent --->
            LEFT JOIN yourTable gp ON gp.pageID = p.parentID      <!--- find it's grandparent --->
WHERE gp.PageID IS NULL
AND     base.PageID <>  #thePageIDBeingEdited#
0
 
LVL 3

Author Comment

by:Eric Bourland
ID: 37835972
>>> So something like this would return all pages that aren't grandchildren

I will try that. I am not gonna bug ya tomorrow. =) I'll work on this.

Thank you. Have a good evening _agx_. Hope you are well.

Eric
0
 
LVL 52

Expert Comment

by:_agx_
ID: 37836007
No worries, your threads are always enjoyable :)

I tested the query above, so it should work as advertised (as long as my sleepy brain didn't overlook a copy/paste error).
0
 
LVL 3

Author Closing Comment

by:Eric Bourland
ID: 37840341
Working like a charm. I learned a ton from this question. I optimized a lot of code, too. I see how using a view is useful, and I am grateful to have learned to use views. As I often do, I am thinking how I could have phrased my question more clearly. The problem is always clearer after it has been solved. =) As always I am very grateful to _agx_ and gdemaria for their expertise and patience.

Eric
0

Featured Post

Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Suggested Solutions

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…
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…
Illustrator's Shape Builder tool will let you combine shapes visually and interactively. This video shows the Mac version, but the tool works the same way in Windows. To follow along with this video, you can draw your own shapes or download the file…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

708 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now