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/cf-talk@houseoffusion.com/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
