Solved

Strategy to add document subtopics to document topics?

Posted on 2010-09-20
188
610 Views
Last Modified: 2012-05-10
Task in brief: I need to add document subtopics to a list of document topics.

Details:

I've been thinking for several days about the best way to go about this. I think I need to create a table to contain subtopics, and a relational table to relate document topics to document subtopics.

More details:

* I have a group of document topics listed in a table, tbl_CEP_Document_Topic. There are topics like:

Common Core Standards
Federal Education Programs
High School Exit Exams                                          
Public School Facts, History and Issues

...etc.

* Documents are contained in table tbl_CEP_Documents

* A relational table assigns documents to topics: tbl_Document_Has_Topic

So there are three tables so far:

1. documents: tbl_CEP_Documents
DocumentID (pk)
DocumentTitle, DocumentAuthor, etc

2. topics: tbl_CEP_Document_Topic
DocumentTopicID (pk)
DocumentTopic, DocumentTopicDescription

3. relate documents to topics: tbl_Document_Has_Topic
DocumentID
DocumentTopicID

Now the client wants a hierarchy of document topics and subtopics, like this:

Federal Education Programs
            ---Congressional Testimony
            ---Economic Stimulus Package/ARRA
            ---IDEA/Special Education
            ---No Child Left Behind

High School Exit Exams                                                                              
            ---Annual Reports and State Profiles
            ---Policy Briefs
            ---Special Reports and Articles

... etc.

How should I go about this? Should I create a new table, CEP_Document_Subtopics?

table: CEP_Document_Subtopics
with columns:
DocumentSubTopicID
DocumentTopicID (from table tbl_CEP_Document_Topic)
DocumentSubTopic, DocumentSubTopicDescription

Should I add to tbl_Document_Has_Topic (relational table, above, that relates Documents to Topics) the new DocumentSubTopicID column?

So now tbl_Document_Has_Topic would have three foreign keys:

DocumentID
DocumentTopicID
DocumentSubTopicID

Correct?

Though I still need another table to relate DocumentTopics to DocumentSubTopics:

tbl_DocumentTopics_Has_DocumentSubTopics

Am I on the right track? I try to think this through before I ask my question in EE. I also realize my questions are long and complicated. I spend a lot of time framing, writing, and editing them before I post. Thank you for reading.

As always: peace and gratitude!

Eric
0
Comment
Question by:Eric Bourland
  • 105
  • 78
  • 4
  • +1
188 Comments
 
LVL 4

Expert Comment

by:sandy12879
ID: 33722927
Hi Eric,
Yes that is one of the way through you can acheive the same, i had a similar requirement and we created txt files for Sub topics,so for eg Federal Education Programs,we had a txt file for its subtopicsand the whole list is maintained in there,just read the txt file and populate it in the front end.
I assume the subtopics are not going to change frequently,keeping the same thought we didnt add another table in the DB.
0
 
LVL 39

Accepted Solution

by:
gdemaria earned 500 total points
ID: 33726670
Hey Eric,
You definitely want to do this in the database; doing it outside the database would lead to quite a mess.

A couple questions in order to determine how to position the tables/foreign keys.

 Does every sub-topic belong to one and only one topic?   In other words, a topic will always be found under the same topic, never 2 different topics.

 It appears that your document can have more than one Topic?  Is that true?  

 Will a document be forced to have a subtopic, or can the document be placed at the topic-level without a subtopic?


 
Your table definition for CEP_Document_Subtopics  seems correct.   Once you address the questions above we can discuss how to connect everything.
0
 
LVL 3

Author Comment

by:Eric Bourland
ID: 33727163
gdemaria,

Good to hear from you.

>>> Does every sub-topic belong to one and only one topic?   In other words, a topic will always be found under the same topic, never 2 different topics.

Correct. A subtopic belongs to only on topic.

>>> It appears that your document can have more than one Topic?  Is that true?

True. A document can belong to more than one topic and more than one subtopic.

>>>> Will a document be forced to have a subtopic, or can the document be placed at the topic-level without a subtopic?

No. Documents are not forced to have a subtopic.

I will set up the table CEP_Documents_Subtopics. Thank you. I hope your day is going well.

Eric
0
 
LVL 3

Author Comment

by:Eric Bourland
ID: 33727190
sandy12879,

Thank you for your reply. The subtopics will change now and then, and it will be useful for the client to have a convenient interface in which to make changes, and the rest of the application is in a database, so I will go ahead and set up subtopics in a database table. I should have said, up front, that I am using MS SQL Server 2005 for my database; and ColdFusion 8. I appreciate that you took time to make a thoughtful reply.

Eric
0
 
LVL 39

Assisted Solution

by:gdemaria
gdemaria earned 500 total points
ID: 33727255

I agree with this approach to now allow a document to have topics and subtopics

> So now tbl_Document_Has_Topic would have three foreign keys:

DocumentID
DocumentTopicID
DocumentSubTopicID


> Though I still need another table to relate DocumentTopics to DocumentSubTopics:

>  tbl_DocumentTopics_Has_DocumentSubTopics


I don't feel you need this extra table because of your answer to this question > Correct. A subtopic belongs to only on topic.

In this case you only need to add a TOPIC_ID to your SubTopics table.    If there was a many to many relationship, it would require the extra table.




One question I missed,  are you stopping at subtopics, or will there be a sub-sub-topic or any lower levels?  Try to avoid this to make your life easier !




0
 
LVL 3

Author Comment

by:Eric Bourland
ID: 33727595
>>> will there be a sub-sub-topic or any lower levels?

There will be no sub-sub-topics. I talked the client into stopping at sub-topics.

>>>In this case you only need to add a TOPIC_ID to your SubTopics table.

That is a good idea. I am working on this and will get back here soon.
0
 
LVL 3

Author Comment

by:Eric Bourland
ID: 33729169
Now I have four tables and this is their structure:

1. documents: tbl_CEP_Documents
DocumentID (pk)
DocumentTitle, DocumentAuthor, etc

2. topics: tbl_CEP_Document_Topic
DocumentTopicID (pk)
DocumentTopic, DocumentTopicDescription

3. subtopics: tbl_CEP_Document_Subtopic
DocumentSubTopicID (pk)
DocumentSubTopicTitle, DocumentSubTopicDescription
DocumentTopicID (foreign key from tbl_CEP_Document_Topic)

4. and, a table to relate documents to topics and subtopics:
tbl_Document_Has_Topic, which has three foreign keys from the tables above:
DocumentID
DocumentTopicID
DocumentSubTopicID

I am trying to think what should my next step be. I need to edit the familiar insert_update.cfm application. I will need to make these changes:

* edit the code that updates or inserts an existing document, or inserts a new document (to account for new document subtopics)

* edit the code that updates or inserts an existing topic or subtopic

* create the HTML / CSS that displays a list a topics, with their subtopics


First, I edited the code that updates or inserts an existing document, or inserts a new document. I did that, but: I can't test this code till I populate tbl_CEP_Document_Subtopic with Subtopics. Which means I need to build the interface to populate tbl_CEP_Document_Subtopic. Which means I need to edit insert_update.cfm to display a list of Topics and their Subtopics.

So that is what I will work on next. I took a stab at doing this, below.

I have been spinning my wheels a little, thinking about which step I should take next. Right now I am manually populating tbl_CEP_Document_Subtopic with Subtopics.

Thanks again.

EB
<!--- this query requests values from columns in tbl_CEP_Document_Topics, tbl_CEP_Document_Subtopic --->

 <cfquery datasource="#ds#" name="GetDocumentTopics">

SELECT DocumentTopicID, DocumentTopic, DocumentSubTopicID, DocumentSubTopicTitle

FROM tbl_CEP_Document_Topic, tbl_CEP_Document_Subtopic

ORDER BY DocumentTopic, DocumentSubTopicTitle

  </cfquery>

 

    <p><strong>Assign this Document to CEP Topics and Subtopics</strong> <a href="#" class="hintanchor" onMouseover="showhint('Assign this document to a topic and / or a subtopic', this, event, '200px')">[?]</a></p>

    

    

      <p class="small">Choose at least one topic or subtopic. To select more than one topic or subtopic, hold the Ctrl or the Shift key (on your keyboard) and select topics or subtopics with the mouse cursor.</p>

   



<!--- query to get existing topics for the current documentID --->



       <cfquery datasource="#ds#" name="GetSelectedTopics">

              SELECT DocumentTopicID, DocumentSubTopicID

              FROM    tbl_Document_Has_Topic

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

       </cfquery>

     

       <!--- convert the DocumentTopicID values to a list --->

      <cfset form.SelectDocumentTopics = ValueList(GetSelectedTopics.DocumentTopicID)>





       <!--- here, I am not sure how to display topics AND subtopics in the same SELECT menu --->

       



<cfselect name="SelectDocumentTopics"

size="8"

multiple="yes"

query="GetDocumentTopics"

queryPosition="below"

value="DocumentTopicID"

display="DocumentTopic"

selected="#form.SelectDocumentTopics#">

     <option value="0">Choose topic:</option>

</cfselect>

Open in new window

0
 
LVL 39

Expert Comment

by:gdemaria
ID: 33729262

Hey Eric,

 This custom tag may be helpful you.  It will create two select tags that are dependent on each other.  That could be used whereever your user is to select a topic, he/she can then select a topic and subtopic..

http://www.naropa.edu/cfwebstorefb/customtags/TwoSelectsRelated.html
0
 
LVL 3

Author Comment

by:Eric Bourland
ID: 33729578
>>>http://www.naropa.edu/cfwebstorefb/customtags/TwoSelectsRelated.html

That is helpful! The first example looks useful because it allows me to select a subtopic without selecting a topic (which is what I need this CEP document application to do).

I will see about implementing this tag. Thank you again. I'll come back here soon with my results.
0
 
LVL 39

Expert Comment

by:gdemaria
ID: 33729655

You need to have a topic for any subtopic, the subtopic is a child of topic so it's automatic which topic you have.  Example 1 is simply showing you the subtopics for the fist topic by default
0
 
LVL 3

Author Comment

by:Eric Bourland
ID: 33729670
>>>You need to have a topic for any subtopic, the subtopic is a child of topic so it's automatic which topic you have.

Hmmm.

OK.

I think that will work.
0
 
LVL 3

Author Comment

by:Eric Bourland
ID: 33730603
I did some searching and found the actual CFML template for the custom tag, TwoSelectsRelated, here at Adobe:

http://www.adobe.com/cfusion/exchange/index.cfm?s=4&o=desc&exc=1&event=productHome&from=2

(I think that is the CFML template I am supposed to use?)

Am I going down the right path with this custom tag? I have been staring at the TwoSelectsRelated code for about three hours and I am not sure how to set up my query to request columns from tables

tbl_CEP_Document_Topic
tbl_CEP_Document_Subtopic

and make that query work with the TwoSelectsRelated custom tag. I feel like I am introducing complexity. We carefully set up the four tables, above. But I do not see how to use those tables with the TwoSelectsRelated custom tag. Sorry to be dense.

Should I get rid of all of the code, below, that we carefully built before, and use:

  <CF_TwoSelectsRelated
      QUERY="Server.Cats"
      NAME1="Category"
      NAME2="Selection"
      DISPLAY1="Master_Cat"
      DISPLAY2="Cat"
      VALUE1="MCode"
      VALUE2="Code"
      SIZE1="Auto"
      SIZE2="Auto"
      AUTOSELECTFIRST="No"
      FORMNAME="FormX">

Is it simpler to use this custom tag, or to modify code that I already know and trust? My instinct says, choose the latter. I really appreciate your guidance in this matter. =) Thanks. Eric
<!--- this query requests values from columns DocumentTopicID and DocumentTopic in tbl_CEP_Document_Topics --->

 <cfquery datasource="#ds#" name="GetDocumentTopics">

SELECT DocumentTopicID, DocumentTopic

FROM tbl_CEP_Document_Topic

ORDER BY DocumentTopic

  </cfquery>

 

    <p><strong>Assign this Document to CEP Topics</strong> <a href="#" class="hintanchor" onMouseover="showhint('Assign this document to a topic', this, event, '200px')">[?]</a></p>

  

    

      <p class="small">Choose at least one topic. To select more than one topic, hold the Ctrl or the Shift key (on your keyboard) and select topics with the mouse cursor.</p>

   

<!--- query to get existing topics for the current documentID --->



       <cfquery datasource="#ds#" name="GetSelectedTopics">

              SELECT DocumentTopicID

              FROM    tbl_Document_Has_Topic

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

       </cfquery>

     

       <!--- convert the DocumentTopicID values to a list --->

      <cfset form.SelectDocumentTopics = ValueList(GetSelectedTopics.DocumentTopicID)>



<cfselect name="SelectDocumentTopics"

size="8"

multiple="yes"

query="GetDocumentTopics"

queryPosition="below"

value="DocumentTopicID"

display="DocumentTopic"

selected="#form.SelectDocumentTopics#">

     <option value="0">Choose topic:</option>

</cfselect>

Open in new window

0
 
LVL 39

Expert Comment

by:gdemaria
ID: 33730821
Hey Eric - this is the way you use the 2-selects tag.  Put the code you downloaded into the same folder as your test page or into the Cf custom tag folder and check it out.

This code will give you an easy way of choosing a topic and subtopic, it is different than the code you posted above with the single select.  With just one select, how will you ask them to choose both topic and subtopic?


       <cfquery datasource="#ds#" name="getTopics">

           SELECT tp.DocumentTopicID

                  tp.DocumentTopicTitle

                  sub.DocumentSubTopicID

                  sub.DocumentSubTopicTitle

            FROM tbl_CEP_Document_topic tp

             left join  tbl_CEP_Document_Subtopic sub on sub.DocumentTopicID on tp.DocumentTopicID

            order by tp.DocumentTopicTitle, sub.DocumentSubTopicTitle

       </cfquery>

     

<CF_TwoSelectsRelated

      QUERY="getTopics"

      NAME1="DocumentTopicID"

      NAME2="DocumentSubTopicID"

      DISPLAY1="DocumentTopicTitle"

      DISPLAY2="DocumentSubTopicTitle"

      VALUE1="DocumentTopicID"

      VALUE2="DocumentSubTopicID"

      SIZE1="Auto"

      SIZE2="Auto"

      AUTOSELECTFIRST="Yes"

      FORMNAME="MyForm">



      

Open in new window

0
 
LVL 3

Author Comment

by:Eric Bourland
ID: 33730996
This is much clearer to me now.

>>>With just one select, how will you ask them to choose both topic and subtopic?

I was thinking I would use the single Select menu to list Topics and Subtopics; then, I also see there are problems with that idea -- how would the ColdFusion know which table to update with input from the Select menu?

I see what you are doing with the TwoSelectsRelated tag. This is better. I will follow your advice.

OK, I tried your code, and I get this error:

[Macromedia][SQLServer JDBC Driver][SQLServer]Incorrect syntax near '.'.  
 
The error occurred in C:\websites\ebwebwork.com\cep\admin\insert_update_test.cfm: line 589
Called from C:\websites\ebwebwork.com\cep\admin\insert_update_test.cfm: line 495
Called from C:\websites\ebwebwork.com\cep\admin\insert_update_test.cfm: line 1
Called from C:\websites\ebwebwork.com\cep\admin\insert_update_test.cfm: line 589
Called from C:\websites\ebwebwork.com\cep\admin\insert_update_test.cfm: line 495
Called from C:\websites\ebwebwork.com\cep\admin\insert_update_test.cfm: line 1
 
587 :       <p class="small">Choose at least one topic. To select more than one topic, hold the Ctrl or the Shift key (on your keyboard) and select topics with the mouse cursor.</p>
588 :    
589 : <cfquery datasource="#ds#" name="getTopics">
590 :            SELECT tp.DocumentTopicID
591 :                   tp.DocumentTopic
 

Is there a problem with:

         LEFT JOIN tbl_CEP_Document_Subtopic sub ON sub.DocumentTopicID ON tp.DocumentTopicID

?
0
 
LVL 39

Expert Comment

by:gdemaria
ID: 33731037

Lol, just because some part of the code is new, there could be some simple old problems, right?   I happen to have left out all commas from the select list :)


SELECT tp.DocumentTopicID
             tp.DocumentTopicTitle
             sub.DocumentSubTopicID
              sub.DocumentSubTopicTitle
FROM tbl_CEP_Document_topic tp
  left join  tbl_CEP_Document_Subtopic sub on sub.DocumentTopicID on tp.DocumentTopicID
order by tp.DocumentTopicTitle, sub.DocumentSubTopicTitle

0
 
LVL 3

Author Comment

by:Eric Bourland
ID: 33731106
Which I should have seen. =)

OK, that's fixed. Now I get:

Macromedia][SQLServer JDBC Driver][SQLServer]An expression of non-boolean type specified in a context where a condition is expected, near 'ON'.  
 
The error occurred in C:\websites\ebwebwork.com\cep\admin\insert_update_test.cfm: line 589
Called from C:\websites\ebwebwork.com\cep\admin\insert_update_test.cfm: line 495
Called from C:\websites\ebwebwork.com\cep\admin\insert_update_test.cfm: line 1
Called from C:\websites\ebwebwork.com\cep\admin\insert_update_test.cfm: line 589
Called from C:\websites\ebwebwork.com\cep\admin\insert_update_test.cfm: line 495
Called from C:\websites\ebwebwork.com\cep\admin\insert_update_test.cfm: line 1
 
587 :       <p class="small">Choose at least one topic. To select more than one topic, hold the Ctrl or the Shift key (on your keyboard) and select topics with the mouse cursor.</p>
588 :    
589 : <cfquery datasource="#ds#" name="getTopics">
590 :            SELECT tp.DocumentTopicID
591 :                   , tp.DocumentTopic
 
Does this line look OK to you?

LEFT JOIN tbl_CEP_Document_Subtopic sub ON sub.DocumentTopicID ON tp.DocumentTopicID

The two ON operators look wrong to me?
0
 
LVL 3

Author Comment

by:Eric Bourland
ID: 33738777
This setup makes a lot of sense to me:

<cfquery datasource="#ds#" name="getTopics">
           SELECT tp.DocumentTopicID
                  , tp.DocumentTopic
                  , sub.DocumentSubTopicID
                  , sub.DocumentSubTopicTitle
           FROM tbl_CEP_Document_Topic tp
           LEFT JOIN tbl_CEP_Document_Subtopic sub
           ON sub.DocumentTopicID
           ON tp.DocumentTopicID
           ORDER BY tp.DocumentTopic, sub.DocumentSubTopicTitle
</cfquery>
       
<CF_TwoSelectsRelated  
      QUERY="getTopics"
      NAME1="DocumentTopicID"  
      NAME2="DocumentSubTopicID"  
      DISPLAY1="DocumentTopic"  
      DISPLAY2="DocumentSubTopicTitle"  
      VALUE1="DocumentTopicID"  
      VALUE2="DocumentSubTopicID"  
      SIZE1="Auto"  
      SIZE2="Auto"  
      AUTOSELECTFIRST="Yes"  
      FORMNAME="MyForm">  


I have been trying different ideas to negotiate the error:

Macromedia][SQLServer JDBC Driver][SQLServer]An expression of non-boolean type specified in a context where a condition is expected, near 'ON'.  

Should there be two ON operators in a row?

Eric
0
 
LVL 52

Expert Comment

by:_agx_
ID: 33738823
(no points please ...)

>> Should there be two ON operators in a row?

No.  Maybe you meant to use equals "=" ?

...
FROM tbl_CEP_Document_Topic tp
           LEFT JOIN tbl_CEP_Document_Subtopic sub
           ON sub.DocumentTopicID = tp.DocumentTopicID


0
 
LVL 3

Author Comment

by:Eric Bourland
ID: 33738987
>>>ON sub.DocumentTopicID = tp.DocumentTopicID

Could have sworn I tried that. Trying again...

... and that works now. =) The error is resolved!

The subtopics are not displaying in the second SELECT box and I am looking at that next.

Thank you!

More soon....

Eric
0
 
LVL 3

Author Comment

by:Eric Bourland
ID: 33739140
I made a couple of adjustments in the Subtopics data table and the subtopics are showing up now. This is progress! More in a while. I am working on next steps. Thank you again. EB
0
 
LVL 3

Author Comment

by:Eric Bourland
ID: 33740189
gdemaria,
Hmmm. I think I have found a roadblock with the TwoSelectsRelated.cfm template.

The problem is, CEP staff will want to select multiple topics for a document. And also multiple subtopics.

The TwoSelectsRelated.cfm template allows selection of only one Topic.

So I modified the TwoSelectsRelated.cfm template to allow multiple selections of Topic and Subtopic; I added the multiple="yes" attribute to the SELECT tag in TwoSelectsRelated.cfm.

Testing that, I see that I can select multiple topics. That's good. But, subtopics show for only one of the selected topics. Subtopics display only for the top selected topic.

Darn.

I need to re-think this. At this point the TwoSelectsRelated.cfm template will not work for this task. It's a really useful custom tag. Do you think it is possible to customize the TwoSelectsRelated.cfm template to display multiple topics and multiple subtopics? Or should I go about this task in another way?
Thank you as always for your help. I hope your evening is going well.
Eric
0
 
LVL 3

Author Comment

by:Eric Bourland
ID: 33740771
I have been thinking about this further. I think I led us down the wrong path.

In the insert_update.cfm page, I don't think we need to make selection of subtopic contingent on selection of topic.

By which I mean, I can simply create two SELECT menus: one for Topics (which is already there); another for Subtopics.

And CEP staff can just select multiple Topics and Subtopics for each document, using the two separate SELECT menus. That will be OK.

Documents will be assigned to Topics and Subtopics, and Subtopics do not have to be related to Topics.

The problem is, the navigation menu. I need to display an orderly list of topics, and, underneath the topics, their respective subtopics.

So the Navigation menu will look like:

Home / What's New
Common Core Standards
Federal Education Programs
          Congressional Testimony
          Economic Stimulus Package/ARRA
          IDEA/Special Education
          No Child Left Behind
          NCLB School Improvement
          Rethinking the Federal Role
          Studies of the Impact of Accountability
High School Exit Examinations
          Annual Reports & State Profiles
          Policy Briefs
          Special Reports & Articles
Public School Facts, History, and Issues
          Democracy & Public Schools
          Dropouts
          Education & Jobs
          Facts About Public Education
          International Studies
          Private School Issues
          Public Engagement
          Testing
          Violence & Crime
          Virtual Schools
State Testing Data and Student Achievement
          Reports on Trends in State Tests
          State Profiles & Data
Miscellaneous Archive
          Improving America's Schools Act
          Presidential Briefs


Given the current table structure, is there a way to create this navigation menu?

Tables:
1. documents: tbl_CEP_Documents
DocumentID (pk)
DocumentTitle, DocumentAuthor, etc

2. topics: tbl_CEP_Document_Topic
DocumentTopicID (pk)
DocumentTopic, DocumentTopicDescription

3. subtopics: tbl_CEP_Document_Subtopic
DocumentSubTopicID (pk)
DocumentSubTopicTitle, DocumentSubTopicDescription
DocumentTopicID (foreign key from tbl_CEP_Document_Topic)

4. and, a table to relate documents to topics and subtopics:
tbl_Document_Has_Topic, which has three foreign keys from the tables above:
DocumentID
DocumentTopicID
DocumentSubTopicID

The query that builds the navigation menu would need to loop through the Topics and Subtopics, and group by Topic, Subtopic?

I hope this a) makes sense and b) is easier than editing the TwoSelectsRelated.cfm template.

What do you think?

Eric
0
 
LVL 39

Expert Comment

by:gdemaria
ID: 33740862
> Documents will be assigned to Topics and Subtopics, and Subtopics do not have to be related to Topics.

> The problem is, the navigation menu. I need to display an orderly list of topics, and, underneath the topics, their respective subtopics.


So you want to allow them to select subtopics without any association to topics, but then the subtopics will be associated to topics on the navigation menu?

It's a bit odd to have them not have subtopics listed within their topics, it seems to make it more unclear where the document will end up.  

> The query that builds the navigation menu would need to loop through the Topics and Subtopics, and group by Topic, Subtopic?

You already have this query, its the one used by the custom tag..  It's just a query you can use it for navigation or whatever you like..


0
 
LVL 3

Author Comment

by:Eric Bourland
ID: 33740883
>>>So you want to allow them to select subtopics without any association to topics, but then the subtopics will be associated to topics on the navigation menu?

Yes. Exactly. This will work for them.

>>It's a bit odd to have them not have subtopics listed within their topics, it seems to make it more unclear where the document will end up

It is odd. I agree. But the document will end up (I hope) under the selected topics and subtopics.

If they want a document to appear under topic "Public School Facts, History, and Issues", and subtopic "Facts About Public Education", then they can select both of those manually in the two select menus. I think that will work OK. Do you foresee any problem with that?

>>>You already have this query, its the one used by the custom tag

Of course. =) I will try this and get back to you. Thank you again.

Eric
0
 
LVL 39

Expert Comment

by:gdemaria
ID: 33740891
How about one select box that shows both topics and their subtopics?

Picture this is a single select tag...
You can seperate the topics from subtopics by making the topics have a colored background.
The can select as many as they want in a single multi-click select tag.

Home / What's New
Common Core Standards
Federal Education Programs
          Congressional Testimony
          Economic Stimulus Package/ARRA
          IDEA/Special Education
          No Child Left Behind
          NCLB School Improvement
          Rethinking the Federal Role
          Studies of the Impact of Accountability
High School Exit Examinations
          Annual Reports & State Profiles
          Policy Briefs
          Special Reports & Articles
Public School Facts, History, and Issues
          Democracy & Public Schools
          Dropouts
          Education & Jobs
          Facts About Public Education


0
 
LVL 3

Author Comment

by:Eric Bourland
ID: 33740903
That would be ideal. And I could use the same query to dispaly the topics and subtopics in this SELECT menu:

<cfquery datasource="#ds#" name="getTopics">
           SELECT tp.DocumentTopicID
                  , tp.DocumentTopic
                  , sub.DocumentSubTopicID
                  , sub.DocumentSubTopicTitle
           FROM tbl_CEP_Document_Topic tp
           LEFT JOIN tbl_CEP_Document_Subtopic sub
           ON sub.DocumentTopicID = tp.DocumentTopicID
           ORDER BY tp.DocumentTopic, sub.DocumentSubTopicTitle
</cfquery>

I will work on this. This is a good idea.
0
 
LVL 39

Expert Comment

by:gdemaria
ID: 33740909
when you loop through the query to display the select options, just color the background of the topics

<option style="background-color:silver">  (whatever color is right for you..)

and keep the subtopic color white...
0
 
LVL 3

Author Comment

by:Eric Bourland
ID: 33741464
(I should know this. Ugh.)

I am working first on the navigation menu. I just want to get that looking right. Then I will finish the SELECT menu in the insert_update.cfm page.

I am trying to get the Navigation menu items to display in the order I mentioned above:

Topic
----Subtopic
----Subtopic
----Subtopic

Topic
----Subtopic
----Subtopic
----Subtopic

...etc.


Currently the menu displays all of the Topics, and only the first Subtopic under each topic -- in correct order however.

I have tried different ideas to loop through all of the subtopics under each Topic. What am I missing? When I learn this I am going to tattoo it somewhere. =) Thank you. Have a good evening.

Eric
Query:



<!--- query Get_CEP_Topics_List_for_Navigation: this query selects columns from table tbl_CEP_Document_Topic to create a list of CEP topics for the lefthand navigation menu; tp.DocumentTopicID 24 is omitted by design --->



<cfquery datasource="#ds#" name="Get_CEP_Topics_List_for_Navigation">

           SELECT tp.DocumentTopicID

                  , tp.DocumentTopic

                  , sub.DocumentSubTopicID

                  , sub.DocumentSubTopicTitle

           FROM tbl_CEP_Document_Topic tp

           LEFT JOIN tbl_CEP_Document_Subtopic sub

           ON sub.DocumentTopicID = tp.DocumentTopicID

           WHERE tp.DocumentTopicID <> <cfqueryparam value="24" cfsqltype="cf_sql_integer">

           ORDER BY tp.DocumentTopic, sub.DocumentSubTopicTitle

</cfquery>





code for left navigation:



<cfoutput query="Get_CEP_Topics_List_for_Navigation" group="DocumentTopic">

    

    <div class="LeftNav">

<a href="/cep/index.cfm?DocumentTopicID=#Get_CEP_Topics_List_for_Navigation.DocumentTopicID#">#Get_CEP_Topics_List_for_Navigation.DocumentTopic#</a>

    </div>



 <cfif DocumentSubTopicID is not "">



 <div class="SubLeftNav">

<a href="/cep/index.cfm?DocumentSubTopicID=#Get_CEP_Topics_List_for_Navigation.DocumentSubTopicID#">#Get_CEP_Topics_List_for_Navigation.DocumentSubTopicTitle#</a>

</div>



</cfif>



	</cfoutput>

Open in new window

0
 
LVL 39

Assisted Solution

by:gdemaria
gdemaria earned 500 total points
ID: 33743656
when you use the cfouput group attribute, you need to add a 2nd set of cfoutput tags to loop around the grouped code

<cfoutput query="Get_CEP_Topics_List_for_Navigation" group="DocumentTopic">
    <div class="LeftNav">
    <a href="/cep/index.cfm?DocumentTopicID=#Get_CEP_Topics_List_for_Navigation.DocumentTopicID#">#Get_CEP_Topics_List_for_Navigation.DocumentTopic#</a>
    </div>

  <cfoutput>
  <cfif DocumentSubTopicID is not "">
    <div class="SubLeftNav">
    <a href="/cep/index.cfm?DocumentSubTopicID=#Get_CEP_Topics_List_for_Navigation.DocumentSubTopicID#">#Get_CEP_Topics_List_for_Navigation.DocumentSubTopicTitle#</a>
    </div>
  </cfif>
  </cfoutput>
</cfoutput>
0
 
LVL 3

Author Comment

by:Eric Bourland
ID: 33745096
>>>when you use the cfouput group attribute, you need to add a 2nd set of cfoutput tags to loop around the grouped code

That is working. =) That makes sense.

Now I am trying to get DocumentTopicTitles, DocumentSubTopicTitles, and Documents and Files to display.

I have been using two queries to get DocumentTopicTitles and Documents / Files to display. I bet there is a more efficient way to do this. And I want DocumentSubTopicTitles to appear too, underneath DocumentTopicTitles.

That is what I am working on now. More soon. Thank you. Hope your day is going well. EB
0
 
LVL 39

Expert Comment

by:gdemaria
ID: 33745144
> Now I am trying to get DocumentTopicTitles, DocumentSubTopicTitles, and Documents and Files to display.


I'm having trouble picturing this step.

Is this the input form where someone is entering their document and have to choose their topics and subtopics?

Or is this a listing of documents once a person has chosen a particular topic?

..
0
 
LVL 3

Author Comment

by:Eric Bourland
ID: 33745316
>>>Or is this a listing of documents once a person has chosen a particular topic?

It is this. When a web user views a Topics or a Subtopics page. This is what she sees:

Topic Title

<cfif DocumentsubtopicID is not "">Sub Topic Title</cfif>

List of document titles plus descriptions, author, publication date, download link.
0
 
LVL 3

Author Comment

by:Eric Bourland
ID: 33747290
Topics and Subtopics now appear correctly in left navigation. =)

The next task I am trying to do is display the actual pages for Subtopics. I mean, when a visitor clicks on a Subtopic in menu navigation, she will go to a page that contains documents assigned to that subtopic.

Till now, I have displayed DocumentTopics, and then documents and files, with two queries:

1) a query that displayed Topic titles at tops of pages; for example, http://ebwebwork.com/cep/index.cfm?DocumentTopicID=6 displays DocumentTopicID=6, which is Federal Education Programs.

2. another query that displayed documents and files that belong to the Topic

I wonder if I can

1) combine the queries, so that ColdFusion needs to do less work (I'll work on this later; I think it is possible, but might be simpler to keep the two queries?)
2) make Subtopic titles and documents appear on Subtopic pages

I'm working on the second task: to make Subtopic titles and documents appear on Subtopic pages.

<!--- query getTitles_for_Topics_Subtopics_Pages --->
<!--- the purpose is to display CEP Topic and Subtopic Titles at top of each CEP Topic and Subtopic page --->
<cfquery name="getTitles_for_Topics_Subtopics_Pages" datasource="#ds#">
           SELECT tp.DocumentTopicID
                  , tp.DocumentTopic
                  , tp.DocumentTopicDescription
                  , sub.DocumentSubTopicID
                  , sub.DocumentSubTopicTitle
                  , sub.DocumentSubTopicDescription
           FROM tbl_CEP_Document_Topic tp
           LEFT JOIN tbl_CEP_Document_Subtopic sub
           ON sub.DocumentTopicID = tp.DocumentTopicID
           WHERE   tp.DocumentTopicID = <cfqueryparam value="#url.DocumentTopicID#" cfsqltype="cf_sql_integer">
           <cfif sub.DocumentSubTopicID is not "">
           AND sub.DocumentSubTopicID = <cfqueryparam value="#url.DocumentSubTopicID#" cfsqltype="cf_sql_integer">
           </cfif>
           ORDER BY tp.DocumentTopic, sub.DocumentSubTopicTitle
</cfquery>

... this does not quite work.

I get an error: Element DOCUMENTSUBTOPICID is undefined in SUB.  
 
The error occurred in C:\websites\ebwebwork.com\cep\index.cfm: line 41
 
39 :            WHERE   tp.DocumentTopicID = <cfqueryparam value="#url.DocumentTopicID#" cfsqltype="cf_sql_integer">
40 :                AND tp.DocumentTopicID = <cfqueryparam value="#url.DocumentTopicID#" cfsqltype="cf_sql_integer">
41 :            <cfif sub.DocumentSubTopicID is not "">
42 :            AND sub.DocumentSubTopicID = <cfqueryparam value="#url.DocumentSubTopicID#" cfsqltype="cf_sql_integer">
43 :            </cfif>

 
I am puzzled about this error. I thought we defined sub.DocumentSubTopicID because we selected it in the query "getTitles_for_Topics_Subtopics_Pages".

But clearly I need to define sub.DocumentSubTopicID in some other way?

Thanks again for your time and advice.

Eric
0
 
LVL 39

Expert Comment

by:gdemaria
ID: 33747657


 You accidently put the query's sub alias into your CFIF statement

  <cfif sub.DocumentSubTopicID is not "">


Should that be form.DocumentSubTopicID ?
0
 
LVL 3

Author Comment

by:Eric Bourland
ID: 33748223
I have added confusion. dang. I will try to clarify.

>>>Should that be form.DocumentSubTopicID ?

I don't think it should be, because this is not the insert_update.cfm template. (Sorry to be confusing.)

I am working on the display page.

For now I just want to display CEP Topic and Subtopic Titles at the top of each CEP Topic and Subtopic page. For now I do not want to edit or update. I will work next on editing / updating the CEP Topics and Subtopics (using your notes and our work, above).

So the form scope is not appropriate here, since there is no form. Am I correct?

I just want to display CEP Topic and Subtopic Titles at top of each CEP Topic and Subtopic page.

An example is here:

http://ebwebwork.com/cep/index.cfm?DocumentTopicID=6

That is the Federal Education Programs topic page. On that page you see the topic title: Federal Education Programs, and documents assigned to topic Federal Education Programs.

My task / problem: I am trying to get each Subtopic to appear on its own page. So, for example, http://ebwebwork.com/cep/index.cfm?DocumentSubTopicID=1 should show a page only for DocumentSubTopicID=1, which happens to be Congressional Testimony.

Right now, if I go to  http://ebwebwork.com/cep/index.cfm?DocumentSubTopicID=1, then I see only the default page -- the home or front page.

I hope I am making more sense?
<!--- query getTitles_for_TopicsPages --->

<!--- the purpose is to display CEP Topic and Subtopic Titles at top of each CEP Topic and Subtopic page --->



<cfquery name="getTitles_for_TopicsPages" datasource="#ds#">

           SELECT tp.DocumentTopicID

                  , tp.DocumentTopic

                  , tp.DocumentTopicDescription

                  , sub.DocumentSubTopicID

                  , sub.DocumentSubTopicTitle

                  , sub.DocumentSubTopicDescription

           FROM tbl_CEP_Document_Topic tp

           LEFT JOIN tbl_CEP_Document_Subtopic sub

           ON sub.DocumentTopicID = tp.DocumentTopicID

           WHERE   tp.DocumentTopicID = <cfqueryparam value="#url.DocumentTopicID#" cfsqltype="cf_sql_integer">

           ORDER BY tp.DocumentTopic, sub.DocumentSubTopicTitle

</cfquery>





index.cfm:



<!--- query getTitles_for_TopicsPages; list DocumentTopic at top of each topic page --->

<!--- if DocumentTopicID IS 24 (home page), then omit DocumentTopic (title)! --->

<!--- why? it's complicated --->



<cfif DocumentTopicID IS 24>



<cfoutput query="getTitles_for_TopicsPages" group="DocumentTopic" groupcasesensitive="no">



<div>

#DocumentTopicDescription#

</div>



</cfoutput>



<cfelseif val(url.DocumentTopicID)>



<!--- output DocumentTopics, each topic on its own page WITHOUT subtopics --->



<cfoutput query="getTitles_for_TopicsPages" group="DocumentTopic" groupcasesensitive="no">

<h1>#DocumentTopic#</h1>



<div>

#DocumentTopicDescription#

</div>



</cfoutput>



<cfelseif val(url.DocumentSubTopicID)>



<!--- output DocumentSubTopics, each subtopic on its own page WITHOUT topics --->

 <cfoutput>



<h1>#DocumentSubTopicTitle#</h1>



<div>

#DocumentSubTopicDescription#

</div>



  </cfoutput>

</cfif>

Open in new window

0
 
LVL 3

Author Comment

by:Eric Bourland
ID: 33748292
The display page is index.cfm. (I am trying hard to clarify everything.)
0
 
LVL 39

Expert Comment

by:gdemaria
ID: 33748482

This is wrong because you are using your database table's alias in your coldfusion CFIF statement

 <cfif sub.DocumentSubTopicID is not "">


"Sub" comes from here...

FROM tbl_CEP_Document_Topic tp
    LEFT JOIN tbl_CEP_Document_Subtopic sub
                                                                  ^^^^

You can't do that, it's not a valid CF scope and you didn't create a CF structure called SUB.


So, what scope do you want to put on that variable in the CFIF statement?

Where is the value of DocumentSubTopicID coming from ?

<cfif ????.DocumentSubTopicID is not "">

0
 
LVL 3

Author Comment

by:Eric Bourland
ID: 33748986
I understand. I resolved the error,  Element DOCUMENTSUBTOPICID is undefined in SUB. I made a new query, getTitles_for_TopicsPages (see below).

* Topic pages display correctly. Example: http://ebwebwork.com/cep/index.cfm?DocumentTopicID=6

* A problem remains with Subtopic pages.

When I view a subtopic page, for example: http://ebwebwork.com/cep/index.cfm?DocumentSubTopicID=3

...I do not see the Subtopic title or content; instead I see the content for the default page which is the front page.

I think this is because the default DocumentTopicID is 24 -- which is the front page. That is, the front page is its own category, and CEP staff can choose to assign documents to the front page. I set the default DocumentTopicID = 24 at the top of the page, using cfparam:

<!--- Set default DocumentTopicID = 24; this is the front page! --->
<!--- a default DocumentTopicID should always be defined --->
<cfparam name="url.DocumentTopicID" default="24" />

But I am wondering why this default DocumentTopicID content shows up in my Document Subtopic pages?

Did I form my query getTitles_for_TopicsPages correctly?

query, getTitles_for_TopicsPages:



<!--- query getTitles_for_TopicsPages --->

<!--- the purpose is to display CEP Topic and Subtopic Titles at top of each CEP Topic and Subtopic page --->



<cfquery name="getTitles_for_TopicsPages" datasource="#ds#">

           SELECT tp.DocumentTopicID

                  , tp.DocumentTopic

                  , tp.DocumentTopicDescription

                  , sub.DocumentSubTopicID

                  , sub.DocumentSubTopicTitle

                  , sub.DocumentSubTopicDescription

           FROM tbl_CEP_Document_Topic tp

           LEFT JOIN tbl_CEP_Document_Subtopic sub

           ON sub.DocumentTopicID = tp.DocumentTopicID

           

           <cfif val(url.DocumentTopicID)>

           WHERE   tp.DocumentTopicID = <cfqueryparam value="#url.DocumentTopicID#" cfsqltype="cf_sql_integer">

           

           <cfelseif val(url.DocumentSubTopicID)>

           OR sub.DocumentSubTopicID = <cfqueryparam value="#url.DocumentSubTopicID#" cfsqltype="cf_sql_integer">

           </cfif>

           

           ORDER BY tp.DocumentTopic, sub.DocumentSubTopicTitle

</cfquery>





index.cfm; cfif and cfoutputs to view DocumentTopic or DocumentSubtopics:





<!--- query getTitles_for_TopicsPages; list DocumentTopic, or DocumentSubTopicTitle, at top of each topic or subtopic page --->



<!--- if DocumentTopicID IS 24 (home page), then omit DocumentTopic (title)! --->

<!--- why? it's complicated ... page formatting, per client's request --->



<cfif DocumentTopicID IS 24>



<cfoutput query="getTitles_for_TopicsPages" group="DocumentTopic" groupcasesensitive="no">



<div>

#DocumentTopicDescription#

</div>



</cfoutput>



<cfelseif val(url.DocumentTopicID)>



<!--- output DocumentTopics, each topic on its own page WITHOUT subtopics --->



<cfoutput query="getTitles_for_TopicsPages" group="DocumentTopic" groupcasesensitive="no">

<h1>#DocumentTopic#</h1>



<div>

#DocumentTopicDescription#

</div>



</cfoutput>



<cfelseif val(url.DocumentSubTopicID)>



<!--- output DocumentSubTopics, each subtopic on its own page WITHOUT topics --->

 <cfoutput group="DocumentSubTopicTitle" groupcasesensitive="no">



<h1>#DocumentSubTopicTitle#</h1>



<div>

#DocumentSubTopicDescription#

</div>



  </cfoutput>

</cfif>

Open in new window

0
 
LVL 39

Expert Comment

by:gdemaria
ID: 33750663
> But I am wondering why this default DocumentTopicID content shows up in my Document Subtopic pages?


Because every subtopic is associated with a subtopic.

You pulled up the topic based on the subtopic ID


I don't really understand the CFIF in the query.  If you don't supply the TopicID and only provide the SubTopicID then the query resolves to this...

FROM tbl_CEP_Document_Topic tp
  LEFT JOIN tbl_CEP_Document_Subtopic sub
     ON sub.DocumentTopicID = tp.DocumentTopicID
     OR sub.DocumentSubTopicID =  <cfqueryparam value="#url.DocumentSubTopicID#" cfsqltype="cf_sql_integer">        
 ORDER BY tp.DocumentTopic, sub.DocumentSubTopicTitle

There is an "OR" condition part of a left join and no where clause..

That couldn't return the value you want... ?


0
 
LVL 3

Author Comment

by:Eric Bourland
ID: 33750737
First of all, I really appreciate your patience.

>>>That couldn't return the value you want... ?

True. It could not. I will try to explain what I was thinking. You might laugh.

This is the original query, which gives me the error: "Element DOCUMENTSUBTOPICID is undefined in URL."

<cfquery name="getTitles_for_TopicsPages" datasource="#ds#">
           SELECT tp.DocumentTopicID
                  , tp.DocumentTopic
                  , tp.DocumentTopicDescription
                  , sub.DocumentSubTopicID
                  , sub.DocumentSubTopicTitle
                  , sub.DocumentSubTopicDescription
           FROM tbl_CEP_Document_Topic tp
           LEFT JOIN tbl_CEP_Document_Subtopic sub
           ON sub.DocumentTopicID = tp.DocumentTopicID
           WHERE   tp.DocumentTopicID = <cfqueryparam value="#url.DocumentTopicID#" cfsqltype="cf_sql_integer">
           AND sub.DocumentSubTopicID = <cfqueryparam value="#url.DocumentSubTopicID#" cfsqltype="cf_sql_integer">
           ORDER BY tp.DocumentTopic, sub.DocumentSubTopicTitle
</cfquery>


So, I thought, I will set up a CFIF statement. If DocumentTopicID is defined in scope URL, then process the statement: WHERE   tp.DocumentTopicID = <cfqueryparam value="#url.DocumentTopicID#" cfsqltype="cf_sql_integer">

If DocumentSubTopicID is defined in scope URL, then process this statement:

WHERE sub.DocumentSubTopicID = <cfqueryparam value="#url.DocumentSubTopicID#" cfsqltype="cf_sql_integer">

(sorry, it should be WHERE, not OR!)

My query should be:
<!--- query getTitles_for_TopicsPages; list DocumentTopic, or DocumentSubTopicTitle, at top of each topic or subtopic page --->

<cfquery name="getTitles_for_TopicsPages" datasource="#ds#">
           SELECT tp.DocumentTopicID
                  , tp.DocumentTopic
                  , tp.DocumentTopicDescription
                  , sub.DocumentSubTopicID
                  , sub.DocumentSubTopicTitle
                  , sub.DocumentSubTopicDescription
           FROM tbl_CEP_Document_Topic tp
           LEFT JOIN tbl_CEP_Document_Subtopic sub
           ON sub.DocumentTopicID = tp.DocumentTopicID
           
           <cfif val(url.DocumentTopicID)>
           WHERE tp.DocumentTopicID = <cfqueryparam value="#url.DocumentTopicID#" cfsqltype="cf_sql_integer">
           
           <cfelseif val(url.DocumentSubTopicID)>
           WHERE sub.DocumentSubTopicID = <cfqueryparam value="#url.DocumentSubTopicID#" cfsqltype="cf_sql_integer">
           </cfif>
</cfquery>

WHERE, not OR <---- sorry; I was not thinking clearly

... except, as noted, when I try to view a subtopics page, http://ebwebwork.com/cep/index.cfm?DocumentSubTopicID=3

... I see the content for the default page which is the front page.

>>>>Because every subtopic is associated with a subtopic.

>>>>You pulled up the topic based on the subtopic ID

Hmmm. This part I do not understand. (Sorry. Again. I feel extra dense today.) I think I correctly solved the "Element DOCUMENTSUBTOPICID is undefined in URL." error using the revised CFIF statement above (?). I am not sure how I pull up a topic based on a SubtopicID?

Eric
0
 
LVL 39

Expert Comment

by:gdemaria
ID: 33750764
Here's an example of code that could do what the above code block is trying to do.

If you pass in the Topic ID, it will show you the topic name and description

If you pass in the Subtopic ID, it will show you the sub topic name and description.

I assume the next step would be.. below this block of code, you would just need to list the documents associated with the topic or subtopic ID
<cfparam name="url.DocumentTopicID" default="">

<cfparam name="url.DocumentSubTopicID" default="">



<cfquery name="getTitles_for_TopicsPages" datasource="#ds#">

   SELECT   tp.DocumentTopicID

          , tp.DocumentTopic

          , tp.DocumentTopicDescription

          , sub.DocumentSubTopicID

          , sub.DocumentSubTopicTitle

          , sub.DocumentSubTopicDescription

   FROM tbl_CEP_Document_Topic tp

    LEFT JOIN tbl_CEP_Document_Subtopic sub ON sub.DocumentTopicID = tp.DocumentTopicID

   <cfif val(url.DocumentSubTopicID)>

     WHERE sub.DocumentSubTopicID = <cfqueryparam value="#url.DocumentSubTopicID#" cfsqltype="cf_sql_integer">

   <cfelseif val(url.DocumentTopicID)>

     WHERE tp.DocumentTopicID = <cfqueryparam value="#url.DocumentTopicID#" cfsqltype="cf_sql_integer">

   <cfelse>

     WHERE 1=2 <!---- nothing passed, return no records ---->

   </cfif>

   ORDER BY tp.DocumentTopic, sub.DocumentSubTopicTitle

</cfquery>



<cfoutput>

  <cfif val(url.DocumentSubTopicID)>

    <h1>#DocumentTopic# &gt; #DocumentSubTopicTitle#</h1>

    <div>

     #DocumentSubTopicDescription#

    </div>

  <cfelse>

    <cfif DocumentTopicID neq 24>

    <h1>#DocumentTopic#</h1>

    </cfif>

    <div>

     #DocumentTopicDescription#

    </div>

  </cfif>

</cfoutput>

Open in new window

0
 
LVL 39

Expert Comment

by:gdemaria
ID: 33750777
> I  think I correctly solved the "Element DOCUMENTSUBTOPICID is undefined in URL." error using the revised CFIF statement above

I think the error was only masked for the moment.  If you pass in the topic ID, then the <CFELSE IF ... > is never reached so the  URL. subtopic ID is never tested.   Try NOT passing the Topic ID to that code and you will get the error again.

You need to add the cfparam to ensure the URL variables exist even if not passed in.   That's what I did at the start of my code block above.
0
 
LVL 3

Author Comment

by:Eric Bourland
ID: 33750906
You have a very cool way of interpreting my ColdFusion problems. I am studying this.
0
 
LVL 3

Author Comment

by:Eric Bourland
ID: 33752842
It's working very well.

I understand that I needed to set two parameters:

<cfparam name="url.DocumentTopicID" default="">
<cfparam name="url.DocumentSubTopicID" default="">

I understand the rest of the query. This part is very interesting:

WHERE 1=2

Obviously 1 can never equal 2, so that WHERE clause means that, if there is no value to DocumentTopicID or DocumentSubtopicID, then no records will be selected from the database table. Which is as it should be.

Neat.

I am working next to properly display documents and files download links under appropriate topics and subtopics. I will work on that for a while on my own. More later. Good morning!

Eric
0
 
LVL 3

Author Comment

by:Eric Bourland
ID: 33752956
I believe this query is working properly to display documents for Topics or for SubTopics:

<!--- query getDocumentsandFiles: this query selects columns from tables tbl_CEP_Documents D, tbl_Document_Has_Topic H, tbl_CEP_files F --->
<!--- the purpose is to display a list of CEP DocumentTitles plus Author, Abstract, Publication Date; and CEP Files associated with those DocumentTitles; and download links to the files --->

<cfquery name="getDocumentsandFiles" datasource="#ds#">
SELECT D.DocumentID
         , D.DocumentTitle
     , D.DocumentType
     , D.DocumentAuthor
     , D.DocumentAbstract
     , D.DocumentKeyword
     , D.DocumentImage
     , D.DocumentPublicationDate
     , H.DocumentID
     , H.DocumentTopicID
     , H.DocumentSubtopicID
     , F.DocumentID
     , F.FileID
     , F.FileName
     , F.FileLinkText
     , F.FileExtension
     , F.FileType
     , F.FileSize
FROM tbl_CEP_Documents D
INNER JOIN tbl_Document_Has_Topic H
ON D.DocumentID = H.DocumentID
<cfif val(url.DocumentSubTopicID)>
AND H.DocumentSubTopicID = <cfqueryparam value="#url.DocumentTopicID#" cfsqltype="cf_sql_integer">
<cfelseif val(url.DocumentTopicID)>
AND H.DocumentTopicID = <cfqueryparam value="#url.DocumentTopicID#" cfsqltype="cf_sql_integer">
</cfif>
LEFT OUTER JOIN tbl_CEP_files F
ON D.DocumentID = F.DocumentID
ORDER BY #sortBy#
</cfquery>

Next, I am working on the SELECT menu in the insert_update.cfm page.
0
 
LVL 3

Author Comment

by:Eric Bourland
ID: 33758013
>>>I am working on the SELECT menu in the insert_update.cfm page.

I have been staring at this for a few hours trying different ideas. The problem (I think) is the ValueList function. I am trying to get a list of Topics and SubTopics to appear in the SELECT list.

I've been working on this ValueList to populate the SELECT list:

       <!--- convert the DocumentTopicID, DocumentSubTopicID values to a list --->
      <cfset form.SelectDocumentTopics = ValueList(GetSelectedTopics.DocumentTopicID,GetSelectedTopics.DocumentSubTopicID)>


However, the output is an ungrouped list of DocumentTopics, with no SubTopics.

I've reached a point at which it's very inefficient to keep staring at the code and at previous examples. =) What am I missing?

Hope you are well.

Eric
<!--- query Get_Document_Topics_Subtopics requests values from columns in tbl_CEP_Document_Topic and tbl_CEP_Document_Subtopic, for use later in the insert_update.cfm template --->



<cfquery datasource="#ds#" name="Get_Document_Topics_Subtopics">

           SELECT tp.DocumentTopicID

                  , tp.DocumentTopic

                  , sub.DocumentSubTopicID

                  , sub.DocumentSubTopicTitle

           FROM tbl_CEP_Document_Topic tp

           LEFT JOIN tbl_CEP_Document_Subtopic sub

           ON sub.DocumentTopicID = tp.DocumentTopicID

           ORDER BY tp.DocumentTopic, sub.DocumentSubTopicTitle

</cfquery>

 

  <tr>

  <td>

    <p><strong>Assign this Document to CEP Topics</strong> <a href="#" class="hintanchor" onMouseover="showhint('Assign this document to a topic', this, event, '200px')">[?]</a></p>

    

    

      <p class="small">Choose at least one topic. To select more than one topic, hold the Ctrl or the Shift key (on your keyboard) and select topics with the mouse cursor.</p>





   

<!--- this query requests existing topics and subtopics for the current documentID --->

       <cfquery datasource="#ds#" name="GetSelectedTopics">

              SELECT DocumentTopicID, DocumentSubTopicID

              FROM    tbl_Document_Has_Topic

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

       </cfquery>

     

       <!--- convert the DocumentTopicID, DocumentSubTopicID values to a list --->

      <cfset form.SelectDocumentTopics = ValueList(GetSelectedTopics.DocumentTopicID,GetSelectedTopics.DocumentSubTopicID)>



<cfselect name="SelectDocumentTopics"

size="15"

multiple="yes"

query="Get_Document_Topics_Subtopics"

queryPosition="below"

value="DocumentTopicID"

display="DocumentTopic"

selected="#form.SelectDocumentTopics#">

<option value="0"> 

</cfselect>

Open in new window

0
 
LVL 3

Author Comment

by:Eric Bourland
ID: 33767002
gdemaria,

I've worked on this over the weekend. I am still trying to get an orderly list of Topics and SubTopics to appear in the SELECT list.

I am wondering two things:

     <!--- convert the DocumentTopicID, DocumentSubTopicID values to a list --->
      <cfset form.SelectDocumentTopics = ValueList(GetSelectedTopics.DocumentTopicID,GetSelectedTopics.DocumentSubTopicID)>

1. Is it valid to palce two comma-delimited values in the ValueList function, as above?

<cfselect name="SelectDocumentTopics"
size="15"
multiple="yes"
query="Get_Document_Topics_Subtopics"
queryPosition="below"
value="DocumentTopicID"
display="DocumentTopic"
selected="#form.SelectDocumentTopics#">
<option value="0">  
</cfselect>

2. Since the display attribute of the CFSELECT tag allows me to specify only one column in one table, is there a way to set a value that represents both Topics and Subtopics, and put that value as the value for the display tag?

I'm trying different angles to make this work. =) Thanks for any advice. I hope your weekend has been relaxing, and ColdFusion-free.

Eric
0
 
LVL 39

Expert Comment

by:gdemaria
ID: 33767043
Hey Eric,
Sorry I didn't see the previous post's email notification.

First, you have to get rid of CFSELECT and just use <select> and then loop the <options> with CFLOOP or CFOUTPUT query="xxx"

The value for your option tag needs to be a concatination of topic and subtopic..

value="#GetSelectedTopics.DocumentTopicID#:#GetSelectedTopics.DocumentSubTopicID#"

So if you look at the HTML source you will see..

 value="123:"   ---- topic, no subtopic
 value="123:44"  --- subtopic
 value="123:52"
 value="222:"     ---- topic, no subtopic
 value="222:11"


0
 
LVL 3

Author Comment

by:Eric Bourland
ID: 33767084
Brilliant. I am trying this right now. Sweet!
0
 
LVL 3

Author Comment

by:Eric Bourland
ID: 33767569
Here is my first shot at implementing your idea. I've tried different ideas, and the best one I came up with is below. I think I followed your instructions. But no topics or subtopics appear. What did I do wrong?

Thank you again. Have a good evening.

Eric
<!--- query Get_Document_Topics_Subtopics requests values from columns in tbl_CEP_Document_Topic and tbl_CEP_Document_Subtopic, for use later in the insert_update.cfm template --->



<cfquery datasource="#ds#" name="Get_Document_Topics_Subtopics">

           SELECT tp.DocumentTopicID

                  , tp.DocumentTopic

                  , sub.DocumentSubTopicID

                  , sub.DocumentSubTopicTitle

           FROM tbl_CEP_Document_Topic tp

           LEFT JOIN tbl_CEP_Document_Subtopic sub

           ON sub.DocumentTopicID = tp.DocumentTopicID

           ORDER BY tp.DocumentTopic, sub.DocumentSubTopicTitle

</cfquery>

 

  <tr>

  <td>

    <p><strong>Assign this Document to CEP Topics</strong> <a href="#" class="hintanchor" onMouseover="showhint('Assign this document to a topic', this, event, '200px')">[?]</a></p>

    

    

      <p class="small">Choose at least one topic. To select more than one topic, hold the Ctrl or the Shift key (on your keyboard) and select topics with the mouse cursor.</p>





   

<!--- this query requests existing topics and subtopics for the current documentID --->

       <cfquery datasource="#ds#" name="GetSelectedTopics">

              SELECT DocumentTopicID, DocumentSubTopicID

              FROM    tbl_Document_Has_Topic

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

       </cfquery>

     

<!--- convert the DocumentTopicID, DocumentSubTopicID values to a list --->

      <cfset form.SelectDocumentTopics = ValueList(GetSelectedTopics.DocumentTopicID,GetSelectedTopics.DocumentSubTopicID)>





<!--- This select menu lists Topics and Subtopics; if topics and subtopics were already selected for form.documentID, --->

<!---  then display them in select menu through query GetSelectedTopics --->



<!--- query Get_Document_Topics_Subtopics requests for Topics and Subtopics --->



<select name="SelectDocumentTopics"

size="15"

multiple="yes"

selected="#form.SelectDocumentTopics#">

<cfoutput query="Get_Document_Topics_Subtopics">

<option value="#GetSelectedTopics.DocumentTopicID#:#GetSelectedTopics.DocumentSubTopicID#">

</cfoutput>

</select>

Open in new window

0
 
LVL 39

Expert Comment

by:gdemaria
ID: 33768795

You didn't put anything in the display part of the <option> tag, you only populated the value.   So your select right now t is probably a long empty list, right?

Now you need to show the topic/subtopic name in the option
0
 
LVL 3

Author Comment

by:Eric Bourland
ID: 33770869
That's what I get for working at 2 in the morning.

It's working better now. I'm seeing all of the Topics:Subtopics. I'm not seeing the Topics, by themselves. I have an idea to fix this.
0
 
LVL 3

Author Comment

by:Eric Bourland
ID: 33772180
Making progress.

At the moment I am stuck on something. The subtopics display perfectly. But, the discrete topics do not display. For example, instead of:

Topic
----Topic : Subtopic
----Topic : Subtopic
----Topic : Subtopic

Topic
----Topic : Subtopic
----Topic : Subtopic
----Topic : Subtopic

I get:

Topic : Subtopic
Topic : Subtopic
Topic : Subtopic
Topic : Subtopic
Topic : Subtopic
Topic : Subtopic
Topic : Subtopic

...etc

Topics that have no subtopics, such as Common Core Standards, display discretely. (I attach a screenshot.)

I have tried to use the group attribute, and also to modify the Get_Document_Topics_Subtopics query and the GetSelectedTopics query. I'm not able to get Topics to display discretely.

I wonder if I need to change the Get_Document_Topics_Subtopics query. I am looking at that, next.

What do you think I am missing? Thank you again. =) EB
<!--- query Get_Document_Topics_Subtopics requests values from columns in tbl_CEP_Document_Topic and tbl_CEP_Document_Subtopic, for use later in the insert_update.cfm template --->



<cfquery datasource="#ds#" name="Get_Document_Topics_Subtopics">

           SELECT tp.DocumentTopicID

                  , tp.DocumentTopic

                  , sub.DocumentSubTopicID

                  , sub.DocumentSubTopicTitle

           FROM tbl_CEP_Document_Topic tp

           LEFT JOIN tbl_CEP_Document_Subtopic sub

           ON sub.DocumentTopicID = tp.DocumentTopicID

           ORDER BY tp.DocumentTopic, sub.DocumentSubTopicTitle

</cfquery>

 

  <tr>

  <td>

    <p><strong>Assign this Document to CEP Topics and Subtopics</strong> <a href="#" class="hintanchor" onMouseover="showhint('Assign this document to a topic and a subtopic', this, event, '200px')">[?]</a></p>

    

    

      <p class="small">Choose at least one topic or subtopic. To select more than one topic, hold the Ctrl or the Shift key (on your keyboard) and select topics with the mouse cursor.</p>





   

<!--- this query requests existing topics and subtopics for the current documentID --->

       <cfquery datasource="#ds#" name="GetSelectedTopics">

              SELECT DocumentTopicID, DocumentSubTopicID

              FROM    tbl_Document_Has_Topic

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

       </cfquery>

     

<!--- convert the DocumentTopicID, DocumentSubTopicID values to a list --->

      <cfset form.SelectDocumentTopics = ValueList(GetSelectedTopics.DocumentTopicID,GetSelectedTopics.DocumentSubTopicID)>





<!--- This select menu lists Topics and Subtopics; if topics and subtopics were already selected for form.documentID, --->

<!---  then display them in select menu using query GetSelectedTopics --->



<!--- query Get_Document_Topics_Subtopics requests for Topics and Subtopics --->



<select name="SelectDocumentTopics"

size="15"

multiple="yes"

selected="#form.SelectDocumentTopics#">



<cfoutput query="Get_Document_Topics_Subtopics">



<option value="#GetSelectedTopics.DocumentTopicID#:#GetSelectedTopics.DocumentSubTopicID#">



#Get_Document_Topics_Subtopics.DocumentTopic# 



<cfif Get_Document_Topics_Subtopics.DocumentSubTopicTitle IS NOT "">: #Get_Document_Topics_Subtopics.DocumentSubTopicTitle#</cfif>



</cfoutput>

</select>

Open in new window

Untitled-1.gif
0
 
LVL 39

Expert Comment

by:gdemaria
ID: 33772455
There are two ways of doing this.

First, as you suggested, add a group in your cfoutput.   That will (1) always display a topic and then (2) loop your subtopics.

The other method is to create a UNION in your query so that you query TOPICs only and then subtopics in unioned query.

But the group should do it for you..
0
 
LVL 3

Author Comment

by:Eric Bourland
ID: 33772795
I tried the GROUP. When I do:

<cfoutput query="Get_Document_Topics_Subtopics" group="DocumentTopic">

I get a list of Topics ... but no subtopics.

I wonder if I need two different CFOUTPUTs and two different groups, one each for Topics and Subtopics.

I'm trying some more things....
<!--- query Get_Document_Topics_Subtopics requests values from columns in tbl_CEP_Document_Topic and tbl_CEP_Document_Subtopic, for use later in the insert_update.cfm template --->



<cfquery datasource="#ds#" name="Get_Document_Topics_Subtopics">

           SELECT tp.DocumentTopicID

                  , tp.DocumentTopic

                  , sub.DocumentSubTopicID

                  , sub.DocumentSubTopicTitle

           FROM tbl_CEP_Document_Topic tp

           LEFT JOIN tbl_CEP_Document_Subtopic sub

           ON sub.DocumentTopicID = tp.DocumentTopicID

           ORDER BY tp.DocumentTopic, sub.DocumentSubTopicTitle

</cfquery>

 

  <tr>

  <td>

    <p><strong>Assign this Document to CEP Topics and Subtopics</strong> <a href="#" class="hintanchor" onMouseover="showhint('Assign this document to a topic and a subtopic', this, event, '200px')">[?]</a></p>

    

    

      <p class="small">Choose at least one topic or subtopic. To select more than one topic, hold the Ctrl or the Shift key (on your keyboard) and select topics with the mouse cursor.</p>





   

<!--- this query requests existing topics and subtopics for the current documentID --->

       <cfquery datasource="#ds#" name="GetSelectedTopics">

              SELECT DocumentTopicID, DocumentSubTopicID

              FROM    tbl_Document_Has_Topic

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

       </cfquery>

     

<!--- convert the DocumentTopicID, DocumentSubTopicID values to a list --->

      <cfset form.SelectDocumentTopics = ValueList(GetSelectedTopics.DocumentTopicID,GetSelectedTopics.DocumentSubTopicID)>





<!--- This select menu lists Topics and Subtopics; if topics and subtopics were already selected for form.documentID, --->

<!---  then display them in select menu using query GetSelectedTopics --->



<!--- query Get_Document_Topics_Subtopics requests for Topics and Subtopics --->



<select name="SelectDocumentTopics"

size="15"

multiple="yes"

selected="#form.SelectDocumentTopics#">



<cfoutput query="Get_Document_Topics_Subtopics" group="DocumentTopic">



<option value="#GetSelectedTopics.DocumentTopicID#:#GetSelectedTopics.DocumentSubTopicID#">



#Get_Document_Topics_Subtopics.DocumentTopic# 



<cfif Get_Document_Topics_Subtopics.DocumentSubTopicTitle IS NOT "">: #Get_Document_Topics_Subtopics.DocumentSubTopicTitle#</cfif>



</cfoutput>

</select>

Open in new window

0
 
LVL 39

Expert Comment

by:gdemaria
ID: 33772822
This may sound familiar, but you have a GROUP without a second set of CFOUTPUT tags...
0
 
LVL 39

Expert Comment

by:gdemaria
ID: 33772848

<select name="SelectDocumentTopics" size="15" multiple="yes">

 <cfoutput query="Get_Document_Topics_Subtopics" group="DocumentTopic">

  <!--- show topic ---->

  <option value="#Get_Document_Topics_Subtopics.DocumentTopicID#:" style="background-color:silver;">#Get_Document_Topics_Subtopics.DocumentTopic#</option>

  <cfoutput>

    <cfif val(Get_Document_Topics_Subtopics.DocumentSubTopicID)>

    <!---- show subtopic, but only if it exists ---->

    <option value="#Get_Document_Topics_Subtopics.DocumentTopicID#:#Get_Document_Topics_Subtopics.DocumentSubTopicID#">#Get_Document_Topics_Subtopics.DocumentSubTopicTitle#</cfif>

    </cfif>

  </cfoutput>

</cfoutput>

</select>

Open in new window

0
 
LVL 3

Author Comment

by:Eric Bourland
ID: 33772914
I see now. Working on this.
0
 
LVL 3

Author Comment

by:Eric Bourland
ID: 33773631
It is working really well. Next I am working with the ValueList

<!--- convert the DocumentTopicID, DocumentSubTopicID values to a list --->
      <cfset form.SelectDocumentTopics = ValueList(GetSelectedTopics.DocumentTopicID,GetSelectedTopics.DocumentSubTopicID)>

... to get selected Topics and and Subtopics to show up.
0
 
LVL 39

Expert Comment

by:gdemaria
ID: 33773710

 Remember that the value of the select tag is  two numbers joined by ":"  as in  100:21  which is topicID:subTopicID

 That means you need to build a list of values that could match that option value exactly.   The valueList function you have isn't doing that.


 The easiest way to do this is to create a new column in your SELECT statement

 SELECT DocumentTopicID
           , DocumentSubTopicID
           , cast(DocumentTopicID as varchar) + ':' + cast(DocumentSubTopicID as varchar)  as JoinedID
...


Now using the new column called "JoinedID"  in your value list will give you the concatinated value you need to compare against the option's value.


0
 
LVL 3

Author Comment

by:Eric Bourland
ID: 33773879
That is cool.

As we go, I am trying to also think about why you recommend the solutions that you recommend. I can see how this solution will work. The concatenation of the values is an idea I would not have thought of.

I am working on this.
0
 
LVL 3

Author Comment

by:Eric Bourland
ID: 33774380
Hmmm. Well, the problem is, I cannot use the cast( function. If you remember from last April, we outlawed cast( because it leaves open a door for SQL injection.

I am 98% sure that I have used only safe, CFPARAM queries in all of my code since then.

I understand that by this:

DocumentTopicID as varchar) + ':' + cast(DocumentSubTopicID as varchar)  as JoinedID

... you simply want to create a value that combines two other values: DocumentTopicID and DocumentSubTopicID. That is exactly what I need to do.

Is there another way to do it?

Hmm. What if I manually created a new column, JoinedID, in tbl_Document_Has_Topic? And figured out some way to make that column hold a single value that combined the two values for DocumentTopicID and DocumentSubTopicID.

What is your advice?
0
 
LVL 39

Expert Comment

by:gdemaria
ID: 33775239
I think you misunderstood the cast() situation, the use of cast in SQL is completely safe.   The problem in the situation before was that a SQL statement was entered on the URL and was then forced into a SQL statement that was not protected by cfqueryparam.   That bad SQL statement happened to have a cast() statement in it.   So, we attempted to detect the attacking SQL statement by looking on the URL for the keyword cast(

People use cast() in their SQL statements all the time, there is no issue with the use as I've shown it here at all.  If you like, ask a question about this in EE so you will feel confident.
0
 
LVL 3

Author Comment

by:Eric Bourland
ID: 33776155
OK, you are talking me into it.

So, in application.cfc, it is OK for me to remove:

        <!--- if query_string contains cast(, then abort! --->                                              
    <cfif cgi.query_string contains "cast(">
      <cfabort>
    </cfif>

from the OnRequestStart function?
0
 
LVL 39

Assisted Solution

by:gdemaria
gdemaria earned 500 total points
ID: 33778435
> So, in application.cfc, it is OK for me to remove

No sir, in this case you are testing the query string (the URL) for "cast(" because sql injection attacks often use cast as part of a sql statement that they are forcing into your code via the URL.  So you are checking the URL for such a code snippet and blocking it.

  <cfif cgi.query_string contains "cast(">


In this case,  you are using cast() as a function in a SQL statement that you wrote.  

 SELECT cast(DocumentTopicID as varchar) + ':' + cast(DocumentSubTopicID as varchar)  as JoinedID


They are apples and oranges, nothing to do with each other.    cast() has not inherit problems itself, you are just detecting for abuse on the URL.  

Think of cast() like a car.    You put a police officer on the highway to catch speeding vehicles.  Now you don't want to take your car to the local grocery store because there's a cop on the highway.  :)

 

0
 
LVL 3

Author Comment

by:Eric Bourland
ID: 33783100
I get it. =) I will stop worrying.

I implemented the SELECT cast(DocumentTopicID as varchar) + ':' + cast(DocumentSubTopicID as varchar)  as JoinedID

and then I changed the ValueList() like this:

<!--- convert the DocumentTopicID, DocumentSubTopicID values to a list --->

     <cfset form.SelectDocumentTopics = ValueList(GetSelectedTopics.JoinedID)>

and then I added selected="#form.SelectDocumentTopics#" to the two OPTION tags: the OPTION for the Topics and the OPTION for the Subtopics.

It does not throw an error. But, when I load the page, all of the Topics and Subtopics are selected. Not just the Topics and Subtopics that are selected for the form.DocumentID. This is true of all documentIDs that I test.

Hmm. I am looking at why all Topics and Subtopics are selected.
<!--- this query requests existing topics and subtopics for the current documentID --->

       <cfquery datasource="#ds#" name="GetSelectedTopics">

              SELECT DocumentTopicID

              , DocumentSubTopicID

              , cast(DocumentTopicID AS varchar) + ':' + cast(DocumentSubTopicID AS varchar) AS JoinedID

              FROM    tbl_Document_Has_Topic

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

       </cfquery>

     

<!--- convert the DocumentTopicID, DocumentSubTopicID values to a list --->

      <cfset form.SelectDocumentTopics = ValueList(GetSelectedTopics.JoinedID)>





<!--- This select menu lists Topics and Subtopics; if topics and subtopics were already selected for form.documentID, --->

<!---  then display them in select menu using query GetSelectedTopics --->



<!--- query Get_Document_Topics_Subtopics requests for Topics and Subtopics --->



<select name="SelectDocumentTopics" size="15" multiple="yes"> 

 <cfoutput query="Get_Document_Topics_Subtopics" group="DocumentTopic"> 



  <!--- show topics ----> 

  <option value="#Get_Document_Topics_Subtopics.DocumentTopicID#" selected="#form.SelectDocumentTopics#">#Get_Document_Topics_Subtopics.DocumentTopic#</option> 



  <cfoutput> 

    <!---- show subtopics if they exist ----> 

    <cfif val(Get_Document_Topics_Subtopics.DocumentSubTopicID)> 

    <!---- use a series of HTML non-breaking spaces to indent subtopics ----> 

    <option value="#Get_Document_Topics_Subtopics.DocumentTopicID#:#Get_Document_Topics_Subtopics.DocumentSubTopicID#" selected="#form.SelectDocumentTopics#">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;#Get_Document_Topics_Subtopics.DocumentSubTopicTitle#

    </cfif> 

  </cfoutput> 



</cfoutput> 

</select>

Open in new window

0
 
LVL 52

Expert Comment

by:_agx_
ID: 33783264
>> Now you don't want to take your car to the local grocery store because there's
>> a cop on the highway.  :)

...  But he could decide to go grocery shopping after work! :P  

( Just joking.  Sorry, that one just made me laugh. I'll have to remember that one ;-)
0
 
LVL 39

Expert Comment

by:gdemaria
ID: 33783407
> But he could decide to go grocery shopping after work

That's a good pont, agx.    But if he exceeded the speed limit, then he would be guilty of SQL injection and could be arrested by the cop for posession of bananas.

It's a complicated analogy,  but really helps clear things up, don't you think?   lol

0
 
LVL 3

Author Comment

by:Eric Bourland
ID: 33783440
I understand the cast( function better than ever now. ;-)
0
 
LVL 39

Expert Comment

by:gdemaria
ID: 33783589


 selected="#form.SelectDocumentTopics#">

this is not a valid method for selecting an option in a select tag.

The format is more like you'd use in a checkbox

 <cfif (criteria)>selected='select'</cfif>


The criteria is to see if the current option is among the list of options stored in the database (from the JoinedID column)
0
 
LVL 3

Author Comment

by:Eric Bourland
ID: 33784059
<cfif (criteria)>selected='select'</cfif>

Hmmm. I am trying different ideas. Would the OPTION look like this?

  <!--- show topics ---->
  <option value="#Get_Document_Topics_Subtopics.DocumentTopicID#"
   <cfif isDefined(form.SelectDocumentTopics)>
selected="#Get_Document_Topics_Subtopics.DocumentTopic#"</cfif>>
  #Get_Document_Topics_Subtopics.DocumentTopic#</option>

... no. That is not right.

  <!--- show topics ---->
  <option value="#Get_Document_Topics_Subtopics.DocumentTopicID#"
  <cfif val(form.SelectDocumentTopics)>selected="#form.SelectDocumentTopics#">#Get_Document_Topics_Subtopics.DocumentTopic</cfif>
 
  >#Get_Document_Topics_Subtopics.DocumentTopic#</option>

Hmm. No.

I am looking at examples of the way that I have done checkboxes. I am missing something.
0
 
LVL 39

Assisted Solution

by:gdemaria
gdemaria earned 500 total points
ID: 33785143

In simple HTML speak, a selected option would look like this...

 <option value="123:10" selected>Library Books</option>

or ..

 <option value="123:10" selected='selected'>Library Books</option>


To indicate that an option should be highlighted, you need SELECT='selected'   Just like with a checkbox you would use  CHECKED="Checked"

So, this must be incorrect..

 selected="#form.SelectDocumentTopics#">

Unless of course FORM.SelectDocumentTopics somehow equals "selected" ... unlikely.


For the CFIF part (the criteria) your two examples only test to see if a variable exists or has any value at all..

 <cfif isDefined(form.SelectDocumentTopics)>   -- does the form variable exist?

 <cfif val(form.SelectDocumentTopics)>   ---  does the form variable have a numeric value > 0 ?


Neither of these does the trick for you.




Let's see what we have...

 In order to select an option you want to see if the VALUE of that option already exists in the database.

 So what is the value of the option?


<option value="#Get_Document_Topics_Subtopics.DocumentTopicID#:#Get_Document_Topics_Subtopics.DocumentSubTopicID#"


Its the Topic ID concatinated with the SubTopic ID.

So, we want to see if this combination exists in the database.

Conveniently, we have created a concatinated column in the SELECT clause of this same format...

We called in JoinID...

      <cfquery datasource="#ds#" name="GetSelectedTopics">
              SELECT DocumentTopicID
              , DocumentSubTopicID
              , cast(DocumentTopicID AS varchar) + ':' + cast(DocumentSubTopicID AS varchar) AS JoinedID
              FROM    tbl_Document_Has_Topic
              WHERE  DocumentID = <cfqueryparam cfsqltype="cf_sql_integer" value="#val(form.documentID)#">
       </cfquery>
     

 And we put all the JoinIDs from the database into one comma delimited list of JoinIDs...
      <cfset form.SelectDocumentTopics = ValueList(GetSelectedTopics.JoinedID)>


So, now we have a variable that we can compare to the option's value:   form.SelectDocumentTopics

Remember that form.SelectDocumentTopics  is a LIST of comma delimited join IDs.


Now, let's compare the OPTION's Value with the list of JoinIDs in the database

form.SelectDocumentTopics


<option value="#Get_Document_Topics_Subtopics.DocumentTopicID#:#Get_Document_Topics_Subtopics.DocumentSubTopicID#"

So, your CFIF statement should compare

See if this value:  Get_Document_Topics_Subtopics.DocumentTopicID#:#Get_Document_Topics_Subtopics.DocumentSubTopicID

Is on this list:
form.SelectDocumentTopics

If it is... the option will be selected.

make sense?
 


0
 
LVL 3

Author Comment

by:Eric Bourland
ID: 33785160
That is a good explanation! Thank you. I am working on this....
0
 
LVL 3

Author Comment

by:Eric Bourland
ID: 33786627
First, I want to reiterate what a very thorough and patient explanation this is.

I've been working on this for a few hours, trying different ideas.

To see if this value: #Get_Document_Topics_Subtopics.DocumentTopicID#:#Get_Document_Topics_Subtopics.DocumentSubTopicID#

is on this list:

form.SelectDocumentTopics

....I think I should use the ListContains function.

I tried a couple of ideas:

  <option value="#Get_Document_Topics_Subtopics.DocumentTopicID#" <cfif listcontains(form.SelectDocumentTopics,#Get_Document_Topics_Subtopics.DocumentTopicID#)selected='selected'</cfif>>#Get_Document_Topics_Subtopics.DocumentTopic#</option>

  <option value="#Get_Document_Topics_Subtopics.DocumentTopicID#" <cfif #listcontains(form.SelectDocumentTopics,Get_Document_Topics_Subtopics.DocumentTopicID)# selected='selected'</cfif>>#Get_Document_Topics_Subtopics.DocumentTopic#</option>

... but I am obviously going down the wrong path.

I understand your concept, above:

* I check to see if the concatenated value form.SelectDocumentTopics

contains value

#Get_Document_Topics_Subtopics.DocumentTopicID#:#Get_Document_Topics_Subtopics.DocumentSubTopicID#

* and, if true, select the option.

I am having trouble putting together a combination of CFIF and ListContains that does not break ColdFusion. =)

And, is ListContains the function that I should use?

Sorry to be dense. (I'm feeling pretty dense and frustrated right now.) Good night.

Eric
0
 
LVL 39

Expert Comment

by:gdemaria
ID: 33789960

You have the right idea,  how does it break coldfusion ?  There must be a simple error..


However I would use ListFind() instead of listContains..

 <cfif listFind(form.SelectDocumentTopics, Get_Document_Topics_Subtopics.DocumentTopicID)


But, remember we said that

>  see if the list contains the value

> #Get_Document_Topics_Subtopics.DocumentTopicID#:#Get_Document_Topics_Subtopics.DocumentSubTopicID#


You are only checking for the topic ID, not the whole thing..

 <cfif listFind(form.SelectDocumentTopics,
      "#Get_Document_Topics_Subtopics.DocumentTopicID#:#Get_Document_Topics_Subtopics.DocumentSubTopicID#")>
0
 
LVL 3

Author Comment

by:Eric Bourland
ID: 33792332
I am glad to know that I was on the right track. I changed from ListContains to ListFind.

I did this:

  <option value="#Get_Document_Topics_Subtopics.DocumentTopicID#"
 
  <cfif listFind(form.SelectDocumentTopics,"#Get_Document_Topics_Subtopics.DocumentTopicID#:#Get_Document_Topics_Subtopics.DocumentSubTopicID#")
   
  selected='selected' </cfif>>
 
  #Get_Document_Topics_Subtopics.DocumentTopic#
 
  </option>

and I think I got all of the syntax correct. ColdFusion does not like the selected attribute of the OPTION tag:

Invalid CFML construct found on line 628 at column 4.  
ColdFusion was looking at the following text:
selected

The CFML compiler was processing:

A cfif tag beginning on line 626, column 4.
 
 
The error occurred in C:\websites\ebwebwork.com\cep\admin\insert_update_test.cfm: line 628
 
626 :   <cfif listFind(form.SelectDocumentTopics,"#Get_Document_Topics_Subtopics.DocumentTopicID#:#Get_Document_Topics_Subtopics.DocumentSubTopicID#")
627 :    
628 :    selected="selected" </cfif>>
629 :  
630 :   #Get_Document_Topics_Subtopics.DocumentTopic#

 
I did some research to refresh my memory about selected. It looks like either

selected='selected' or selected="selected" is allowed:

http://www.w3schools.com/html/tryit.asp?filename=tryhtml_select3

Do I need to wrap "selected" in something to make ColdFusion accept it?
0
 
LVL 39

Expert Comment

by:gdemaria
ID: 33792841

Coldfusion doesn't care what you write outside of the Coldfusion syntax, right?   You could write <cfif true> sdfkjsdlkfjsd fkjsdf s</cfif>    and that is a valid statement that Coldfusion would process and display correctly to the page, even though sdfkjsdlkfjsd fkjsdf  doesn't make any sense.

Coldfusion cannot give you an error because you mess-up HTML either.
So if you were to write..

<cfset checkFlag = "checkeddd">
<input type="checkbox" value="1" #checkFlag#>

Coldfusion could not complain about that even though it's not valid HTML.


So, if Coldfusion is complaining about something it shouldn't complain about, you have to look to the closest Coldfusion code and see why it's a problem.    


Sometimes things are simplier than they seem, always start with the simple syntax issues...

  <cfif listFind(form.SelectDocumentTopics,"#Get_Document_Topics_Subtopics.DocumentTopicID#:
#Get_Document_Topics_Subtopics.DocumentSubTopicID#")
                                                                                          ^^^^   CFIF is not closed, need >
  selected='selected' </cfif>

0
 
LVL 39

Expert Comment

by:gdemaria
ID: 33792868

Btw, I think your code would be easier to read and type if you had shorter names.  I totally agree with very clear column and variable names, but your query name is a bit long.

You could do a global replace on :  Get_Document_Topics_Subtopics

and change it something like this:  getTopics  

and I think the page would be half as long! :)

0
 
LVL 3

Author Comment

by:Eric Bourland
ID: 33793166
>>>  CFIF is not closed, need >

Of course. I knew something was missing. I was staring right at that. Dang. =)

>>>You could do a global replace on :  Get_Document_Topics_Subtopics

I agree. Good idea. Done.

OK, that got rid of the error! The list of Topics and Subtopics appears correctly in the dropdown menu.

But none are selected. Rats.

For example, DocumentID 157 should have several topics selected:

http://ebwebwork.com/cep/admin/insert_update_test.cfm?DocumentID=157

when I load it in the insert_update_test.cfm template.

I am looking again at the way that we told ColdFusion to check for selected topics. We used ListFind to compare the concatenated value:

 form.SelectDocumentTopics

with

#Get_Document_Topics_Subtopics.DocumentTopicID#:#Get_Document_Topics_Subtopics.DocumentSubTopicID#

I think something is missing. I am looking further. I missed something. =)
<!--- This select menu lists Topics and Subtopics; if topics and subtopics were already selected for form.documentID, --->

<!---  then display them in select menu using query GetSelectedTopics --->



<!--- query getTopics requests for Topics and Subtopics --->



<select name="SelectDocumentTopics" size="15" multiple="yes"> 



<!--- get Topics --->

 <cfoutput query="getTopics" group="DocumentTopic"> 

  

  <option value="#getTopics.DocumentTopicID#"

  <cfif listFind(form.SelectDocumentTopics,"#getTopics.DocumentTopicID#:#getTopics.DocumentSubTopicID#")>

   selected='selected' </cfif>>

  #getTopics.DocumentTopic#

   </option>





  <cfoutput> 

    <!---- get subtopics if they exist ----> 

    <cfif val(getTopics.DocumentSubTopicID)> 

    <!---- use HTML non-breaking spaces to indent subtopics ----> 

    

  <option value="#getTopics.DocumentTopicID#"

  <cfif listFind(form.SelectDocumentTopics,"#getTopics.DocumentTopicID#:#getTopics.DocumentSubTopicID#")>

   selected='selected' </cfif>>

&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;#getTopics.DocumentSubTopicTitle#

   </option>

   

    

    </cfif> 

  </cfoutput> 



</cfoutput> 

</select>

Open in new window

0
 
LVL 52

Expert Comment

by:_agx_
ID: 33794118
>> But if he exceeded the speed limit, then he would be guilty of SQL injection and
>> could be arrested by the cop for posession of bananas.

ROFL ;-)

Judge: What are the charges against the defendant?
DA:  Possession of bananas your honor.
Judge: Um... come again?
0
 
LVL 52

Expert Comment

by:_agx_
ID: 33794132
>> For example, DocumentID 157 should have several topics selected:

I did a view source of that page and don't see the "selected" attribute anywhere. So you might want to see if your listFind() is working.

ie <cfif listFind(form.SelectDocumentTopics,"#getTopics.DocumentTopicID#:#getTopics.DocumentSubTopicID#")>
.....
0
 
LVL 39

Expert Comment

by:gdemaria
ID: 33795508

agx is right on with the listFInd being an issue, how do you check it's values?

Just print them out to the screen...

<cfoutput>
 List of Existing Topics: #form.SelectDocumentTopics#<br>
</cfoutput>
<cfoutput query="getTopics">
  Is this value on the list? "#getTopics.DocumentTopicID#:#getTopics.DocumentSubTopicID#
</cfoutput>


On a second, different problem...

Using agx's idea of looking at source... here is a snippet of the <select> tag after CF is done with it...

<select name="SelectDocumentTopics" size="15" multiple="yes">
  <option value="1">Common Core Standards</option>
  <option value="6">Federal Education Programs</option>
  <option value="6">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Congressional Testimony</option>
  <option value="6">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Economic Stimulus Package/ARRA</option>

The value column does not show the two digit format we planned

 of topicID:subTopicID  as in ...  123:42




0
 
LVL 3

Author Comment

by:Eric Bourland
ID: 33795514
Got it. OK. I am looking at this again.
0
 
LVL 3

Author Comment

by:Eric Bourland
ID: 33795606
I printed on the screen the list of existing topics:

http://ebwebwork.com/cep/admin/insert_update_test.cfm?DocumentID=157

and it does show the topicID:subTopicID format. However I notice that some topic IDs are missing. I mean discrete topic IDs; each topicID should have its own single list item:

TopicIDs
1
3
6
7
12
24

It's also strange that this: List of Existing Topics: #form.SelectDocumentTopics#

outputs: List of Existing Topics: ,,,
0
 
LVL 3

Author Comment

by:Eric Bourland
ID: 33795663
I imagine that the reason we do not get the subtopics is, currently, DocumentID=157 is not assigned to any subtopics. And since we have said to display subtopics only when a subtopic has a value greater than 0:

 <cfif val(getTopics.DocumentSubTopicID)>

then no subtopics are displayed. So instead of topicID:subTopicID, we get only topicID.
0
 
LVL 3

Author Comment

by:Eric Bourland
ID: 33796414
I'm going through it step by step.

0. this part requests values from columns in tbl_CEP_Document_Topic and tbl_CEP_Document_Subtopic

<!--- query getTopics requests values from columns in tbl_CEP_Document_Topic and tbl_CEP_Document_Subtopic, for use later in the insert_update.cfm template --->

<cfquery datasource="#ds#" name="getTopics">
           SELECT tp.DocumentTopicID
                  , tp.DocumentTopic
                  , sub.DocumentSubTopicID
                  , sub.DocumentSubTopicTitle
           FROM tbl_CEP_Document_Topic tp
           LEFT JOIN tbl_CEP_Document_Subtopic sub
           ON sub.DocumentTopicID = tp.DocumentTopicID
           ORDER BY tp.DocumentTopic, sub.DocumentSubTopicTitle
</cfquery>

1. This part selects existing topics and subtopics for the current documentID; and creates a value from TopicID and SubTopicID AS JoinedID

<!--- this query requests existing topics and subtopics for the current documentID --->
       <cfquery datasource="#ds#" name="GetSelectedTopics">
              SELECT DocumentTopicID
              , DocumentSubTopicID
              , cast(DocumentTopicID AS varchar) + ':' + cast(DocumentSubTopicID AS varchar) AS JoinedID
              FROM    tbl_Document_Has_Topic
              WHERE  DocumentID = <cfqueryparam cfsqltype="cf_sql_integer" value="#val(form.documentID)#">
       </cfquery>

2. This part creates a value, form.SelectDocumentTopics, and makes it equal to the value of the JoinedID from part 1:

<!--- convert the DocumentTopicID, DocumentSubTopicID values to a list --->
      <cfset form.SelectDocumentTopics = ValueList(GetSelectedTopics.JoinedID)>

3. The SELECT menu is also name SelectDocumentTopics, to match form.SelectDocumentTopics created in part 2

<!--- This select menu lists Topics and Subtopics; if topics and subtopics were already selected for form.documentID, --->
<!---  then display them in select menu using query GetSelectedTopics --->
<!--- query getTopics requests for Topics and Subtopics --->

<select name="SelectDocumentTopics" size="15" multiple="yes">

4. inside the SELECT menu we output the query="getTopics", and  group the output on DocumentTopic:

<!--- get Topics --->
 <cfoutput query="getTopics" group="DocumentTopic">
 
5. here we have the OPTION value="#getTopics.DocumentTopicID#" -- this just displays the Topics

  <option value="#getTopics.DocumentTopicID#"

6. Use ListFind to ask if the value of form.SelectDocumentTopics (from part 2) is contained within this value: #getTopics.DocumentTopicID#:#getTopics.DocumentSubTopicID#; also, close the CFIF

  <cfif listFind(form.SelectDocumentTopics,"#getTopics.DocumentTopicID#:#getTopics.DocumentSubTopicID#")>
   selected='selected' </cfif>>

* as _agx_ and gdemaria have observed, this ListFind is not doing what we need it to do, so maybe consider another method *

7. List Topic title; close OPTION
  #getTopics.DocumentTopic#
   </option>

8. Subtopics work much the same as Topics. Above, I wondered if the val() function prevents Subtopics from appearing in the dyad of: #getTopics.DocumentTopicID#:#getTopics.DocumentSubTopicID#

but I do not think that makes sense. I still wonder if the val() function is messing with the ListFind function in part 2.

  <cfoutput>
    <!---- get subtopics if they exist ---->
    <cfif val(getTopics.DocumentSubTopicID)>
    <!---- use HTML non-breaking spaces to indent subtopics ---->
   
  <option value="#getTopics.DocumentTopicID#"
  <cfif listFind(form.SelectDocumentTopics,"#getTopics.DocumentTopicID#:#getTopics.DocumentSubTopicID#")>
   selected='selected' </cfif>>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;#getTopics.DocumentSubTopicTitle#
   </option>
   
   
    </cfif>
  </cfoutput>

</cfoutput>
</select>

I need to think about this further after I get more sleep. Good night friends.

Eric B
0
 
LVL 39

Expert Comment

by:gdemaria
ID: 33804671

>  It's also strange that this: List of Existing Topics: #form.SelectDocumentTopics#

>  outputs: List of Existing Topics: ,,,

makre sure you're testing with a document that actually has a topic/subtopics selected
0
 
LVL 39

Expert Comment

by:gdemaria
ID: 33804689
> * as _agx_ and gdemaria have observed, this ListFind is not doing what we need it to do, so maybe consider another method *

I don't recall saying that.   ListFind is the right function to use, not listContains.

0
 
LVL 39

Expert Comment

by:gdemaria
ID: 33804696
> but I do not think that makes sense. I still wonder if the val() function is messing with the ListFind function in part 2.

>    <cfif val(getTopics.DocumentSubTopicID)>

The val function here is simply asking, does the subTopicID have a numeric value > 0 ?

If it does, then there is a subtopic, so display it.

If it does not have a value, then there is no subtopic so don't attempt to display it (it's a topic only record, so there is no need to display a blank line for the subtopic).

You can try removing it to see see what I mean, but be ready to put it back
0
 
LVL 39

Expert Comment

by:gdemaria
ID: 33804701
Ok, I've reviewed everything, I have lost track of the problem we are trying to solve.

Everything looks good.   I think you just need to have a document that has topics/subtopics chosen for it..  If the document doesn't then of course no topics will be highlighted.


If that's not the problem, let me know what we're trying to fix right now and provide your code - thanks!
0
 
LVL 3

Author Comment

by:Eric Bourland
ID: 33804774
I think you are right. On all counts. I think it is working. I am testing it.
0
 
LVL 3

Author Comment

by:Eric Bourland
ID: 33806316
gdemaria,

First, thank you again for all of your help.

It's almost working. =)

I've had some time to test the insert_update.cfm test application. It does display, in the SELECT menu, a documentID's selected topics and subtopics ... but only when a Topic AND a Subtopic are selected for a DocumentID.

When a Topic alone (no subtopic) is selected for a DocumentID, then that selected Topic does not display in the SELECT menu -- even though, in the Document_Has_Topic table, a document topic is clearly assigned to a DocumentID. (See attached screen capture: you will see DocumentID=157, assigned to TopicIDs 1, 6, 12, 16, 24; and SubtopicIDs 1, 4, 6, 11, 21.)

For example:

http://ebwebwork.com/cep/admin/insert_update_test.cfm?DocumentID=157
This document has many topics and subtopics assigned to it, and these appear correctly selected in the SELECT menu. But this DocumentID=157 is also assigned to Topics "Common Core Standards" and "Home / What's New" ... and these topics have no subtopics.

In the SELECT menu, the topics  "Common Core Standards" and "Home / What's New" are not selected for DocumentID=157.
I hope this makes sense.
Do you think it is possible to display the selected Topics in the SELECT menu, even if a SubtopicID value for a document is NULL?
I'm sorry that this question is dragging on. I hope you are well. Happy Friday.
Eric


Untitled-1.gif
0
 
LVL 39

Expert Comment

by:gdemaria
ID: 33807439
Also, unless you've changed the selected items since posting, why is the "Federal Education Programs"  topic ID 6 selected?   The topic-level of this is not selected, only it's subtopics.

0
 
LVL 39

Expert Comment

by:gdemaria
ID: 33807503
Not sure what you're current code looks like, so I'll give you an idea where to look.

The thing that determines whether or not an option is selected is the CIF ListFind()  statement for that item.

So, take the CFIF ListFind() statement for the TOPIC (not subtopic) and look at it closely.   Look at the string that's being searched for and compare it to the list that is being searched.   The string must not be on the list for whatever reason.   This should be fairly obvious why if you can see the values.   All you need to do is display the values to the screen, or you can display the value right inside the <option> tag so you can see it in the select...

For example, this is a code snipped from before, you could take this:

  <cfif listFind(form.SelectDocumentTopics,
      "#getTopics.DocumentTopicID#:#getTopics.DocumentSubTopicID#")>selected='selected' </cfif>>
  #getTopics.DocumentTopic#
</option>


and change it to this...

  <cfif listFind(form.SelectDocumentTopics,
      "#getTopics.DocumentTopicID#:#getTopics.DocumentSubTopicID#")>selected='selected' </cfif>>
  #getTopics.DocumentTopic#. Is "#getTopics.DocumentTopicID#:#getTopics.DocumentSubTopicID#" in this #form.SelectDocumentTopics#
</option>

That is all in the <option> tag so will appear on every line of the select for debugging
0
Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

 
LVL 3

Author Comment

by:Eric Bourland
ID: 33809178
I'm sorry -- I meant to post my code. It is posted below.

>>>unless you've changed the selected items since posting, why is the "Federal Education Programs"  topic ID 6 selected?
I did change the selected items by editing the database table. I did this to see for myself how the SELECT menu selected items would respond to my edits in the  tbl_Document_Has_Topic table.

I made the code edit that you suggest, above.

  <option value="#getTopics.DocumentTopicID#"
  <cfif listFind(form.SelectDocumentTopics,
      "#getTopics.DocumentTopicID#:#getTopics.DocumentSubTopicID#")>selected='selected' </cfif>>
  #getTopics.DocumentTopic#. Is "#getTopics.DocumentTopicID#:#getTopics.DocumentSubTopicID#" in this #form.SelectDocumentTopics#
</option>

That is helpful because it shows me exactly how ColdFusion sees my Topics and Subtopics! You can see it here:

http://ebwebwork.com/cep/admin/insert_update_test.cfm?DocumentID=157

I think the Topics problem is very visible now.

Common Core Standards is TopicID 1. But ColdFusion sees it as 1: and seem to expect a Subtopic to follow.

Federal Education Programs is TopicID 6, but ColdFusion sees it as 6:1 -- meaning TopicID 6 : SubtopicID 1

But Congressional Testimony is SubTopicID 1, and it falls under Topic 6.

Do you think we need a way to identify Topics as if they had a SubTopic? Maybe the Federal Education Programs Topic could be 6:0. Or 6:NULL.
<!--- this query requests existing topics and subtopics for the current documentID --->

       <cfquery datasource="#ds#" name="GetSelectedTopics">

              SELECT DocumentTopicID

              , DocumentSubTopicID

              , cast(DocumentTopicID AS varchar) + ':' + cast(DocumentSubTopicID AS varchar) AS JoinedID

              FROM    tbl_Document_Has_Topic

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

       </cfquery>

     

<!--- convert the DocumentTopicID, DocumentSubTopicID values to a list --->

      <cfset form.SelectDocumentTopics = ValueList(GetSelectedTopics.JoinedID)>





<!--- This select menu lists Topics and Subtopics; if topics and subtopics were already selected for form.documentID, --->

<!---  then display them in select menu using query GetSelectedTopics --->

<!--- query getTopics requests for Topics and Subtopics --->



<select name="SelectDocumentTopics" size="15" multiple="yes"> 



<!--- get Topics --->

 <cfoutput query="getTopics" group="DocumentTopic"> 

  

  <option value="#getTopics.DocumentTopicID#"

  <cfif listFind(form.SelectDocumentTopics,"#getTopics.DocumentTopicID#:#getTopics.DocumentSubTopicID#")>

   selected='selected' </cfif>>

  #getTopics.DocumentTopic#

   </option>





  <cfoutput> 

    <!---- get subtopics if they exist ----> 

    <cfif val(getTopics.DocumentSubTopicID)> 

    <!---- use HTML non-breaking spaces to indent subtopics ----> 

  <option value="#getTopics.DocumentTopicID#"

  <cfif listFind(form.SelectDocumentTopics,"#getTopics.DocumentTopicID#:#getTopics.DocumentSubTopicID#")>

   selected='selected' </cfif>>

&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;#getTopics.DocumentSubTopicTitle#

   </option>

  

   

    </cfif> 

  </cfoutput> 



</cfoutput> 

</select>

Open in new window

0
 
LVL 39

Expert Comment

by:gdemaria
ID: 33809261
That does help, look at the list of existing IDs (those topics/subtopics in the database)

 ,6:1,12:11,,16:21

There are blanks!

 ,6:1,12:11,,16:21
^^           ^^

We know that every record MUST have a topic ID at least, so why aren't the topic IDs showing on the list.

After playing with it a minute, it seems that if the subtopic ID is empty, this entire express resolves to NULL

   cast(DocumentTopicID AS varchar) + ':' + cast(DocumentSubTopicID AS varchar)

So just because subTopicID is empty, SQL Server makes the entire expression NULL, I never liked the way SQL server handles data types, not like Oracle which is much more logical to me.

Adding isNull() function to the subtopic should handle that..

   cast(DocumentTopicID AS varchar) + ':' + isNull(cast(DocumentSubTopicID AS varchar),'')




0
 
LVL 39

Expert Comment

by:gdemaria
ID: 33809317

The other problem is that in your listFind for topics, you have included your subtopic ID in the string to search.   You only want to be looking for your topic ID, not your topic and subtopic ID.  

Of course on the subtopic part, you want both Topic and SubTopic IDs, but on the first ListFind() you only want to see if the topic ID is in the list right?   So you don't want to have your subtopic ID part of the listFind ..
0
 
LVL 3

Author Comment

by:Eric Bourland
ID: 33809345
>>>The other problem is that in your listFind for topics, you have included your subtopic ID in the string to search.

I noticed this too.

I am working on this.
0
 
LVL 3

Author Comment

by:Eric Bourland
ID: 33809458
>>>After playing with it a minute, it seems that if the subtopic ID is empty, this entire express resolves to NULL

That explains a lot. I was baffled. I was beginning to question my sanity.

This code is working properly now. =) Attached.

>>>The other problem is that in your listFind for topics, you have included your subtopic ID in the string to search.
I find that if I change

<option value="#getTopics.DocumentTopicID#"
  <cfif listFind(form.SelectDocumentTopics,"#getTopics.DocumentTopicID#:#getTopics.DocumentSubTopicID#")>selected='selected' </cfif>>
  #getTopics.DocumentTopic#
   </option>

to:

<option value="#getTopics.DocumentTopicID#"
  <cfif listFind(form.SelectDocumentTopics,"#getTopics.DocumentTopicID#")>selected='selected' </cfif>>
  #getTopics.DocumentTopic#
   </option>

as I think you are suggesting, that the Topics are no longer selected. I think I need :#getTopics.DocumentSubTopicID# in the ListFind for the Topics. Am I wrong?
<!--- this query requests existing topics and subtopics for the current documentID --->

       <cfquery datasource="#ds#" name="GetSelectedTopics">

              SELECT DocumentTopicID

              , DocumentSubTopicID

              , cast(DocumentTopicID AS varchar) + ':' + isNull(cast(DocumentSubTopicID AS varchar),'') AS JoinedID

              FROM    tbl_Document_Has_Topic

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

       </cfquery>

     

<!--- convert the DocumentTopicID, DocumentSubTopicID values to a list --->

      <cfset form.SelectDocumentTopics = ValueList(GetSelectedTopics.JoinedID)>





<!--- This select menu lists Topics and Subtopics; if topics and subtopics were already selected for form.documentID, --->

<!---  then display them in select menu using query GetSelectedTopics --->

<!--- query getTopics requests for Topics and Subtopics --->



<select name="SelectDocumentTopics" size="15" multiple="yes"> 



<!--- get Topics --->

 <cfoutput query="getTopics" group="DocumentTopic"> 

  

  <option value="#getTopics.DocumentTopicID#"

  <cfif listFind(form.SelectDocumentTopics,"#getTopics.DocumentTopicID#:#getTopics.DocumentSubTopicID#")>selected='selected' </cfif>>

  #getTopics.DocumentTopic#

   </option>





  <cfoutput> 

    <!---- get subtopics if they exist ----> 

    <cfif val(getTopics.DocumentSubTopicID)> 

    <!---- use HTML non-breaking spaces to indent subtopics ----> 

  <option value="#getTopics.DocumentTopicID#"

  <cfif listFind(form.SelectDocumentTopics,"#getTopics.DocumentTopicID#:#getTopics.DocumentSubTopicID#")>

   selected='selected' </cfif>>

&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;#getTopics.DocumentSubTopicTitle#

   </option>

  

   

    </cfif> 

  </cfoutput> 



</cfoutput> 

</select>

Open in new window

0
 
LVL 39

Expert Comment

by:gdemaria
ID: 33809719
> I think I need :#getTopics.DocumentSubTopicID# in the ListFind for the Topics. Am I wrong?

what is your theory on why subtopic ID should be included?  It seems to me you only want to verify that the topic has been selected and if you require a comparison that includes the subtopicID then you likely won't get an accurate match.  Topics are chosen without the subtopic aren't they?   The format for topic would be   "123:"  -- there is not subtopic ID so I don't see that being included in the comparison

I would repeat the same excersize as before.   You can easily answer the question as soon as you see the thing you are trying to compare next to the list of items you're hoping to find it on.   This visual will make it obvious what is wrong, even though it may not be as obvious how to fix it.

As my Dad always says, "The easiest way to find the answer, is to ask the right question"

So first we have to know what is wrong to figure out how to fix it..
0
 
LVL 3

Author Comment

by:Eric Bourland
ID: 33810028
Your Dad is wise. He sounds a lot like my Dad.

OK, I know that variable form.SelectDocumentTopics is a list of joined TopicIDs and SubtopicIDs.

In this query:

     <cfquery datasource="#ds#" name="GetSelectedTopics">
              SELECT DocumentTopicID
              , DocumentSubTopicID
              , cast(DocumentTopicID AS varchar) + ':' + isNull(cast(DocumentSubTopicID AS varchar),'') AS JoinedID
              FROM    tbl_Document_Has_Topic
              WHERE  DocumentID = <cfqueryparam cfsqltype="cf_sql_integer" value="#val(form.documentID)#">
       </cfquery>

We list the TopicIDs and SubtopicIDs in this form: TopicIDs : SubtopicIDs

Function listFind looks for selected TopicIDs and SubtopicIDs with the variable form.SelectDocumentTopics. As so:

<cfif listFind(form.SelectDocumentTopics,"#getTopics.DocumentTopicID#:#getTopics.DocumentSubTopicID#")>

We don't need Subtopics to appear in the list of OPTIONS for Topics. But, if I change

<cfif listFind(form.SelectDocumentTopics,"#getTopics.DocumentTopicID#:#getTopics.DocumentSubTopicID#")>

to

<cfif listFind(form.SelectDocumentTopics,"#getTopics.DocumentTopicID#")>

then selected Topics and Subtopics both disappear from the SELECT menu.

(I am trying to see this, as you suggest.)

Why do Topics disappear? They must not be on the list of selected options.

Hmmm.

I am still looking at this.
0
 
LVL 39

Expert Comment

by:gdemaria
ID: 33810074
When I said use the same technique as before, I meant display the information to the screen so we can see what the values look like.    We just want to see the list of IDs and the ID of the topic you want to check.  

If we can actually see those values, we can see what is different about them and understand why they don't match.   That's the fastest way to know why the code is not working, let's just see the values its working with..
0
 
LVL 3

Author Comment

by:Eric Bourland
ID: 33810084
I see that is I leave in the colon:

 <cfif listFind(form.SelectDocumentTopics,"#getTopics.DocumentTopicID#:")>selected='selected' </cfif>>

It works. Is that what I was missing?
0
 
LVL 39

Expert Comment

by:gdemaria
ID: 33810095
> It works. Is that what I was missing?

Could very well be, it makes sense... I didn't know either, that's why I wanted to see the values
0
 
LVL 3

Author Comment

by:Eric Bourland
ID: 33810132
I want to see the values too. I put this as a test:

<cfoutput>
 <h2>List of Existing Topics: #form.SelectDocumentTopics#</h2>
</cfoutput>
<cfoutput query="getTopics">
  <p>"#getTopics.DocumentTopicID#:#getTopics.DocumentSubTopicID#</p>
</cfoutput>

And the result is visible here, under the SELECT menu:

http://ebwebwork.com/cep/admin/insert_update_test.cfm?DocumentID=157

I see there are no gaps. And the two topics that have no subtopics

Common Core Standards (TopicID 1) and Home / What's New (TopicID 24) appear as:

1:
24:

... I think this is acceptable. What do you think?
0
 
LVL 39

Expert Comment

by:gdemaria
ID: 33810221


Yeah, it looks like you've got a match now.

This is your list of existing IDs :

  List of Existing Topics: 1:,6:1,12:11,24:,16:21,6:6,6:,6:4

These are the ones you're testing against the list...

1:
6:1
6:2
6:3
6:5
6:4
6:6
6:7
7:8
7:9
7:10
24:

Notice how the 1: and the 24:  now match the list.   The list has it as 1:  and 24:  so with out the : on the search ID you would not match it.   (  24 does not match 24: )


So....   does it save ?   :)
0
 
LVL 3

Author Comment

by:Eric Bourland
ID: 33810326
>>>Notice how the 1: and the 24:  now match the list.   The list has it as 1:  and 24:  so with out the : on the search ID you would not match it.   (  24 does not match 24: )

I am very relieved that this makes sense. =)

It does not save. Yet. I still need to edit the code that Updates these tables:

tbl_CEP_Document_Topic
tbl_CEP_Document_Subtopic
tbl_Document_Has_Topic

... I am working on that now. More soon.
0
 
LVL 39

Expert Comment

by:gdemaria
ID: 33810356
I think this page would only update

 tbl_Document_Has_Topic


The other two would be managed on another page probably...
0
 
LVL 3

Author Comment

by:Eric Bourland
ID: 33810637
The form updates. But... ooops. The form does not take the selected values in the SELECT list and update the table tbl_Document_Has_Topic with those values. It looks like the form, for DocumentID=157, updated ALL possible values for all topics and subtopics.

~sigh~

http://ebwebwork.com/cep/admin/insert_update_test.cfm?DocumentID=157

The form also turned all NULL values into integer values. So that Topic selections, which used to be for example 1:NULL, are now 1:integer; so now Topic selections do not appear in the SELECT list.

The query that updates the document with new topics is this:

<cfquery name="UpdateDocumentInsertNewTopics" datasource="#ds#">
INSERT INTO tbl_Document_Has_Topic
            (
        DocumentID
        ,  DocumentTopicID
        ,  DocumentSubTopicID
        )
SELECT   <cfqueryparam value="#form.DocumentID#" cfsqltype="cf_sql_integer">
         , t.DocumentTopicID
         , st.DocumentSubtopicID
         FROM  tbl_CEP_Document_Topic t, tbl_CEP_Document_Subtopic st
WHERE   t.DocumentTopicID  IN
(
<cfqueryparam value="#form.SelectDocumentTopics#" cfsqltype="cf_sql_integer" list="true">
)
</cfquery>


but I do not think that cfqueryparam value="#form.SelectDocumentTopics#" is reading the correct, selected values from the SELECT list. What do you think?
<!--- the action is UPDATE; a DocumentID Exists --->

    

				<cfif val(form.DocumentID)>

                               

      



   <!--- group queries in a cftransaction: UPDATE Document; DELETE Document Topics; INSERT (new) Document Topics--->

			<cftransaction>

    

  			  <cfquery name="UpdateDocument" datasource="#ds#">

				  UPDATE tbl_CEP_Documents

				  SET   DocumentTitle = <cfqueryparam cfsqltype="cf_sql_varchar" value="#Trim(form.DocumentTitle)#">,

					DocumentType = <cfqueryparam cfsqltype="cf_sql_varchar" value="#Trim(form.DocumentType)#">,

					DocumentAuthor = <cfqueryparam cfsqltype="cf_sql_varchar" value="#Trim(form.DocumentAuthor)#">,

				    DocumentAbstract = <cfqueryparam cfsqltype="cf_sql_varchar" value="#Trim(form.DocumentAbstract)#">,

  				    DocumentKeyword = <cfqueryparam cfsqltype="cf_sql_varchar" value="#Trim(form.DocumentKeyword)#">,

                    <cfif len(trim(uploadedImage))>

					DocumentImage = <cfqueryparam cfsqltype="cf_sql_varchar" value="#uploadedImage#" null="#not len(uploadedImage)#">,

                    </cfif>

				    DocumentPublicationDate = <cfqueryparam cfsqltype="cf_sql_date" value="#Trim(form.DocumentPublicationDate)#">,

				    DateRecordModified = <cfqueryparam cfsqltype="cf_sql_timestamp" value="#now()#">

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

			</cfquery>





<cfquery name="UpdateDocumentDeleteTopics" datasource="#ds#">

   DELETE FROM tbl_Document_Has_Topic

   WHERE DocumentID = <cfqueryparam value="#form.DocumentID#" cfsqltype="cf_sql_integer">

</cfquery>



<cfquery name="UpdateDocumentInsertNewTopics" datasource="#ds#">

INSERT INTO tbl_Document_Has_Topic

		(

        DocumentID

        ,  DocumentTopicID

        ,  DocumentSubTopicID

        )

SELECT   <cfqueryparam value="#form.DocumentID#" cfsqltype="cf_sql_integer">

         , t.DocumentTopicID

         , st.DocumentSubtopicID

         FROM  tbl_CEP_Document_Topic t, tbl_CEP_Document_Subtopic st

WHERE   t.DocumentTopicID  IN

(

<cfqueryparam value="#form.SelectDocumentTopics#" cfsqltype="cf_sql_integer" list="true">

)

</cfquery>

</cftransaction>







SELECT list:





<select name="SelectDocumentTopics" size="15" multiple="yes"> 



<!--- get Topics --->

 <cfoutput query="getTopics" group="DocumentTopic"> 

  

  <option value="#getTopics.DocumentTopicID#"

  <cfif listFind(form.SelectDocumentTopics,"#getTopics.DocumentTopicID#:")>selected='selected' </cfif>>

  #getTopics.DocumentTopic#

   </option>





  <cfoutput> 

    <!---- get subtopics if they exist ----> 

    <cfif val(getTopics.DocumentSubTopicID)> 

    <!---- use HTML non-breaking spaces to indent subtopics ----> 

  <option value="#getTopics.DocumentTopicID#"

  <cfif listFind(form.SelectDocumentTopics,"#getTopics.DocumentTopicID#:#getTopics.DocumentSubTopicID#")>

   selected='selected' </cfif>>

&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;#getTopics.DocumentSubTopicTitle#

   </option>

  

   

    </cfif> 

  </cfoutput> 



</cfoutput> 

</select>

Open in new window

0
 
LVL 39

Assisted Solution

by:gdemaria
gdemaria earned 500 total points
ID: 33810704

I guess we have a theme for this post, let's see that the values look like that are coming from that form field.

In your action, before you do anything with the insert or udpate or anything, just display the value you're getting from the SELECT tag called: SelectDocumentTopics


<cfouptut><h1>SelectDocumentTopics is [#SelectDocumentTopics#]</h1></cfoutput>
<cfabort>


Keep in mind that this one select tag holds BOTH topicIDs and SubTopic IDs in a comma delimited list.   You have to seperate the topic ID from the subtopic ID and handle them seperately so they get into the correct table column

0
 
LVL 3

Author Comment

by:Eric Bourland
ID: 33810940
>>>I guess we have a theme for this post

Got it. I need to see the values that are selected in the SELECT field. =)


>>><cfoutput><h1>SelectDocumentTopics is [#SelectDocumentTopics#]</h1></cfoutput>
<cfabort>

This displays a long list of all topicIDs and all subtopicIDs. So, currently, we are getting all possible values from the SELECT list, thanks to my last update operation. I am glad the update was limited to one DocumentID.

So I need to change this query to update topicID and subtopicID separately:

<cfquery name="UpdateDocumentInsertNewTopics" datasource="#ds#">
INSERT INTO tbl_Document_Has_Topic
                (
        DocumentID
        ,  DocumentTopicID
        ,  DocumentSubTopicID
        )
SELECT   <cfqueryparam value="#form.DocumentID#" cfsqltype="cf_sql_integer">
         , t.DocumentTopicID
         , st.DocumentSubtopicID
         FROM  tbl_CEP_Document_Topic t, tbl_CEP_Document_Subtopic st
WHERE   t.DocumentTopicID  IN
(
<cfqueryparam value="#form.SelectDocumentTopics#" cfsqltype="cf_sql_integer" list="true">
)
</cfquery>

I will take a stab at that:

<cfquery name="UpdateDocumentInsertNewTopics" datasource="#ds#">
INSERT INTO tbl_Document_Has_Topic
                (
        DocumentID
        ,  DocumentTopicID
        ,  DocumentSubTopicID
        )
SELECT   <cfqueryparam value="#form.DocumentID#" cfsqltype="cf_sql_integer">
         , t.DocumentTopicID
         , st.DocumentSubtopicID
         FROM  tbl_CEP_Document_Topic t, tbl_CEP_Document_Subtopic st
WHERE   t.DocumentTopicID  IN
(
<cfqueryparam value="#form.SelectDocumentTopics#" cfsqltype="cf_sql_integer" list="true">
)
AND
st.DocumentSubTopicID  IN
(
<cfqueryparam value="#form.SelectDocumentTopics#" cfsqltype="cf_sql_integer" list="true">
)
</cfquery>

... hmmm. No. All topics and subtopics are still selected.

Am I on the right track using the AND operator to include the DocumentSubTopicID?
0
 
LVL 39

Expert Comment

by:gdemaria
ID: 33810997

> This displays a long list of all topicIDs and all subtopicIDs. So, currently, we are getting all possible values from the SELECT list, thanks to my last update operation.

If you had selected every topic and subtopic that would be correct.  Try selecting only 5; say 2 topics and 3 subtopics and then hopefully you will see only 5 items on the debug list.

Btw, I should have used the FORM scope for that variable:   #FORM.SelectDocumentTopics#
0
 
LVL 39

Expert Comment

by:gdemaria
ID: 33811036
Hmmmm, the problem with this logic is a couple things..

WHERE   t.DocumentTopicID  IN
(
<cfqueryparam value="#form.SelectDocumentTopics#" cfsqltype="cf_sql_integer" list="true">
)
AND
st.DocumentSubTopicID  IN
(
<cfqueryparam value="#form.SelectDocumentTopics#" cfsqltype="cf_sql_integer" list="true">
)
</cfquery>


First since you are using AND, that means the topic ID has to be in the list AND the subtopic ID has to be in the list.  What if the user doesn't select any subtopic IDs?  Then the where clause can never be true because the subtopic part of the where clause will never match.

Also, think of the value of this variable:   form.SelectDocumentTopics

If you display the value of this variable, it should look like this:
   1:,6:1,12:11,24:,16:21,6:6,6:,6:4

So when put into the where clause..

 WHERE   t.DocumentTopicID  IN (1:,6:1,12:11,24:,16:21,6:6,6:,6:4)

that is not valid syntax

Seems to me you have to do some work on the list to get it into a format that you can work with.    That list has topics and subtopics seperated by :  and delimited by a ,





0
 
LVL 3

Author Comment

by:Eric Bourland
ID: 33811172
So, then, I cannot use the variable form.SelectDocumentTopics in this query, because form.SelectDocumentTopics takes the form of:

3:3,3:6,3:12,3:24,6:3,6:6,6:12,6:24,12:3,12:6,12:12,12:24,24:3,24:6,24:12,24:24

>>>Seems to me you have to do some work on the list to get it into a format that you can work with

In variable form.SelectDocumentTopics, I need to separate the TopicIDs and the SubtopicIDs. For purposes of this query.

Am I correct?

How can I unlist a List?

0
 
LVL 39

Expert Comment

by:gdemaria
ID: 33811476

<cfloop list=

0
 
LVL 3

Author Comment

by:Eric Bourland
ID: 33812249
Ugh. OK. I have been turning this over in my head for two hours. Here is what I come up with, as an example:

<cfquery name="UpdateDocumentInsertNewTopics" datasource="#ds#">
INSERT INTO tbl_Document_Has_Topic
                (
        DocumentID
        ,  DocumentTopicID
        ,  DocumentSubTopicID
        )
SELECT   <cfqueryparam value="#form.DocumentID#" cfsqltype="cf_sql_integer">
         , t.DocumentTopicID
         , st.DocumentSubtopicID
         FROM  tbl_CEP_Document_Topic t, tbl_CEP_Document_Subtopic st
WHERE   t.DocumentTopicID = <cfloop index = "ListElement"
   list = "#form.SelectDocumentTopics#">
      <cfoutput>t.DocumentTopicID#</cfoutput>
</cfloop>
</cfquery>

Can you tell me if I am at least on the right track? Thanks chief.

Eric
0
 
LVL 3

Author Comment

by:Eric Bourland
ID: 33812700
Or this:

<cfquery name="UpdateDocumentInsertNewTopics" datasource="#ds#">
INSERT INTO tbl_Document_Has_Topic
                (
        DocumentID
        ,  DocumentTopicID
        ,  DocumentSubTopicID
        )
SELECT   <cfqueryparam value="#form.DocumentID#" cfsqltype="cf_sql_integer">
         , t.DocumentTopicID
         , st.DocumentSubtopicID
         FROM  tbl_CEP_Document_Topic t, tbl_CEP_Document_Subtopic st
WHERE   t.DocumentTopicID = <cfloop list = "#form.SelectDocumentTopics#">
      <cfoutput>#t.DocumentTopicID#</cfoutput>
</cfloop>
</cfquery>
0
 
LVL 39

Assisted Solution

by:gdemaria
gdemaria earned 500 total points
ID: 33812702
Hey Eric, if you need help just let me know, no biggie.  Sometimes I don't have time to write a bunch of code out and I'm not sure when you need it and not.

The goal is to seperate out the topic ID and subtopic ID so you can use them in the insert/update.

Here are two ways to do it...



This is the same format as when you do array-style processing

<cfloop index="aSet" list="#form.selectDocumentTopics#">
  <cfset theTopicID = listFirst(aSet,":")>
  <cfset theSubTopicID = "">
  <cfif listLen(aSet,":") gt 1>
     <cfset theSubTopicID = listFirst(aSet,":")>
  </cfif>
  <!---- now you have theTopicID and theSubTopicID to insert ----->

   here you can insert the topic and subtopic, but only if it's not already in the table

</cfloop>


another way of doing it is to build a list of Topic IDs and SubTopic Ids and then do just one or two inserts to update them all..

 <cfset allTopicID = "">
 <cfset allSubTopicID = "">
 <cfloop index="aSet" list="#form.selectDocumentTopics#">
    <cfif listLen(aSet,":") gt 1>
        <cfset allSubTopicID =  ListAppend(allSubTopicID ,  listLast(aSet,":"))>
    <cfelse>
       <cfset allTopicID =  ListAppend(allTopicID ,  listFirst(aSet,":"))>
    </cfif>
</cfloop>

  Now you have all topic IDs in one variable and all subtopic Ids in another.  Now you just need to use them to insert the records as you were doing before..

  allTopicID, allSubTopicID

0
 
LVL 3

Author Comment

by:Eric Bourland
ID: 33812710
Our updates overlapped. I was still staring at the problem and trying different ideas.

I prefer it that you don't write code for me. I always learn a tremendous amount from our exchanges. I value this quite a lot.

Your code above -- I will study it now. More in a while. =)

I hope your Friday evening is relaxing. Peace!

Eric
0
 
LVL 3

Author Comment

by:Eric Bourland
ID: 33816491
<cfset allSubTopicID =  ListAppend(allSubTopicID ,  listLast(aSet,":"))>
<cfset allTopicID =  ListAppend(allTopicID ,  listFirst(aSet,":"))>

That is a really good idea.

I've worked at this off and on since last night. The CFLOOP that you created makes a lot of sense to me, now that I see it. You have built other CFLOOPs similar to this one and I am getting a better idea of when a CFLOOP like this is needed.

What I still do not get is how to set up the query that updates the Topics and Subtopics.

<cfquery name="UpdateDocumentInsertNewTopics" datasource="#ds#">
INSERT INTO tbl_Document_Has_Topic
                (
        DocumentID
        ,  DocumentTopicID
        ,  DocumentSubTopicID
        )
SELECT   <cfqueryparam value="#form.DocumentID#" cfsqltype="cf_sql_integer">
         , t.DocumentTopicID
         , st.DocumentSubtopicID
         FROM  tbl_CEP_Document_Topic t, tbl_CEP_Document_Subtopic st
WHERE   t.DocumentTopicID  IN
(
<cfqueryparam value="#form.SelectDocumentTopics#" cfsqltype="cf_sql_integer" list="true">
)
AND
st.DocumentSubTopicID  IN
(
<cfqueryparam value="#form.SelectDocumentTopics#" cfsqltype="cf_sql_integer" list="true">
)
</cfquery>

I understand that the AND operator is not useful. But I do not see what else to do. What am I missing?

Thanks again. I hope your weekend is going very well.

Eric
<cfset allTopicID = "">

 <cfset allSubTopicID = "">

 <cfloop index="aSet" list="#form.selectDocumentTopics#">

    <cfif listLen(aSet,":") gt 1>

        <cfset allSubTopicID =  ListAppend(allSubTopicID ,  listLast(aSet,":"))>

    <cfelse>

       <cfset allTopicID =  ListAppend(allTopicID ,  listFirst(aSet,":"))>

    </cfif>

</cfloop>



<cfquery name="UpdateDocumentInsertNewTopics" datasource="#ds#"> 

INSERT INTO tbl_Document_Has_Topic 

                ( 

        DocumentID 

        ,  DocumentTopicID 

        ,  DocumentSubTopicID 

        ) 

SELECT   <cfqueryparam value="#form.DocumentID#" cfsqltype="cf_sql_integer"> 

         , t.DocumentTopicID 

         , st.DocumentSubtopicID 

         FROM  tbl_CEP_Document_Topic t, tbl_CEP_Document_Subtopic st 

WHERE   t.DocumentTopicID  IN 

( 

<cfqueryparam value="#form.SelectDocumentTopics#" cfsqltype="cf_sql_integer" list="true"> 

) 

AND

st.DocumentSubTopicID  IN 

( 

<cfqueryparam value="#form.SelectDocumentTopics#" cfsqltype="cf_sql_integer" list="true"> 

) 

</cfquery>

Open in new window

0
 
LVL 39

Expert Comment

by:gdemaria
ID: 33817822
> What I still do not get is how to set up the query that updates the Topics and Subtopics.

I think it's because you need to seperate SELECT statements.  One to get the topics (because there are no subtopics so joining to subtopic would not work) and the other to get the subtopics.

You can do this in one of two ways.  (1)  create two CFQUERY statements, one for topics and one for subtopics (2) create one CFQUERY within one insert statement, but the select part is actually two select statements with a UNION joining them together.

Have you thought about this..   what if someone unselects a previously selected topic/subtopic, how is it being removed from the database?   There are a couple ways to do this (aren't there always?)  (1) before your insert statement, delete all records in that document has_topic for that document ID and then insert them fresh (2) write a "smart" delete statement that will delete the records from _has_topics only if they are not in the new SELECT statement.    

Option 1 is simplier.



0
 
LVL 3

Author Comment

by:Eric Bourland
ID: 33818054
>>>Have you thought about this..   what if someone unselects a previously selected topic/subtopic, how is it being removed from the database?

I have been worrying about that, yes. I believe this takes care of it:

<cfquery name="UpdateDocumentDeleteTopics" datasource="#ds#">
   DELETE FROM tbl_Document_Has_Topic
   WHERE DocumentID = <cfqueryparam value="#form.DocumentID#" cfsqltype="cf_sql_integer">
</cfquery>

But that is something I was going to test. I believe the above query runs every time a document is updated. It was working before and I think I have not done anything in the interim to disrupt it.

I think I will stick with the two queries. I tried it out: please see code below. But I am not sure how the created values allTopicID, allSubTopicID fit in to this query?

I append the full CFTRANSACTION which contains everything that happens when a document gets updated.

I just tested this query ... hmm. It breaks ColdFusion. CF tells me that DocumentSubTopicID is not a valid column. The column is clearly in the database table, and so, clearly, I have not identified th column correctly in my second query, which updates Subtopics.

I get this error:
Error Executing Database Query.  
[Macromedia][SQLServer JDBC Driver][SQLServer]Invalid column name 'DocumentSubTopicID'.  
 
The error occurred in C:\websites\ebwebwork.com\cep\admin\insert_update_test.cfm: line 210
Called from C:\websites\ebwebwork.com\cep\admin\insert_update_test.cfm: line 142
Called from C:\websites\ebwebwork.com\cep\admin\insert_update_test.cfm: line 53
Called from C:\websites\ebwebwork.com\cep\admin\insert_update_test.cfm: line 39
Called from C:\websites\ebwebwork.com\cep\admin\insert_update_test.cfm: line 1
Called from C:\websites\ebwebwork.com\cep\admin\insert_update_test.cfm: line 210
Called from C:\websites\ebwebwork.com\cep\admin\insert_update_test.cfm: line 142
Called from C:\websites\ebwebwork.com\cep\admin\insert_update_test.cfm: line 53
Called from C:\websites\ebwebwork.com\cep\admin\insert_update_test.cfm: line 39
Called from C:\websites\ebwebwork.com\cep\admin\insert_update_test.cfm: line 1
 
208 : WHERE   t.DocumentSubTopicID  IN
209 : (
210 : <cfqueryparam value="#form.SelectDocumentTopics#" cfsqltype="cf_sql_integer" list="true">
211 : )
212 : </cfquery>

I wonder if I need to identify the tables by the aliases t and st -- though I do not see why CF would object to that? What am I missing? I feel like I am getting close to solving this. =) I hope your weekend is going well.

Eric
<!--- group queries in a cftransaction: UPDATE Document; DELETE Document Topics; INSERT (new) Document Topics--->

			<cftransaction>

    

  			  <cfquery name="UpdateDocument" datasource="#ds#">

				  UPDATE tbl_CEP_Documents

				  SET   DocumentTitle = <cfqueryparam cfsqltype="cf_sql_varchar" value="#Trim(form.DocumentTitle)#">,

					DocumentType = <cfqueryparam cfsqltype="cf_sql_varchar" value="#Trim(form.DocumentType)#">,

					DocumentAuthor = <cfqueryparam cfsqltype="cf_sql_varchar" value="#Trim(form.DocumentAuthor)#">,

				    DocumentAbstract = <cfqueryparam cfsqltype="cf_sql_varchar" value="#Trim(form.DocumentAbstract)#">,

  				    DocumentKeyword = <cfqueryparam cfsqltype="cf_sql_varchar" value="#Trim(form.DocumentKeyword)#">,

                    <cfif len(trim(uploadedImage))>

					DocumentImage = <cfqueryparam cfsqltype="cf_sql_varchar" value="#uploadedImage#" null="#not len(uploadedImage)#">,

                    </cfif>

				    DocumentPublicationDate = <cfqueryparam cfsqltype="cf_sql_date" value="#Trim(form.DocumentPublicationDate)#">,

				    DateRecordModified = <cfqueryparam cfsqltype="cf_sql_timestamp" value="#now()#">

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

			</cfquery>





<cfquery name="UpdateDocumentDeleteTopics" datasource="#ds#">

   DELETE FROM tbl_Document_Has_Topic

   WHERE DocumentID = <cfqueryparam value="#form.DocumentID#" cfsqltype="cf_sql_integer">

</cfquery>



 <cfset allTopicID = "">

 <cfset allSubTopicID = "">

 <cfloop index="aSet" list="#form.selectDocumentTopics#">

    <cfif listLen(aSet,":") gt 1>

        <cfset allSubTopicID =  ListAppend(allSubTopicID ,  listLast(aSet,":"))>

    <cfelse>

       <cfset allTopicID =  ListAppend(allTopicID ,  listFirst(aSet,":"))>

    </cfif>

</cfloop>



<cfquery name="UpdateDocumentInsertNewTopics" datasource="#ds#"> 

INSERT INTO tbl_Document_Has_Topic 

                ( 

        DocumentID 

        ,  DocumentTopicID 

        ,  DocumentSubTopicID 

        ) 

SELECT   <cfqueryparam value="#form.DocumentID#" cfsqltype="cf_sql_integer"> 

         , t.DocumentTopicID 

         , st.DocumentSubtopicID 

         FROM  tbl_CEP_Document_Topic t, tbl_CEP_Document_Subtopic st 

WHERE   t.DocumentTopicID  IN 

( 

<cfqueryparam value="#form.SelectDocumentTopics#" cfsqltype="cf_sql_integer" list="true"> 

) 

</cfquery>



<cfquery name="UpdateDocumentInsertNewSubTopics" datasource="#ds#"> 

INSERT INTO tbl_Document_Has_Topic 

                ( 

        DocumentID 

        ,  DocumentTopicID 

        ,  DocumentSubTopicID 

        ) 

SELECT   <cfqueryparam value="#form.DocumentID#" cfsqltype="cf_sql_integer"> 

         , t.DocumentTopicID 

         , st.DocumentSubtopicID 

         FROM  tbl_CEP_Document_Topic t, tbl_CEP_Document_Subtopic st 

WHERE   t.DocumentSubTopicID  IN

( 

<cfqueryparam value="#form.SelectDocumentTopics#" cfsqltype="cf_sql_integer" list="true"> 

) 

</cfquery>



</cftransaction>

Open in new window

0
 
LVL 39

Expert Comment

by:gdemaria
ID: 33818317
Recall this conversation above..


Also, think of the value of this variable:   form.SelectDocumentTopics

If you display the value of this variable, it should look like this:
   1:,6:1,12:11,24:,16:21,6:6,6:,6:4

So when put into the where clause..

 WHERE   t.DocumentTopicID  IN (1:,6:1,12:11,24:,16:21,6:6,6:,6:4)

that is not valid syntax

Seems to me you have to do some work on the list to get it into a format that you can work with.    That list has topics and subtopics seperated by :  and delimited by a ,


... so that is what we did, we chopped up that variable and ended up with a nice clean list of Topic IDs and subTopics ID and these two variables...

<cfset allSubTopicID =  ListAppend(allSubTopicID ,  listLast(aSet,":"))>
<cfset allTopicID =  ListAppend(allTopicID ,  listFirst(aSet,":"))>

These two variables contains the topic IDs and SubTopicIDs you need for your inserts

0
 
LVL 3

Author Comment

by:Eric Bourland
ID: 33819085
<cfset allSubTopicID =  ListAppend(allSubTopicID ,  listLast(aSet,":"))>
<cfset allTopicID =  ListAppend(allTopicID ,  listFirst(aSet,":"))>

Of course. =) I need to remember that.

I am making progress. I understand the IN operator is no longer appropriate.

I set up two simple INSERT queries (attached). When I try to process the form I get this error:

Error Executing Database Query.  
[Macromedia][SQLServer JDBC Driver][SQLServer]Conversion failed when converting the varchar value '1,6,6,6,7,3,12,12' to data type int.  
 
The error occurred in C:\websites\ebwebwork.com\cep\admin\insert_update_test.cfm: line 189

187 :             (
188 :         <cfqueryparam value="#form.DocumentID#" cfsqltype="cf_sql_integer">
189 :             ,<cfqueryparam cfsqltype="cf_sql_varchar" value="#Trim(allTopicID)#">
190 :         )
191 : </cfquery>

 Maybe I do need the IN operator. I am revising the queries to include the IN operator.

Hmm..
<cfquery name="UpdateDocumentInsertNewTopics" datasource="#ds#"> 

INSERT INTO tbl_Document_Has_Topic 

        ( 

        DocumentID 

        ,  DocumentTopicID 

        ) 

VALUES

		(

        <cfqueryparam value="#form.DocumentID#" cfsqltype="cf_sql_integer">

		,<cfqueryparam cfsqltype="cf_sql_varchar" value="#Trim(allTopicID)#">

        )

</cfquery>



<cfquery name="UpdateDocumentInsertNewSubTopics" datasource="#ds#"> 

INSERT INTO tbl_Document_Has_Topic 

        ( 

        DocumentID 

        ,  DocumentSubTopicID 

        ) 

VALUES

		(

        <cfqueryparam value="#form.DocumentID#" cfsqltype="cf_sql_integer">

		,<cfqueryparam cfsqltype="cf_sql_varchar" value="#Trim(allSubTopicID)#">

        )

</cfquery>

Open in new window

0
 
LVL 39

Expert Comment

by:gdemaria
ID: 33819503
Eric, why did you change the CFQUERY From  INSERT.. SELECT.. FROM to an  INSERT.. VALUES statement?

Going back to this cfquery...

<cfquery name="UpdateDocumentInsertNewTopics" datasource="#ds#">
INSERT INTO tbl_Document_Has_Topic
                (
        DocumentID
        ,  DocumentTopicID
        ,  DocumentSubTopicID
        )
SELECT   <cfqueryparam value="#form.DocumentID#" cfsqltype="cf_sql_integer">
         , t.DocumentTopicID
         , st.DocumentSubtopicID
         FROM  tbl_CEP_Document_Topic t, tbl_CEP_Document_Subtopic st
WHERE   t.DocumentTopicID  IN
(
<cfqueryparam value="#form.SelectDocumentTopics#" cfsqltype="cf_sql_integer" list="true">
)
</cfquery>


All you need to do is change this...

<cfqueryparam value="#allTopicID#" cfsqltype="cf_sql_integer" list="true">


to this...

<cfqueryparam value="#form.SelectDocumentTopics#" cfsqltype="cf_sql_integer" list="true">
0
 
LVL 39

Expert Comment

by:gdemaria
ID: 33819535

Sorry, I had those backwards


All you need to do is change this...

<cfqueryparam value="#form.SelectDocumentTopics#" cfsqltype="cf_sql_integer" list="true">

to this...

<cfqueryparam value="#allTopicID#" cfsqltype="cf_sql_integer" list="true">
0
 
LVL 3

Author Comment

by:Eric Bourland
ID: 33819553
Oh, heavens. I am really making it more difficult than it needs to be.

I understand now. =)
0
 
LVL 3

Author Comment

by:Eric Bourland
ID: 33820243
Making progress. The error is gone, and the form processes. However, the result is, only TopicID=1 is selected, and also, 24 subtopics are assigned to topicID = 1. My query selects all subtopics and assign them only to TopicID = 1.

I study the query to find out why that happens. =) If nothing else, I am glad this is not a production web site.

The query to update the DocumentTopic is this:

<cfquery name="UpdateDocumentInsertNewTopics" datasource="#ds#">
INSERT INTO tbl_Document_Has_Topic
                (
        DocumentID
        ,  DocumentTopicID
        ,  DocumentSubTopicID
        )
SELECT   <cfqueryparam value="#form.DocumentID#" cfsqltype="cf_sql_integer">
         , t.DocumentTopicID
         , st.DocumentSubtopicID
         FROM  tbl_CEP_Document_Topic t, tbl_CEP_Document_Subtopic st
WHERE   t.DocumentTopicID  IN
(
<cfqueryparam value="#val(allTopicID)#" cfsqltype="cf_sql_integer" list="true">
)
</cfquery>

I do not think I need the references to DocumentSubTopicID in this query, correct? Since we update DocumentSubTopicID in the next query:

<cfquery name="UpdateDocumentInsertNewSubTopics" datasource="#ds#">
INSERT INTO tbl_Document_Has_Topic
                (
        DocumentID
        ,  DocumentTopicID
        ,  DocumentSubTopicID
        )
SELECT   <cfqueryparam value="#form.DocumentID#" cfsqltype="cf_sql_integer">
         , t.DocumentTopicID
         , st.DocumentSubtopicID
         FROM  tbl_CEP_Document_Topic t, tbl_CEP_Document_Subtopic st
WHERE   t.DocumentTopicID  IN
(
<cfqueryparam value="#val(allSubTopicID)#" cfsqltype="cf_sql_integer" list="true">
)
</cfquery>

Likewise I think I can get rid of references to t.DocumentTopicID in this query. Am I correct?

I used the val() function to numerals greater than 0. Otherwise ColdFusion objected that the datatype was not an integer.

We are making a lot of progress thanks to your patient help. =)

I am going to see what happens when I remove the st.DocumentSubtopicID from the TopicID update and t.DocumentTopicID from the subtopicID update.
0
 
LVL 3

Author Comment

by:Eric Bourland
ID: 33820313
OK, my query to update TopicID:

<cfquery name="UpdateDocumentInsertNewTopics" datasource="#ds#">
INSERT INTO tbl_Document_Has_Topic
                (
        DocumentID
        ,  DocumentTopicID
        )
SELECT   <cfqueryparam value="#form.DocumentID#" cfsqltype="cf_sql_integer">
         , t.DocumentTopicID
         FROM  tbl_CEP_Document_Topic t
WHERE   t.DocumentTopicID  IN
(
<cfqueryparam value="#val(allTopicID)#" cfsqltype="cf_sql_integer" list="true">
)
</cfquery>


... inserts only TopicID = 1 for DocumentID=157. Even if I select other topics in the form, only TopicID = 1 is selected.

My query to update the subtopics:


<cfquery name="UpdateDocumentInsertNewSubTopics" datasource="#ds#">
INSERT INTO tbl_Document_Has_Topic
                (
        DocumentID
        ,  DocumentSubTopicID
        )
SELECT   <cfqueryparam value="#form.DocumentID#" cfsqltype="cf_sql_integer">
         , st.DocumentSubtopicID
         FROM  tbl_CEP_Document_Subtopic st
WHERE   st.DocumentSubTopicID  IN
(
<cfqueryparam value="#val(allSubTopicID)#" cfsqltype="cf_sql_integer" list="true">
)
</cfquery>

... updates no records at all.

So, the update action stops after TopicID = 1 is added.

I am looking at why that is happening.

I wonder if it is the val() function around allTopicID: #val(allTopicID)#

I took away the val() function from allTopicID. Hey! It works. The topics are updated correctly. Yahoo! Subtopics still do not update.

Next I took away the val() function from allSubTopicID. ColdFusion objects with:

Invalid data '' for CFSQLTYPE CF_SQL_INTEGER.  
 
  The error occurred in C:\websites\ebwebwork.com\cep\admin\insert_update_test.cfm: line 209

207 : WHERE   st.DocumentSubTopicID  IN
208 : (
209 : <cfqueryparam value="#allSubTopicID#" cfsqltype="cf_sql_integer" list="true">
210 : )
211 : </cfquery>

So, here is where I am stuck. My query to update the Subtopics (attached below) breaks ColdFusion, because CF says the value of allSubTopicID is not an integer.

Sorry to be so wordy and to reply multiple times in the thread. I am working through this step by step.

I am trying to think why the query to update DocumentTopicID works correctly, but the very similar query to update DocumentSubTopicID causes ColdFusion to think that variable allSubTopicID is not an integer.
<!--- group queries in a cftransaction: UPDATE Document; DELETE Document Topics; INSERT (new) Document Topics--->

			<cftransaction>

    

  			  <cfquery name="UpdateDocument" datasource="#ds#">

				  UPDATE tbl_CEP_Documents

				  SET   DocumentTitle = <cfqueryparam cfsqltype="cf_sql_varchar" value="#Trim(form.DocumentTitle)#">,

					DocumentType = <cfqueryparam cfsqltype="cf_sql_varchar" value="#Trim(form.DocumentType)#">,

					DocumentAuthor = <cfqueryparam cfsqltype="cf_sql_varchar" value="#Trim(form.DocumentAuthor)#">,

				    DocumentAbstract = <cfqueryparam cfsqltype="cf_sql_varchar" value="#Trim(form.DocumentAbstract)#">,

  				    DocumentKeyword = <cfqueryparam cfsqltype="cf_sql_varchar" value="#Trim(form.DocumentKeyword)#">,

                    <cfif len(trim(uploadedImage))>

					DocumentImage = <cfqueryparam cfsqltype="cf_sql_varchar" value="#uploadedImage#" null="#not len(uploadedImage)#">,

                    </cfif>

				    DocumentPublicationDate = <cfqueryparam cfsqltype="cf_sql_date" value="#Trim(form.DocumentPublicationDate)#">,

				    DateRecordModified = <cfqueryparam cfsqltype="cf_sql_timestamp" value="#now()#">

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

			</cfquery>





<cfquery name="UpdateDocumentDeleteTopics" datasource="#ds#">

   DELETE FROM tbl_Document_Has_Topic

   WHERE DocumentID = <cfqueryparam value="#form.DocumentID#" cfsqltype="cf_sql_integer">

</cfquery>



 <cfset allTopicID = "">

 <cfset allSubTopicID = "">

 <cfloop index="aSet" list="#form.selectDocumentTopics#">

    <cfif listLen(aSet,":") gt 1>

        <cfset allSubTopicID =  ListAppend(allSubTopicID ,  listLast(aSet,":"))>

    <cfelse>

       <cfset allTopicID =  ListAppend(allTopicID ,  listFirst(aSet,":"))>

    </cfif>

</cfloop>





<cfquery name="UpdateDocumentInsertNewTopics" datasource="#ds#"> 

INSERT INTO tbl_Document_Has_Topic 

                ( 

        DocumentID 

        ,  DocumentTopicID 

        ) 

SELECT   <cfqueryparam value="#form.DocumentID#" cfsqltype="cf_sql_integer"> 

         , t.DocumentTopicID 

         FROM  tbl_CEP_Document_Topic t

WHERE   t.DocumentTopicID  IN 

( 

<cfqueryparam value="#allTopicID#" cfsqltype="cf_sql_integer" list="true"> 

) 

</cfquery>







<cfquery name="UpdateDocumentInsertNewSubTopics" datasource="#ds#"> 

INSERT INTO tbl_Document_Has_Topic 

                ( 

        DocumentID 

        ,  DocumentSubTopicID 

        )

SELECT   <cfqueryparam value="#form.DocumentID#" cfsqltype="cf_sql_integer"> 

         , st.DocumentSubtopicID 

         FROM  tbl_CEP_Document_Subtopic st

WHERE   st.DocumentSubTopicID  IN 

( 

<cfqueryparam value="#allSubTopicID#" cfsqltype="cf_sql_integer" list="true"> 

) 

</cfquery>

</cftransaction>

Open in new window

0
 
LVL 39

Expert Comment

by:gdemaria
ID: 33822235
The val() function returns a number from a string.  It reads the string up until the first non-integer and returns the numeric value

#val("123ABC")#  returns  123
#val("ABC")#  returns  0
#val("1A2B3C")#  returns  1
#val("ABC123")#  returns  0

And here's the important one...

#val("1,2,3,4")#  returns  1

So you don't want to use val() when the value is a list because it will chop it off at the comma


>  So, here is where I am stuck. My query to update the Subtopics (attached below) breaks ColdFusion, because CF says the value of allSubTopicID is not an integer.

Can you guess what I'm going to suggest?

You're puzzled by the fact that allSubTopicID  does not seem to be a valid integer, well, what exactly is the value of allSubTopicID ?   Once you see it, you will probably understand why it's not a valid integer.   I don't know the answer either, I suspect it would be a list of integers such as  12,43,82,110.    Let's find out..

<cfoutput>Subtopic ID is [#allSubTopicID#]</cfoutput>
<cfabort>

0
 
LVL 39

Expert Comment

by:gdemaria
ID: 33822457
The val() function returns a number from a string.  It reads the string up until the first non-integer and returns the numeric value

#val("123ABC")#  returns  123
#val("ABC")#  returns  0
#val("1A2B3C")#  returns  1
#val("ABC123")#  returns  0

And here's the important one...

#val("1,2,3,4")#  returns  1

So you don't want to use val() when the value is a list because it will chop it off at the comma


>  So, here is where I am stuck. My query to update the Subtopics (attached below) breaks ColdFusion, because CF says the value of allSubTopicID is not an integer.

Can you guess what I'm going to suggest?

You're puzzled by the fact that allSubTopicID  does not seem to be a valid integer, well, what exactly is the value of allSubTopicID ?   Once you see it, you will probably understand why it's not a valid integer.   I don't know the answer either, I suspect it would be a list of integers such as  12,43,82,110.    Let's find out..

<cfoutput>Subtopic ID is [#allSubTopicID#]</cfoutput>
<cfabort>

0
 
LVL 39

Expert Comment

by:gdemaria
ID: 33822466
weird, it double posted.  Unintentional, sorry.
0
 
LVL 3

Author Comment

by:Eric Bourland
ID: 33822521
>>>So you don't want to use val() when the value is a list because it will chop it off at the comma

That makes sense. I was thinking something like that was going on.

>>>Can you guess what I'm going to suggest?

Yes! I was expecting something like that. =) And I so have checked the value of #allSubTopicID#.

It is undefined!

When I do:

<cfoutput>Subtopic ID is [#allSubTopicID#]</cfoutput>
<cfabort>

or even if I use the scope of the query:

<cfoutput>Subtopic ID is [#UpdateDocumentInsertNewSubTopics.allSubTopicID#]</cfoutput>
<cfabort>

I get this message:

Element ALLSUBTOPICID is undefined in UPDATEDOCUMENTINSERTNEWSUBTOPICS.  
 
The error occurred in C:\websites\ebwebwork.com\cep\admin\insert_update_test.cfm: line 671
 
669 : </select>
670 :
671 : <cfoutput>Subtopic ID is [#UpdateDocumentInsertNewSubTopics.allSubTopicID#]</cfoutput>
672 : <cfabort>
673 :
 
Hmmm. Yet, we have defined the variable allSubTopicID in the scope of the query UpdateDocumentInsertNewSubTopics.

The undefined variable is probably causing the problem with selecting the subtopics. I don't see why ColdFusion things allSubTopicID  is undefined?
0
 
LVL 39

Expert Comment

by:gdemaria
ID: 33822646
> It is undefined!

Do you mean undefined or empty?  

It is defined to be empty right here in the 2nd line below...

 <cfset allTopicID = "">
 <cfset allSubTopicID = "">
 <cfloop index="aSet" list="#form.selectDocumentTopics#">
    <cfif listLen(aSet,":") gt 1>
        <cfset allSubTopicID =  ListAppend(allSubTopicID ,  listLast(aSet,":"))>
    <cfelse>
       <cfset allTopicID =  ListAppend(allTopicID ,  listFirst(aSet,":"))>
    </cfif>
</cfloop>

Now you want to see what value it has...
<cfoutput>Subtopic ID is [#allSubTopicID#]</cfoutput>
<cfabort>






>  or even if I use the scope of the query:
>  <cfoutput>Subtopic ID is [#UpdateDocumentInsertNewSubTopics.allSubTopicID#]</cfoutput>

Hmmm, not sure where that is coming from.   AllSubTopicID is a simple variable.   The scope would be "variable" as in variable.allSubTopicID.     Although you USE the variable inside of an insert CFQUERY, that doesn't mean it's a column in a SELET statement that you can scope with the query name.  That just doesn't apply...

0
 
LVL 3

Author Comment

by:Eric Bourland
ID: 33822842
The only thing that is different about variable allSubTopicID is that, in its definition, it uses the listLast function:

<cfset allSubTopicID =  ListAppend(allSubTopicID ,  listLast(aSet,":"))>
0
 
LVL 39

Expert Comment

by:gdemaria
ID: 33823140
The value for your topic SELET tag looks something like this...

#form.selectDocumentTopics# =  1:,6:1,12:11,24:,16:21,6:6,6:,6:4

The CFLOOP will loop the comma delimited list..

  <cfloop index="aSet" list="#form.selectDocumentTopics#">

That mans the variable aSet will be ...

 1:    -- for the first loop

 6:1   --- for the 2nd loop

 12:11

 etc..


Now we have to split up the two values of aSet  

     <cfif listLen(aSet,":") gt 1>
         this will handle values such as 6:1 and 12:11 that have two values -
         listLast() will take the last value in a list, the delimiter is ":"  
         For 6:1 the last value is 1 for 12:11 the last value is 11.   This will add 1 and 11 to the subtopics ID list...
         <cfset allSubTopicID =  ListAppend(allSubTopicID ,  listLast(aSet,":"))>
    <cfelse>
         this will handle aSet when it has only one value such as 1:   or  24:  
         this will add the first value from the list, for 1: it will add 1 and for 24: it will add 24....
       <cfset allTopicID =  ListAppend(allTopicID ,  listFirst(aSet,":"))>
    </cfif>


After the loop, let's see what the result is....

<cfoutput>Subtopic ID is [#variables.allSubTopicID#]</cfoutput>
<cfabort>


It should be a comma delimited list of subtopic ids such as 1,11,24,21....

0
 
LVL 3

Author Comment

by:Eric Bourland
ID: 33824120
This logic

     <cfif listLen(aSet,":") gt 1>
         this will handle values such as 6:1 and 12:11 that have two values -
         listLast() will take the last value in a list, the delimiter is ":"  
         For 6:1 the last value is 1 for 12:11 the last value is 11.   This will add 1 and 11 to the subtopics ID list...
         <cfset allSubTopicID =  ListAppend(allSubTopicID ,  listLast(aSet,":"))>
    <cfelse>
         this will handle aSet when it has only one value such as 1:   or  24:  
         this will add the first value from the list, for 1: it will add 1 and for 24: it will add 24....
       <cfset allTopicID =  ListAppend(allTopicID ,  listFirst(aSet,":"))>
    </cfif>

makes sense to me, and explains what you did here:

 <cfset allTopicID = "">
 <cfset allSubTopicID = "">
 <cfloop index="aSet" list="#form.selectDocumentTopics#">
    <cfif listLen(aSet,":") gt 1>
        <cfset allSubTopicID =  ListAppend(allSubTopicID , listLast(aSet,":"))>
    <cfelse>
       <cfset allTopicID =  ListAppend(allTopicID , listFirst(aSet,":"))>
    </cfif>
</cfloop>


hmm. Even when I put

  <cfoutput>Subtopic ID is [#variables.allSubTopicID#]</cfoutput>
<cfabort>

at the very top of the HTML page, I get the allSubTopicID undefined error.

I puzzling about this.
0
 
LVL 3

Author Comment

by:Eric Bourland
ID: 33824249
I tried an experiment. I stripped away all of the extraneous code, to test just the CFLOOP and the allSubTopicID, above. You can see the code below, and the output here:

http://ebwebwork.com/cep/admin/insert_update_test_AllSubTopic.cfm?DocumentID=157

The output is:

Subtopic ID is []

So, Subtopic ID is empty.
<!--- Set datasource --->

 <cfset ds="ebwebwork">



 <!--- Set default value for SelectDocumentTopics in scope FORM --->

<cfparam name="form.SelectDocumentTopics" default="0">



 <!--- Set default value for DocumentID in scope URL --->

<cfparam name="URL.DocumentID" default="">



 <!--- Define DocumentID in scope FORM, then set form.DocumentID equal to the DocumentID passed in the URL: for use later in the application --->

<cfparam name="form.DocumentID" default="#URL.DocumentID#">  





<!--- query getTopics requests values from columns in tbl_CEP_Document_Topic and tbl_CEP_Document_Subtopic, for use later in the insert_update.cfm template --->



<cfquery datasource="#ds#" name="getTopics">

           SELECT tp.DocumentTopicID

                  , tp.DocumentTopic

                  , sub.DocumentSubTopicID

                  , sub.DocumentSubTopicTitle

           FROM tbl_CEP_Document_Topic tp

           LEFT JOIN tbl_CEP_Document_Subtopic sub

           ON sub.DocumentTopicID = tp.DocumentTopicID

           ORDER BY tp.DocumentTopic, sub.DocumentSubTopicTitle

</cfquery>



 

 <cfset allTopicID = "">

 <cfset allSubTopicID = "">

 <cfloop index="aSet" list="#form.selectDocumentTopics#">

    <cfif listLen(aSet,":") gt 1>

        <cfset allSubTopicID =  ListAppend(allSubTopicID , listLast(aSet,":"))>

    <cfelse>

       <cfset allTopicID =  ListAppend(allTopicID , listFirst(aSet,":"))>

    </cfif>

</cfloop>







<!--- BEGIN HTML / CSS PAGE HEADER --->

<cfinclude template="/cep/cep_header_admin.cfm" />



   

<!--- this query requests existing topics and subtopics for the current documentID --->

       <cfquery datasource="#ds#" name="GetSelectedTopics">

              SELECT DocumentTopicID

              , DocumentSubTopicID

              , cast(DocumentTopicID AS varchar) + ':' + isNull(cast(DocumentSubTopicID AS varchar),'') AS JoinedID

              FROM    tbl_Document_Has_Topic

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

       </cfquery>

     

<!--- convert the DocumentTopicID, DocumentSubTopicID values to a list --->

      <cfset form.SelectDocumentTopics = ValueList(GetSelectedTopics.JoinedID)>





<!--- This select menu lists Topics and Subtopics; if topics and subtopics were already selected for form.documentID, --->

<!---  then display them in select menu using query GetSelectedTopics --->

<!--- query getTopics requests for Topics and Subtopics --->



<select name="SelectDocumentTopics" size="15" multiple="yes"> 



<!--- get Topics --->

 <cfoutput query="getTopics" group="DocumentTopic"> 

  

  <option value="#getTopics.DocumentTopicID#"

  <cfif listFind(form.SelectDocumentTopics,"#getTopics.DocumentTopicID#:")>selected='selected' </cfif>>

  #getTopics.DocumentTopic#

   </option>





  <cfoutput> 

    <!---- get subtopics if they exist ----> 

    <cfif val(getTopics.DocumentSubTopicID)> 

    <!---- use HTML non-breaking spaces to indent subtopics ----> 

  <option value="#getTopics.DocumentTopicID#"

  <cfif listFind(form.SelectDocumentTopics,"#getTopics.DocumentTopicID#:#getTopics.DocumentSubTopicID#")>

   selected='selected' </cfif>>

&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;#getTopics.DocumentSubTopicTitle#

   </option>

  

   

    </cfif> 





  </cfoutput> 



</cfoutput> 

</select>





<cfoutput>Subtopic ID is [#allSubTopicID#]</cfoutput>

<cfabort>







<!--- Page footer --->

<cfinclude template="/cep/cep_footer.cfm" />

Open in new window

0
 
LVL 39