OK, it's late and I'm not operating at 100%. Looking at this again, you
could rewrite the whole thing into one query that joins the payments,
invoices and advert_details together. Then use CFOUTPUT with the GROUP
attribute to generate as many (or as few) emails and updates as you like.
At least I think the update query I gave you was correct.
Bob
-----Original Message-----
From: Bob Silverberg [mailto:[EMAIL PROTECTED]]
Sent: November 30, 2000 6:32 PM
To: [EMAIL PROTECTED]
Subject: RE: Advanced queries
Without any report of error messages it's hard to say if there's anything
wrong with your code. It looks like it _could_ run OK. Did you try it? I
think you may want to move the CFMAIL tag inside of the query loop, but I'm
not totally sure of what you're trying to do. You also may want to scope
your variables in the second query with the query name (i.e., invoices.ad_ID
and invoices.email).
To set the sent column to yes, include the following in your loop:
<Cfquery name="update_invoice" datasource="localads">
UPDATE invoices
SET sent = Yes
WHERE WHERE ID = #invoices.ad_ID# AND email = '#invoices.email#'
</cfquery>
I'm assuming you're using Access, and therefore the yes and no without
quotes should be ok.
Bob
-----Original Message-----
From: W Luke [mailto:[EMAIL PROTECTED]]
Sent: November 30, 2000 12:15 PM
To: CF-Talk
Subject: Advanced queries
Hi,
I wonder if someone could help me out with some queries I'm running on a
scheduled task.
I need to send out outstanding invoices, including details of what they have
bought (i.e. an Advert - details of which are in advert_details). Once
done, I need to set the "Sent" column of the Invoices to "Yes". It involved
3 tables - invoices, payments and advert_details.
Below is what I've done - but I'm pretty sure I've done it wrong, and I'm
not sure where or how to set the invoices.sent to "Yes".
<Cfquery name="invoices" datasource="localads">
SELECT email,ad_ID,advert_ID,paid,sent
FROM payments,invoices
WHERE paid = Yes AND sent = No AND ad_ID = advert_ID
</cfquery>
<Cfif #invoices.recordcount# LT 1>
There are no outstanding INVOICES TO BE PAID
<Cfelse>
<Cfoutput query="invoices">
<cfquery name="getuser" datasource="localads">
SELECT * FROM advert_details WHERE ID = #ad_ID# AND email = '#email#'
</cfquery>
</cfoutput>
<cfmail query="getuser" to="#email#">
<!--- send out invoice --->
</cfmail>
There are <cfoutput>#invoices.recordcount# invoices outstanding</cfoutput>
</cfif>
I'd be grateful for anyone's wisdom with advanced querying on this..
Cheers
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
