How to Highlight a matched word ?

[marcovdo]

Hi guys,

I want to highlight any word in my text that matches a word that is in a database table. So if in the table words are: sun / web / ghost then i want in the text these words highlighted.

Now only the top record is matching so in this example sun.
How can i change the code so that any word in the table sun / web / ghost matches with the text ?

CODE:

cfquery:
---------------------------------------------------------
<cfquery name="zo" datasource="spirilog">
SELECT *
FROM onderwerpen
ORDER BY Id DESC</cfquery>
---------------------------------------------------------

text formatting:

---------------------------------------------------------
<cfset MessageComments = "#Replace(MessageComments, '#zo.titel#', '<a href="index.cfm?id=#zo.id#&spot=#zo.echtenaam#" title="Bekijk en/of bewerk de definitie van #zo.titel#..."><span class="style3">#zo.titel#</span></a>', 'ALL')#">
---------------------------------------------------------

I know that i have to change the query in something like:

<cfquery name="zo" datasource="spirilog">
SELECT *
FROM onderwerpen where titel (the word that have to match with the text) = like ??? (any matched word in the text)
ORDER BY Id DESC</cfquery>

Can anyone help ?

 

[r937]

approach: use the FIND_IN_SET function to find each word from the word table in the titel, after substituting commas for spaces, thus making titel a "set" (comma-separated list of words)

SELECT onderwerpen.titel
     , sun_web_ghost_table.theword
  FROM onderwerpen
inner
  join sun_web_ghost_table
    on find_in_set(sun_web_ghost_table.theword
            ,replace(onderwerpen.titel,' ',','))

r937.com | rudy.ca

 

[r937]

uh oh, wait a sec, that will only work in mysql :-(

r937.com | rudy.ca

 

[r937]

what database system are you using?

r937.com | rudy.ca

 

[FALCONSEYE]

you would say
<cfset valuesToMatch = 'sun,ghost,...'>

<cfquery name="zo" datasource="spirilog">
SELECT *
FROM onderwerpen
where titel (the word that have to match with the text) IN
(<cfqueryparam list="yes" value="#valuesToMatch#">)
ORDER BY Id DESC
</cfquery>


hope it helps

http://www.jetansolutions.com

 

[marcovdo]

Hi guys, thanx for your help.

I'm using access (yeah i know) right now, sorry i didn't told that, quite important huh?

I tried the code of falconseye but stil only get the first value (sun) to match in the text. I tried to replace sun,ghost with #artikel.tekst# (the value of the text where the words have to hihglight in) but then i get an error about an invalid precision value?

So i have to change the :

<cfset valuesToMatch = 'sun,ghost,...'>

to:

<cfset valuesToMatch = (divide this in words: #artikel.tekst#>

and stil i also only get the top value matched (sun)

Thanx in advance...

 

[r937]

SELECT onderwerpen.titel
     , sun_web_ghost_table.theword
  FROM onderwerpen
inner
  join sun_web_ghost_table
    on [b]instr[/b](sun_web_ghost_table.theword
            ,onderwerpen.titel)

r937.com | rudy.ca

 

[marcovdo]

Hi r937,

Is that for access ? I get all sort of errors...
What to do with table.theword ?

Can i replace sun_web_ghost for #artikel.tekst#

 

[r937]

yes, INSTR is for microsoft access

perhaps if you show your actual table names, and a couple of sample rows from each, i could help you write the sql using your names rather than the ones i made up

r937.com | rudy.ca

 

[marcovdo]

Hi again, thanx for helping me out here...


So table artikel displays the text and with table zok i want to match words that are in column titel of zok and in column tekst of table artikel

<cfquery name="artikel" datasource="spirilog">
SELECT * FROM onderwerpen WHERE Id = #URL.Id#
</cfquery>


<cfquery name="zok" datasource="spirilog">
SELECT onderwerpen.titel
     , sun_web_ghost_table.theword
  FROM onderwerpen
inner
  join sun_web_ghost_table
    on instr(sun_web_ghost_table.theword
            ,onderwerpen.titel)
</cfquery>


<cfsetting enablecfoutputonly="Yes">
    <cfset MessageComments = #artikel.tekst#>
    <!--- Check for HTML and replace '<' and '>' --->
    <cfset MessageComments = "#Replace(MessageComments, '>', '&gt;','ALL')#">
    <cfset MessageComments = "#Replace(MessageComments, '>', '&gt;','ALL')#">
	<!--- Format the users message. --->
	<cfset MessageComments = "#Replace(MessageComments, '#zok.titel#', '<a href="index.cfm?id=#zok.id#&spot=#zok.echtenaam#" title="Bekijk en/of bewerk de definitie van #zok.titel#..."><span class="style3">#zok.titel#</span></a>', 'ALL')#">
	<cfset MessageComments = "#Replace(MessageComments, chr(10), '<br>','ALL')#">
    <cfset MessageComments = "#Replace(MessageComments, chr(9), '&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;', 'ALL')#">
    <cfset MessageComments = "#Replace(MessageComments, '  ' , '&nbsp;&nbsp;', 'ALL')#">
    <cfoutput>#Left(ParagraphFormat(MessageComments),10000000)#

I hope it's clear like this, thanx in advance!

 

[r937]

so table zok has columns
- id
- titel
- echtenaam

what about this --

title="Bekijk en/of bewerk de definitie van #zo.titel#..."


and let me also ask you whether you are looking to have the REPLACE carried out in the query (which was my first guess), or whether you have the zok data in coldfusion and want to do the REPLACE done in coldfusion

r937.com | rudy.ca

 

[marcovdo]

Hi it's hard for me to explain it correctly sorry for that,

In coldfusion i want to format artikel.tekst from the query artikel.

<cfset MessageComments = "#Replace(MessageComments, '#zok.titel#', '<a href="index.cfm?id=#zok.id#&spot=#zok.echtenaam#" title="Bekijk en/of bewerk de definitie van #zok.titel#...">

In query zok i have titles under the column titel, ik want to compare all the titles with the query artikel artikel.tekst output wich is the text output. If any title is in the text i want to replace that matched word with a link and a highlight.

that's about the thing a want to do...

hope this is understandable enough

 

[marcovdo]

Ok maybe it's getting more complicated then it is,

All i want to know is this:

Select everything from onderwerpen where titel has a match with anything from the #artikel.tekst# (large text) output, what's the code for that?

I tried the below code at first, but ofcourse like this i never have a match because in #onderwerpen.titel# there are single words that don't match with the large text output from #artikel.tekst#...

<cfquery name="zok" datasource="spirilog">
SELECT *
FROM onderwerpen where titel LIKE '%#artikel.tekst#%'
ORDER BY Id DESC
</cfquery>

It's no problem for me to include the whole page but then i need a emailadres or can i include the whole page here?

 

[marcovdo]

Ok i've got it, once again thinking way to complex

In the beginning i told that it was working but that only the first record of the column with the match words went highlighted, that was with this line:

<cfset MessageComments = "#Replace(MessageComments, '#zok.titel#', '<a href="index.cfm?id=#zok.id#&spot=#zok.echtenaam#" title="Bekijk en/of bewerk de definitie van #zok.titel#..."><span class="style3">#zok.titel#</span></a>', 'ALL')#">

You always need a repeat region if you want to display or trigger more results, so i changed it simpy into:

<cfoutput query="zok">

<cfset MessageComments = "#Replace(MessageComments, '#zok.titel#', '<a href="index.cfm?id=#zok.id#&spot=#zok.echtenaam#" title="Bekijk en/of bewerk de definitie van #zok.titel#..."><span class="style3">#zok.titel#</span></a>', 'ALL')#">

</cfoutput>

Stupid, stupid, stupid, i am ashamed for myself...