You could do this in CF by first querying the zap table, and then looping
over those query results and issuing a query against advert_details for each
search term - but I wouldn't do that! This is the type of thing that you
should get the database to do for you.
There are a couple of ways to do this, both of which are fairly complicated.
One method would be to use a cursor to loop through the zap table. I'm no
expert on SQL performance, but I've been led to believe that cursors should
be avoided, so I'm not going to show you how to do that. If you really want
to know, just say so and I'll provide a sample.
The second method is a really neat trick that you can use to sort of emulate
the behaviour of a cursor. You can use it to loop through the records in a
table, just using standard SQL select statements.
This example works in SQL Server, I'm not sure how it would need to be
changed to work with other RDBMS's. Here's the code:
CREATE PROC CoolLoop
@AdId int -- The Ad Id for the most recently added ad (assumes it will be an
integer)
AS
DECLARE @zapsearch varchar(100) -- Used to store the value of zapsearch for
each row in turn
-- Get the first search term
SELECT @zapsearch = MIN(zapsearch)
FROM zap
-- Loop through the zap table
WHILE @zapsearch IS NOT NULL
BEGIN
-- check to see if the search term is included in the body column in the
advert_details table
-- could also search other columns (e.g., subject)
if exists (select * from advert_details where body like '%' + @zapsearch +
'%'
and adid = @AdId)
-- the term was found - do your dirty work here
-- this example will just return the value of zapEmail, in a real
application you'd
-- probably insert this value into a temp table, and then do a select
* on
that temp table
-- at the end of the proc
select zapEmail from zap where zapsearch = @zapsearch
-- get the next search term
SELECT @zapsearch = MIN(zapsearch)
FROM zap
WHERE zapsearch > @zapsearch
END
You'd call this by saying "Exec CoolLoop @AdId = {your ad id here}".
I'm not sure how much better (or worse) this would perform vs. a cursor, but
I think it's extremely cool!
Hope that helps, let me know if you have any questions,
Bob
-----Original Message-----
From: W Luke [mailto:[EMAIL PROTECTED]]
Sent: March 19, 2001 4:12 PM
To: CF-Talk
Subject: Query join problem
Hi,
Not sure what I need to do for this - hope someone can help.
After someone posts an advert, it's entered into the database table
advert_details. A table called zap contains 2 fields, zapSearch and
zapEmail, with zapSearch being a search string. After posting the advert, I
need to run through zapSearch to search for the text in the just-posted
advert. If it finds anything, send an email to zapEmail.
I just can't fathom how to do this. The fields in advert_details we're
worried about are body and subject. I've had a go, but this is out of my
depth. Can anyone help?
Thanks
Will
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Structure your ColdFusion code with Fusebox. Get the official book at
http://www.fusionauthority.com/bkinfo.cfm
Archives: http://www.mail-archive.com/[email protected]/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
