SELECT DISTINCT bEmail FROM zBikeBits ORDER BY bEmail SELECT bEmail FROM getData WHERE bEmail > '#LastEmailSent#' ORDER BY bEmail email sent in batch number #this#. [allowsmilie] => 1 [showsignature] => 0 [ipaddress] => [iconid] => 0 [visible] => 1 [attach] => 0 [infraction] => 0 [reportthreadid] => 0 [isusenetpost] => 1 [msgid] => [ref] => [htmlstate] => on_nl2br [postusername] => zoeski80 [ip] => zoe@webraven.co [isdeleted] => 0 [usergroupid] => [membergroupids] => [displaygroupid] => [password] => [passworddate] => [email] => [styleid] => [parentemail] => [homepage] => [icq] => [aim] => [yahoo] => [msn] => [skype] => [showvbcode] => [showbirthday] => [usertitle] => [customtitle] => [joindate] => [daysprune] => [lastvisit] => [lastactivity] => [lastpost] => [lastpostid] => [posts] => [reputation] => [reputationlevelid] => [timezoneoffset] => [pmpopup] => [avatarid] => [avatarrevision] => [profilepicrevision] => [sigpicrevision] => [options] => [akvbghsfs_optionsfield] => [birthday] => [birthday_search] => [maxposts] => [startofweek] => [referrerid] => [languageid] => [emailstamp] => [threadedmode] => [autosubscribe] => [pmtotal] => [pmunread] => [salt] => [ipoints] => [infractions] => [warnings] => [infractiongroupids] => [infractiongroupid] => [adminoptions] => [profilevisits] => [friendcount] => [friendreqcount] => [vmunreadcount] => [vmmoderatedcount] => [socgroupinvitecount] => [socgroupreqcount] => [pcunreadcount] => [pcmoderatedcount] => [gmmoderatedcount] => [assetposthash] => [fbuserid] => [fbjoindate] => [fbname] => [logintype] => [fbaccesstoken] => [newrepcount] => [vbseo_likes_in] => [vbseo_likes_out] => [vbseo_likes_unread] => [temp] => [field1] => [field2] => [field3] => [field4] => [field5] => [subfolders] => [pmfolders] => [buddylist] => [ignorelist] => [signature] => [searchprefs] => [rank] => [icontitle] => [iconpath] => [avatarpath] => [hascustomavatar] => 0 [avatardateline] => [avwidth] => [avheight] => [edit_userid] => [edit_username] => [edit_dateline] => [edit_reason] => [hashistory] => [pagetext_html] => [hasimages] => [signatureparsed] => [sighasimages] => [sigpic] => [sigpicdateline] => [sigpicwidth] => [sigpicheight] => [postcount] => 2 [islastshown] => [isfirstshown] => [attachments] => [allattachments] => ) --> Sending a Bulk Email from MySQL DB - Coldfusion Database Access

Sending a Bulk Email from MySQL DB - Coldfusion Database Access

I have a Bulk email that is sent to a lot of our members, opt in, sot spam. But recently our ISP has been having trouble sending it all at once and recommended sending bursts of 1000 instead of all of them at once. But I'm not sure how to write the code to send in bursts. The current code pulls all emails from the email field of a MySQL Database and sends to them. Is there a way to have it pull the first 1000 rows and send and then pull the next 1000 and send? How would this ...

  1. #1

    Default Sending a Bulk Email from MySQL DB

    I have a Bulk email that is sent to a lot of our members, opt in, sot spam.
    But recently our ISP has been having trouble sending it all at once and
    recommended sending bursts of 1000 instead of all of them at once. But I'm not
    sure how to write the code to send in bursts. The current code pulls all
    emails from the email field of a MySQL Database and sends to them. Is there a
    way to have it pull the first 1000 rows and send and then pull the next 1000
    and send? How would this be scripted? I am using coldfusion 5 currently. I
    attached the current code that pulls email addresses from the database.



    <cfquery datasource="#DataSource2#" name="getdata">
    SELECT DISTINCT bEmail
    FROM zBikeBits
    ORDER BY bEmail
    </cfquery>

    <cfmail query="getdata"
    from="My Name <bikebitsdomain.org>"
    to="#getdata.bEmail#"
    subject="Bike Bits Vol. 7, No. 4, April 1, 2005"
    server="out.going.com">

    lazerbrains555 Guest

  2. #2

    Default Re: Sending a Bulk Email from MySQL DB

    HI

    You could send the emails individually ie. w/o using the QUERY attribute on
    CFMAIL and then use CFLOOP wtih StartRow and EndRow to get 1000 rows at a time.

    Alternatively if you want to still use the QUERY attribute see attached. It
    works, not sure how reliably tho :)

    - Zoe


    <!--- get all email addresses --->
    <cfquery datasource="#request.globaldatasource#" name="getData">
    SELECT DISTINCT bEmail
    FROM zBikeBits
    ORDER BY bEmail
    </cfquery>

    <!--- how many emails do you want to send per loop? --->
    <CFSET maxEmails = 1000>

    <!--- initialise variable --->
    <CFSET LastEmailSent = ''>

    <!--- find out the number of loops required to send all emails --->
    <CFSET numberOfLoops = ceiling(getData.recordcount / maxEmails)>

    <!--- perform the number of loops required to send all emails in batches --->
    <CFLOOP FROM="1" TO="#numberOfLoops#" INDEX="this">

    <!--- get next X email addresses to send to --->
    <cfquery dbtype="query" name="getEmails" MAXROWS="#maxEmails#">
    SELECT bEmail
    FROM getData
    <CFIF Trim(LastEmailSent) NEQ ''>
    WHERE bEmail > '#LastEmailSent#'
    </CFIF>
    ORDER BY bEmail
    </cfquery>
    <CFDUMP VAR="#getEmails#">

    <!--- get the last email address from the query so we know where to start
    from next loop --->
    <CFSET LastEmailSent = getEmails["bEmail"][getEmails.RecordCount]>

    <!--- send emails to this batch --->
    <cfmail query="getEmails"
    from="My Name <bikebitsdomain.org>"
    to="#bEmail#"
    subject="Bike Bits Vol. 7, No. 4, April 1, 2005">
    email sent in batch number #this#.
    </CFMAIL>

    </CFLOOP>

    zoeski80 Guest

  3. #3

    Default Re: Sending a Bulk Email from MySQL DB

    You could also create a column in your table that flags which records have had
    an email sent, then create a page to only send to the first thousand records
    with a flag of false. Have your sql server open the page every hour or
    something. We do this for newsletters with some of our larger clients and it
    works out really well. Requires no effort on our part or theirs, other than
    creating the newsletter.

    tomrippity02 Guest

  4. #4

    Default Re: Sending a Bulk Email from MySQL DB

    So if I drop the CFQUERY and do it your way would it look like this? :

    <cfmail
    startrow="7402"
    endrow="8402"
    from="My Name <bikebitsdomain.org>"
    to="#getdata.bEmail#"
    subject="Bike Bits Vol. 7, No. 4, April 1, 2005"
    server="out.going.com">

    or am I missing something?


    lazerbrains555 Guest

  5. #5

    Default Re: Sending a Bulk Email from MySQL DB

    Hi

    I think what tomrippity02 means is to do a query on the first 1000 entries
    marked as not sent.
    <CFQUERY MAXROWS="1000">
    SELECT *
    FROM zBikeBits
    WHERE Sent = 0
    </CFQUERY>

    Then do your CFMAIL to all the results within that query.

    Then do an UPDATE to mark those 1000 entries as sent (SET Sent = 1). There are
    a few ways to mark them as SENT but make sure your query is going to update the
    ones that were selected in the first query so be careful of ordering etc so
    that the same 1000 entries are updated.

    Perhaps something like this:
    <CFQUERY MAXROWS="1000">
    UPDATE zBikeBits
    SET Sent = 1

    WHERE Email IN (#ValueList(selectQuery.Email)#)

    OR

    WHERE Email IN (SELECT Email
    FROM zBikeBits
    WHERE Sent = 0)
    </CFQUERY>

    OR loop over the select query and update each one individually - this will
    probably be the most reliable method for updating.

    Then when you are ready to send your next newsletter be sure to set all email
    addresses back to Sent = 0.

    HTH

    Zoe

    zoeski80 Guest

  6. #6

    Default Re: Sending a Bulk Email from MySQL DB

    You could throw a loop into the output, and have CF take a breather before the
    next batch goes out. Something like
    <cfif currentrow MOD 1000 is 0>
    <cfset restinterval="5000">
    <CFLOOP index="timerval" from="1" to="#restinterval#">
    </CFLOOP>
    </cfif>
    Adjust restinterval to suit your server's performance.

    HTH,

    philh Guest

  7. #7

    Default Re: Sending a Bulk Email from MySQL DB

    The rest interval will tie up one of CF Server's allotted processes, which
    could either be no big deal or a disaster depending on your server settings.

    I wrote up a tutorial on throttling down CFMAIL and posted it here:

    [url]http://mysecretbase.com/Slowing_Down_CFMAIL.cfm[/url]

    A follow-on tutorial linked from there shows how to build in failover support
    (i.e. what happens if you get 1/2way thru the mailer and CF crashes).

    MattRobertson Guest

Similar Threads

  1. Bulk Emailing with MySQL DB
    By lazerbrains555 in forum Macromedia ColdFusion
    Replies: 1
    Last Post: April 5th, 07:39 PM
  2. Best CF bulk email tool
    By Petro_ in forum Macromedia ColdFusion
    Replies: 4
    Last Post: March 29th, 12:30 PM
  3. Sending an email from within ASP
    By Nathan Sokalski in forum ASP Components
    Replies: 5
    Last Post: June 16th, 07:48 PM
  4. FMP to email (non bulk) form letter
    By Andy in forum FileMaker
    Replies: 0
    Last Post: October 31st, 01:08 AM
  5. Replies: 2
    Last Post: August 26th, 08:51 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •