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.

[Dan_Bracuk · Accepted solution]

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

[Lee_Crockett]

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

[Dan_Bracuk]

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

[Lee_Crockett]

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:

<cfquery name="rsTags" datasource="committedsardine"> select tagid, tagname, case when x.tag_id is not null then 'tagselected' else 'tagnotselected' end included from tags left join ( select tagid from blogTagLink where tagid = <cfqueryparam value="#SESSION.blogID#" cfsqltype="cf_sql_numeric">) x on tags.tagid = x.tagid

order by tagname

</cfquery

[Lee_Crockett]

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>

[Dan_Bracuk]

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>

[Lee_Crockett]

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

a charm.

[ilssac]

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])>

[Lee_Crockett]

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.

[ilssac]

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.