Skip to main content
Known Participant
April 23, 2009
Answered

Comparing a list with a query output

  • April 23, 2009
  • 2 replies
  • 1319 views

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.

This topic has been closed for replies.
Correct answer Dan_Bracuk

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 replies

ilssac
Inspiring
April 23, 2009

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

Known Participant
April 23, 2009

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
Inspiring
April 23, 2009

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.

Dan_BracukCorrect answer
Inspiring
April 23, 2009

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

Known Participant
April 23, 2009

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

Inspiring
April 23, 2009

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