10 Replies Latest reply on Apr 23, 2009 12:14 PM by Lee Crockett

    Comparing a list with a query output

    Lee Crockett

      I may just be really tired, but I'm just not getting this.  What I'm trying to accomplish is to output a query, and as it's outputting, check a list to see if a value exists, and if so, change the class of a <li>

       

      I have two queries and am defining a list from one:

       

       

      <cfquery name="rsTags" datasource="cfsfwer">

          SELECT tags.tagName, tags.tagsID

          FROM tags, blogTagLink

          WHERE blogTagLink.blogID = <cfqueryparam value="#SESSION.blogID#" cfsqltype="cf_sql_numeric">

          AND tags.tagsID  = blogTagLink.tagID

      </cfquery>

       

      <cfset selectedTags = ValueList(rsTags.tagsID)>

       

      <cfquery name="rsAllTags" datasource="cfsfwer">

          SELECT *

          FROM tags

          ORDER BY tags.tagName

      </cfquery>

       

       

      So far this works just fine.  The output of #selectedTags# is correct.  Now I want to output #rsAllTags# and check to see if the record is ALSO in the #selectedTags# list, and if it is, change the class on the output, like this

       

      <ul>

      <cfoutput query="rsAllTags">

       

      <cfset temp = ListFind(selectedTags, #rsAllTags.tagsID#)>

           <cfif temp EQ 0>

                 <li class="tagnotselected""><a>#rsAllTags.tagName#</a></li>

           <cfelse>

                 <li class="tagselected""><a>#rsAllTags.tagName#</a></li>

           </cfif>

      </cfoutput>

      </ul>

       

      It doesn't work, and it is related to the variable #rsAllTags.tagsID#.  If I use a static value for this, it works fine.  Obviously, I need a dynamic value to deal with the output query.

       

      Any help would be appreciated.

        • 1. Re: Comparing a list with a query output
          Dan Bracuk Level 5

          If your db supports it, this type of query will give you exactly what you want and simplify your output.

           

          select tagid, tagname,

          case when x.tag_id is not null then 'tagselected' else 'tagnotselected' end included

          from tags left join (

          select tagid

          from tagblocklink

          where tagid = = <cfqueryparam value="#SESSION.blogID#" cfsqltype="cf_sql_numeric">

          ) x on tags.tagid = x.tagid

          order by tagname

           

          The output should require no if/else logic

          • 2. Re: Comparing a list with a query output
            Lee Crockett Level 1

            I'm using MYSQL, and I am not sure.  Thanks for putting this up, but, honestly that answer is a little over my head.  I'm struggling to follow it

            • 3. Re: Comparing a list with a query output
              Dan Bracuk Level 5

              run it and dump it.   make sure you delete the extra equal sign first

              • 4. Re: Comparing a list with a query output
                ilssac Level 5

                You need use the valuelist() function around your query.column to convert the record set into a list before you can search it.

                 

                First try it witout the pound signs, those are unnecessary.

                 

                <cfset temp = ListFind(selectedTags, rsAllTags.tagsID)>

                 

                And then it is possible that you are not fully referencing the query record set and so you are only getting the first row for each iteration.  If so try this.

                 

                <cfset temp = ListFind(selectedTags, rsAllTags.tagsID[rsAllTags.currentRow])>

                • 5. Re: Comparing a list with a query output
                  Lee Crockett Level 1

                  Thanks Dan:

                   

                  I get errors when trying to run this.  The first is:

                   

                  Unknown column 'x.tag_id' in 'field list'

                   

                  I know that this is the right approach, just not sure how to get it 

                  working.

                   

                  This is what I'm running:

                   

                  ) x on tags.tagid = x.tagid

                  order by tagname

                  </cfquery

                  • 6. Re: Comparing a list with a query output
                    Lee Crockett Level 1

                    Thanks Ian:

                     

                    I tried both of those and neither works.  The problem is definitely 

                    related to the current row for me because when I output the variable 

                    #temp#, I am getting a list of results.  This tells me that it is 

                    running all the instances of rsAllTags.tagsID and not just the current 

                    one.  I don't know how to get around it.  I thought that your last 

                    suggestion:

                     

                    <cfset temp = ListFind(selectedTags, 

                    rsAllTags.tagsID[rsAllTags.currentRow])>

                     

                    would work, but it gives the same output.

                    • 7. Re: Comparing a list with a query output
                      ilssac Level 5

                      I would then output each part of the function and the function results for each itteration, or a sub-set of itterations if it is a large result set.

                       

                      Check that each part and the results are as expected.

                      • 8. Re: Comparing a list with a query output
                        Lee Crockett Level 1

                        Dan:

                         

                        Thank you so much for your expert help.  After working at this for a

                        while, I understood what was going on, made some corrections and got

                        it to work.  It is obvious to me that I need to spend more time

                        learning SQL!.  Thank you for taking the time to help.  Here is the

                        final code:

                         

                        <cfquery name="rsNewTags" datasource="committedsardine">

                        select tagid, tagname,

                        case when x.tagid is not null then 'tagselected' else 'tagnotselected' end included

                        from tags left join (

                        select tagID

                        from blogTagLink

                        where blogID = <cfqueryparam value="#SESSION.blogID#" cfsqltype="cf_sql_numeric">

                        ) x on tags.tagsID = x.tagid

                        order by tagname

                        </cfquery>

                         

                        The cfdump for rsNewTags is this:

                         

                                INCLUDED           TAGID           TAGNAME

                        1      tagnotselected           empty string assessment

                        2      tagselected           5                brain

                        3      tagnotselected           empty string collaboration

                        4      tagselected           10                digital

                        5      tagnotselected           empty string digital diet

                        6      tagnotselected           empty string evaluation

                        7      tagselected           4                exponential

                        8      tagselected           6                fluency

                        9      tagnotselected           empty string global

                        10      tagnotselected           empty string hardware

                         

                         

                        And the conditional statement is now this:

                         

                         

                        <cfoutput query="rsNewTags">

                        <cfif rsNewTags.included EQ "tagselected">

                        <li class="tagselected"><a>#rsNewTags.tagName#</a></li>

                        <cfelse>

                        <li class="tagnotselected"><a>#rsNewTags.tagName#</a></li>

                        </cfif>

                        </cfoutput>

                                 
                        • 9. Re: Comparing a list with a query output
                          Dan Bracuk Level 5

                          Regarding

                          And the conditional statement is now this:

                           

                           

                          <cfoutput query="rsNewTags">

                          <cfif rsNewTags.included EQ "tagselected">

                          <li class="tagselected"><a>#rsNewTags.tagName#</a></li>

                          <cfelse>

                          <li class="tagnotselected"><a>#rsNewTags.tagName#</a></li>

                          </cfif>

                          </cfoutput>

                           

                          too much work.  all you need is

                          <li class="#something from your query#"><a>#rsNewTags.tagName#</a></li>

                          1 person found this helpful
                          • 10. Re: Comparing a list with a query output
                            Lee Crockett Level 1

                            Wow, right you are.  I totally get it now.  Thanks so much, works like 

                            a charm.