Solved

How do you check to see if query results contain a string?

Posted on 2008-10-02
1
252 Views
Last Modified: 2012-05-05
I have two queries; the first one simply returns a list (of, say, all letters of the alphabet) and the second  returns a varying number of letters. What I want to do is print out the results of the first query highlighting the ones that are contained in the second query. I'm guessing it would be done something like the following:

<cfloop query="qAlphabet">
   <cfif [qSelectLetters contains qAlphabet.letter]><b></cfif>
   #qAlphabet.letter#
   <cfif [qSelectLetters contains qAlphabet.letter]></b><cfif>
   <br/>
</cfloop>

But I'm unsure of how to phrase the part in the brackets. I've done it by setting a string (looping through query 2, to give something like "a,c,g,w" and seeing if that string contains qAlphabet.letter, but it seems like that would be excessively huge if the queries were involving blocks of text, not just letters.
0
Comment
Question by:SiobhanElara
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
1 Comment
 
LVL 15

Accepted Solution

by:
danrosenthal earned 250 total points
ID: 22629267
The syntax for your example would be something along the lines of:

<cfif LISTFIND(valuelist(qAlphabet.letter),qSelectLetters)>

But, you are correct in that it wouldn't be wise to use that solution for larger resultsets.

I would recommend doing the comparing at the SQL level. Either in the original query using a LEFT JOIN, or by using a query of queries.

Rough example of how you would use a LEFT JOIN to acheive the desired result:

SELECT
   a.value
   , CASE WHEN b.joinID IS NOT NULL THEN 1 ELSE 0 END as isrecordfound
FROM allrecordtable a
LEFT JOIN myvaluetable b
ON a.joinID = b.joinID

And your CFIF inside the output loop would be simply:

<cfif isrecordfound eq 1>


0

Featured Post

[Webinar] Code, Load, and Grow

Managing multiple websites, servers, applications, and security on a daily basis? Join us for a webinar on May 25th to learn how to simplify administration and management of virtual hosts for IT admins, create a secure environment, and deploy code more effectively and frequently.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

PROBLEM:  How to open a cfwindow or run a function on double click of a cfgrid row. One of my clients wanted to be able to double click on a row item to get more detailed information about a transaction and to be able to modify the line items i…
PROBLEM: How to add your own buttons to the bottom toolbar with paging info ( result count ). While creating a cfgrid, I ran into an issue where I wanted to embed my own custom buttons where the default ones ( insert / delete / etc… ) are for aes…
Are you ready to implement Active Directory best practices without reading 300+ pages? You're in luck. In this webinar hosted by Skyport Systems, you gain insight into Microsoft's latest comprehensive guide, with tips on the best and easiest way…

738 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question