Skip to main content
J
jlig
Inspiring
March 1, 2012
Answered

Cancel Email Sending (or processing) if Recordset is Blank?

  • March 1, 2012
  • 2 replies
  • 1389 views

I have the code below which works great to send an email when a new record has been added to the tickets table. (email code is not listed)

- The table column called "sent_email" starts with a default of 0

------------------------------------------

<cfquery name="rsSendEmail" datasource="loc">

SELECT    tickets.ticketID, tickets.sent_email, tblusers.username, tblusers.username AS OwnName, tickets.ticket, tickets.original_date, tickets.work_date, tickets.priority, tickets.type, tickets.started, tickets.name, tickets.caller, tickets.contact, tickets.contact_phone, tickets.county, tickets.place, tickets.location, tickets.work_type, tickets.extent, tickets.remarks, tblusers.email, tblusers.firstName AS fn, tblusers.lastName, tblstatus.stDesc, tickets.street

FROM      tblstatus RIGHT OUTER JOIN tickets ON tblstatus.stID = tickets.statusID LEFT OUTER JOIN tblusers ON tblusers.ID = tickets.ownerID

WHERE  tickets.sent_email = "0"

LIMIT 1

</cfquery>

-----------------------------------------

- Once the email goes out, I run this code to update "sent_email" to a 1

-----------------------------------------

<cfquery name="rsSetSentFlag" datasource="loc">

UPDATE   tickets

SET tickets.sent_email = "1"

WHERE  tickets.sent_email = "0"

LIMIT 1

</cfquery>

------------------------------------------

I get the first email correctly, but then I keep getting emails with no data, that are blank?

I think I need to "Stop Processing" the rest of the page if the Select Query returns no records.

I tried to add this code below after the query, but it stops at the <cfabort line..

          <cfif rsSendEmail.recordCount NEQ 0>

                    <cfabort showerror="">

                    <!--- Processing is stopped, --->

                    <!--- and subsequent operations are not carried out.--->

          </cfif>

    This topic has been closed for replies.
    Correct answer Leonard_B

    Hello,

    Give something like this a try.

    Run your query and then use <cfif></cfif> to run your cfmail process.

    This should only run your cfmail process if there is a recordcount, otherwise nothing should happen.

    <cfquery name="rsSendEmail" datasource="loc">

    SELECT    tickets.ticketID, tickets.sent_email, tblusers.username, tblusers.username AS OwnName, tickets.ticket, tickets.original_date, tickets.work_date, tickets.priority, tickets.type, tickets.started, tickets.name, tickets.caller, tickets.contact, tickets.contact_phone, tickets.county, tickets.place, tickets.location, tickets.work_type, tickets.extent, tickets.remarks, tblusers.email, tblusers.firstName AS fn, tblusers.lastName, tblstatus.stDesc, tickets.street

    FROM      tblstatus RIGHT OUTER JOIN tickets ON tblstatus.stID = tickets.statusID LEFT OUTER JOIN tblusers ON tblusers.ID = tickets.ownerID

    WHERE  tickets.sent_email = "0"

    LIMIT 1

    </cfquery>

    <cfif rsSendEmail.recordcount>

    <!--- Insert your code here --->

    </cfif>

    2 replies

    L
    Leonard_B
    Inspiring
    March 5, 2012

    Hello,

    Add a <cfloop> process inside your <cfif> like below. This should loop through your query

    results and if multiple records are present, process each one individually until all have

    been processed.

    <cfif rsSendEmail.recordcount>

    <cfloop query="rsSendEmail">

    <!--- Insert your code here --->

    </cfloop>

    </cfif>

    Leonard B

    L
    Leonard_BCorrect answer
    Inspiring
    March 2, 2012

    Hello,

    Give something like this a try.

    Run your query and then use <cfif></cfif> to run your cfmail process.

    This should only run your cfmail process if there is a recordcount, otherwise nothing should happen.

    <cfquery name="rsSendEmail" datasource="loc">

    SELECT    tickets.ticketID, tickets.sent_email, tblusers.username, tblusers.username AS OwnName, tickets.ticket, tickets.original_date, tickets.work_date, tickets.priority, tickets.type, tickets.started, tickets.name, tickets.caller, tickets.contact, tickets.contact_phone, tickets.county, tickets.place, tickets.location, tickets.work_type, tickets.extent, tickets.remarks, tblusers.email, tblusers.firstName AS fn, tblusers.lastName, tblstatus.stDesc, tickets.street

    FROM      tblstatus RIGHT OUTER JOIN tickets ON tblstatus.stID = tickets.statusID LEFT OUTER JOIN tblusers ON tblusers.ID = tickets.ownerID

    WHERE  tickets.sent_email = "0"

    LIMIT 1

    </cfquery>

    <cfif rsSendEmail.recordcount>

    <!--- Insert your code here --->

    </cfif>

    J
    jligAuthor
    Inspiring
    March 2, 2012

    Leonard, that did the trick!

    In review, I needed a way to get an automatic email when a new ticket is added to the table..

    - Throughout the day, tickets are emailed from a JULIE locating service, in XML attachment form

    - Using CFPOP I grab the XML attachment & add the ticket/details to the tickets table

    - The tickets.sent_email field is set to a 0

    - I then run an CF email scripting page via the CF Scheduler every minute

    - If my query above finds a ticket with a 0 in the sent_email field, it forwards out the ticket details in an email

    - It then sets the sent_email field to a 1

    I'm using the LIMIT function in the query to just work with 1 record at a time.

    Thanks again Leonard

    BKBK
    Community Expert
    BKBKCommunity Expert
    Community Expert
    March 3, 2012

    Just an aside: there is no need for a Limit clause in your update query.

    Terms & ConditionsAccessibility statement
    Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices