Topic: Using CFloop to insert data into database

rickaclark    -- 11-25-2011 @ 6:43 AM
  Figured it out. Just needed to add the query="MaiList" to the loop. Works perfectly.


I am still having a hard time wrapping my head around cfloop. I use cfmail to send out a small newsletter. What I am trying to do is insert my data into an access database, the emails comes from a database, the body and subject line from a form. When the email is sent it is then deleted. This way I can keep track of any problems that occur and will know who has or hasn't been sent an email. When I run the script it is only inserting the first record from the MailList query.


<cfquery datasource="#application.database#" name="MailList" cachedwithin="#CreateTimeSpan(0,1,0,0)#">
   SELECT newsEmail.email, newsEmail.firstname
   FROM newsEmail
   WHERE newsEmail.email IS NOT NULL
   ORDER BY newsEmail.email_id ASC
</cfquery>


<cfloop index="index" from="1" to="#MailList.recordcount#">
                         
     <cfquery name="insertData" datasource="#application.database#">
          insert into myMessages
          (EmailAddr,EmailMsg,EmailType,EmailServer,EmailSubject,EmailFrom,EmailFirst)
          VALUES
          ('#MailList.email#','#MailList.body#','#application.type#,'#application.server#','#MailList.title#','#application.newsemail#','#MailList.firstname#)
     </cfquery>
</cfloop>

This message was edited by rickaclark on 11-25-11 @ 8:25 AM

egiblock    -- 11-28-2011 @ 10:18 AM
  i just wrote something similar on a website that'll insert multiple records into a database and then send an email message with the records that were just put into the db.  here's the code..  hope this helps.

Get Data into DB:

<cfloop from="1" to="#form.numsamples#" index="i">
  
  <cfset recID = #Evaluate('sampleID' & i)#>
  <cfset testProc = #Evaluate('testprocedure' & i)#>
  
<cfquery name="AddSample" datasource="AccLifeLive" dbtype="odbc">
     INSERT INTO `Sample Information Form`
        (`Sample ID`, `Date Submitted`, location, `test Procedure`,Status, `Print Label`)    
    VALUES
        ('#recID#','#dateformat(form.datesub,'mm/dd/yy')#', '#form.location#', '#testProc#','#status#',#printLabel#)
</cfquery>
</cfloop>


Then, create the email:

<cfmail .........>
The following samples have been entered into the database:<br />
<br />
  <hr>

  <cfloop from="1" to="#form.numsamples#" index="i">
~~ Display data ~~
  </cfloop>

</cfmail>



so i just use the same loop that puts the data into the db, to create the loop in the email.

hope this helps.

..E

This message was edited by egiblock on 11-28-11 @ 10:18 AM

EasyCFM.COM ColdFusion Forums : http://www.easycfm.com/forums
Topic: http://www.easycfm.com/forums/viewmessages.cfm?Forum=12&Topic=15238