multiple values throughout cfmail/ multiple recipients in to="" line as a reult of cfschedule

     I have never used cfschedule before but I plan on setting 1 taskwith a daily interval that points to a page called 30daysmail.cfm which, as you might have guessed, mails to my members every month.

I made a new table  that contains the consumer's info. In this monthly mailer I want to tell the member "This month you have had(#query.recordCount#)consumers hit on your service area.
They have submitted <cfoutput>#U_Name# etc. etc. On:#users.hit_date# <cfoutput>
In this table I made 3 extra columns called qMemberID_1, qMemberID_2, qMemberID_3  (the user can only get 3 results max) and I made them all foreign keys to members.memberID the columns fill up depending on how many members are in their area. So I made this query to go on top of the mailer:

<!---Only sends out mail on the same day of month as member's enrollment--->
<cfquery name="get_members" datasource="#enet#"> select * from members
where datepart(day, member_since) = datepart(day, getdate())
</cfquery>

<!---Selects users that have hit on the memberID of the member being mailed within 30 day period--->
<cfquery name="getUsers" datasource="#enet#">
select * from members,users
where users.qMemberID_1 = getMembers.memberID
OR users.qMemberID_2 = getMembers.memberID
OR users.qMemberID_3 = getMembers.memberID
<!---Need rang of 30 days here but don't know how--->
AND datepart(day, users.hit_date) <= datepart(day, 30)
</cfquery>

THE PROBLEMS:
1- mutiple members could have enrolled within same time span and I don't want the <cfmail to"#getMembers.email#"> to get jumbled up with multiple values (i.e. fred@yahoo.comart@aol.com)

2-I don't think I can use the query attribute in cfmail because I have values from getMembers and getUsers in the body of the message

3-I don't want fred to see art@aol.com in his "To:" line

4-I dont think that the last line in query getUsers is accurate- I only want to show the member their hits from the past 30 days (since last update)
ElectriciansnetAsked:
Who is Participating?
 
dgrafxCommented:
try this
I'm assuming that all records in members table are unique (no dups).
if not then add a distinct (q1)
only use distinct if you need to - it is slow

<cfquery datasource="#Request.Datasource#" name="q1">      
select members.CompanyName, members.Contact_Email, members.memberID
FROM members
WHERE datepart(day, member_since) = datepart(day, getdate())
</cfquery>

<cfmail ...
to="#Contact_Email#"
query="q1">
      #Companyname# [#memberID#]<br>
      Here is your stuff!<br>
      <cfquery datasource="#Request.Datasource#" name="q2">      
      select users.u_city, users.ZipCode, users.U_Name, users.D_Phone, users.C_Phone, users.Email, users.Service, users.comments, users.hit_date
      FROM users
      INNER JOIN member_user ON users.uID = member_user.uID
      WHERE member_user.memberID = #q1.memberID#
      and users.hit_date >= dateAdd(dd,-30,getDate())
      </cfquery>
      #q2.recordcount# hits<br>
      <cfloop query="q2">
      #stuff#
      </cfloop>
</cfmail>
0
 
ElectriciansnetAuthor Commented:
It gets worse! I put a static number in the date format to guarentee results and I'm getting nothing but errors about tring to mix 2 queries on 1 cfmail even though I'm not using the query attribute in cfmail. There are errors in the queries above such as missing hash marks but I have fixed those. Even if I get the cfmail to mail to 1 member at a time if more than 1 fall on that date the tags within the body will show combined results such as Helllo #members.companyName#! You have been contacted this month by#users.RecordCount# consumers. The details are #users.U_Name# (which would be every consumer that hit the site that day etc. etc.). It seems to me that I have to find a way to process the page on closer intervals, only allow single row results and not start back at that result when the next iteration occurs. In short- I need a way to divide the process up differently.
0
 
dgrafxCommented:
Try this

select neededcolumns <!--- i.e. users.qMemberID_1, users.qMemberID_2 etc - don't ever do * --->
from users
where (qMemberID_1 IN (Select memberID From getMembers)
or qMemberID_2 IN (Select memberID From getMembers)
or qMemberID_3 IN (Select memberID From getMembers))
AND users.hit_date >= dateAdd(dd,-30,getDate())
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
ElectriciansnetAuthor Commented:
Thanks for you input, dgrafx:.This gets me off to a better start but still returns mixed rows. Please remember that I ultimately have to end up with one query for cfmail so that I can use the query attribute with contact_Email from the members table for the 'TO:' and companyName from the members table for the heading. Then all the information from the users table that matches that member only being mailed to that member only.
I'm of course trying to tweak it in MSQuery without cfmail but when I thought I had it the results were not good:
Using <cfset EmailList = ValueList(getMembers.Contact_Email , ";")> the mail went out to each member in the list 4 times over showing one, identical row from users to each member.
0
 
dgrafxCommented:
i don't believe you'd use the query attribute for cfmail - not sure till i see more
actually - i just need this:
column names (and what table they are in) that you need to send in your email
0
 
ElectriciansnetAuthor Commented:
CFSchedule points to page-"30daysmail.cfm" with 2 queries.

<cfquery name="getMembers" datasource="#enet#">
 SELECT memberID, contact_Email
FROM members
WHERE datepart(day, member_since) = datepart(day, getdate())-2 <!---Test Only take away last expression(-#)--->
</cfquery>

<!---Planning to lose this tag. Suspect as part of the problem--->
<cfset EmailList = ValueList(getMembers.Contact_Email , ";")>

<cfquery name="getUsers" datasource="#enet#">
SELECT
members.companyName, members.memberID, members.contact_Email,
 users.City ,ZipCode, U_Name, D_Phone, C_Phone, Email,
                      Service , comments , hit_date, qMemberID_1 , qMemberID_2, qMemberID_3
from members, users
where (qMemberID_1 IN (Select memberID From getMembers)
or qMemberID_2 IN (Select memberID From getMembers)
or qMemberID_3 IN (Select memberID From getMembers))
AND users.hit_date >= dateAdd(dd,-30,getDate())
<!---AND users.qMemberID_1 = members.memberID---><!---For MSQuery--->
</cfquery>
<--This worked in MSQuery with both queries in 1 and "getMembers" set to "members".
 Brought to .cfm page and CF says: Invalid object name 'getMembers'.--->

<cfmail To: #members.Contact_Email#
........>
Hello #CompanyName# you have been a member for DateDiff.
The following consumers have hit on you zip code this month:
#U_Name#..... <!---Only for this member with this member's 30 days--->

<!---VERY CONDENSED VERSION--->





0
 
ElectriciansnetAuthor Commented:
There can be several members enrolled on the same day. Sidebar: CFMail will not let me mix queries within.
0
 
ElectriciansnetAuthor Commented:
I'm pretty sure I got it in 1 query allthough I'm not sure how accurate it is or how well it will work with cfmail but it does line the users and members up in an HTML table:

<cfquery name="getUsers" datasource="#enet#">
SELECT
companyName, memberID, contact_Email,
 users.City ,ZipCode, U_Name, D_Phone, C_Phone, Email,
                      Service , comments , hit_date, qMemberID_1 , qMemberID_2, qMemberID_3
from members, users
where (qMemberID_1 IN (Select memberID From Members WHERE datepart(day, member_since) = datepart(day, getdate())-2)
or qMemberID_2 IN (Select memberID From Members WHERE datepart(day, member_since) = datepart(day, getdate())-2)
or qMemberID_3 IN (Select memberID From Members WHERE datepart(day, member_since) = datepart(day, getdate())-2))

AND users.qMemberID_1 = members.memberID
OR users.qMemberID_2 = members.memberID
OR users.qMemberID_3 = members.memberID
AND users.hit_date >= dateAdd(dd,-30,getDate())
</cfquery>
0
 
ElectriciansnetAuthor Commented:
The above query seems to sen a seperate mail for each row in the users table when I use query attribute and not EmailList which is closer but I realy wonder if what I'm tring to do is possible.
0
 
ElectriciansnetAuthor Commented:
Even though I did not use the  EmailList tag, I did a dump on it.  With the time arguments set like above 2 users results should go to 1 member and 1 to another member plus I think there should have been 4 results to 3 members according to the fake user inserts I made earlier but here is the dump:
rowehl@aol.com;member_services@electriciansnet.com;member_services@electriciansnet.com
dis regard the actual address- I only have so many to use as model "user" address. If you want, go to www.electriciansnet.com and use 10007 as your model, enter  real or bogus data and it will insert into users inputting the qmemberID of the member with 10007 as 1 of his zip codes. -Just to help you see what I'm trying to do. Thanks, Andy
0
 
dgrafxCommented:
try this
<cfquery name="getMembers" datasource="#enet#">
SELECT<!--- first off - you need to add the table prefix - query will run faster if i have them incorrect just change --->
members.companyName, members.memberID, members.contact_Email<!--- ,
users.City ,users.ZipCode, users.U_Name, users.D_Phone, users.C_Phone, users.Email,
users.Service , users.comments , users.hit_date, users.qMemberID_1 , users.qMemberID_2, users.qMemberID_3 --->
from users
inner join members on
(users.qMemberID_1 = members.memberID
or users.qMemberID_2 = members.memberID
or users.qMemberID_3 = members.memberID)
Where users.hit_date >= dateAdd(dd,-30,getDate())
and members.member_since >= dateAdd(dd,-2,getDate())<!--- is this what you want - make sure member is at least 2 days old? --->
and memberID IN
(SELECT a.memberID
FROM members a, members b
WHERE a.memberID != b.memberID AND a.contact_Email = b.contact_Email)
</cfquery>

<CFMAIL FROM="#avalidemailaddress#"
TO="#contact_Email#"
SUBJECT="#yoursubject#"
SERVER="#mailserver#"
query="getMembers"
Type="html">

<cfquery name="getUsers" datasource="#enet#">
Select users.City ,users.ZipCode, users.U_Name, users.D_Phone, users.C_Phone, users.Email,
users.Service , users.comments , users.hit_date, users.qMemberID_1 , users.qMemberID_2, users.qMemberID_3
from users
where users.qMemberID_1 = #getMembers.MemberID#
or users.qMemberID_2 = #getMembers.MemberID#
or users.qMemberID_3 = #getMembers.MemberID#
</cfquery>
<cfloop query="getUsers">
#stuff#
</cfloop>
</cfmail>
0
 
ElectriciansnetAuthor Commented:
Thank you I will try tonight. You said:"<!---is this what you want - make sure member is at least 2 days old? --->
Thie -2 is only there to return results for testing. It will go away to select on the members with member_since(enroll date) on that day of month. cfschedule points to it daily. Thanks again, Andy
0
 
ElectriciansnetAuthor Commented:
Error on Server:TERMINAL-SERVER\ENET1, Source:.Net SqlClient Data Provider
Class:15, Msg 102, State 1, Line 15
Incorrect syntax near 'contact_Email'.
0
 
ElectriciansnetAuthor Commented:
Please disregard the above but this is the line I am currently having a problem with:

WHERE a.memberID != b.memberID AND a.contact_Email = b.contact_Email)

I added single quotes to the line (along with a couple of others) to stop "equal operator and data type: text" error. Now it looks like this:

WHERE a.memberID != 'b.memberID' AND 'a.contact_Email' = 'b.contact_Email') is the second equal operaator in line supposed to be equal or not equal?
__________________________________________________________________________________
Where users.hit_date >= dateAdd(dd,-30,getDate())
and members.member_since >= dateAdd(dd,-4,getDate())
Forgive me but this is a hard query for me. When I take away the -4 will it return any member(X 1) who's member_since(enroll date) day of the month equals the current day of the month and every user that hit on that memberID within that month?
The reason I ask is that MSQuery returns 5 rows when coldFusion returns only 1 with the exact same query. Thats a first! Thanks, Andy
0
 
ElectriciansnetAuthor Commented:
This is my end result on my test page so that I don't have to keep sending out mail (When right side of bottom line in getMembers is set to '=' no rows are returned- when set to '!=" all rows are returned from members and only 1 returned from users. I'm confused!):

<cfquery name="getMembers" datasource="#enet#">
SELECT
members.companyName, members.memberID, members.contact_Email
from users
inner join members on
(users.qMemberID_1 = members.memberID
or users.qMemberID_2 = members.memberID
or users.qMemberID_3 = members.memberID)
Where users.hit_date >= dateAdd(dd,-30,getDate())
and members.member_since >= dateAdd(dd,-5,getDate())
and memberID IN
(SELECT a.memberID
FROM members a, members b)
and memberID IN
(SELECT a.memberID
FROM members a, members b
WHERE a.memberID != b.memberID)
AND 'a.contact_Email' = 'b.contact_Email'
</cfquery>

<!---<CFMAIL FROM="#avalidemailaddress#"
TO="#contact_Email#"
SUBJECT="#yoursubject#"
SERVER="#mailserver#"
query="getMembers"
Type="html">--->

<cfquery name="getUsers" datasource="#enet#">
Select users.City ,users.ZipCode, users.U_Name, users.D_Phone, users.C_Phone, users.Email,
users.Service , users.comments , users.hit_date, users.qMemberID_1 , users.qMemberID_2, users.qMemberID_3
from users
where users.qMemberID_1 = '#getMembers.MemberID#'
or users.qMemberID_2 = '#getMembers.MemberID#'
or users.qMemberID_3 = '#getMembers.MemberID#'
</cfquery>



<!---</cfmail>--->

<!---<cfif getUsers.RecordCount NEQ 0 >--->

<!---<cfset EmailList2 = ValueList(getUsers.Contact_Email , ";")>
<cfdump var="#EmailList2#">--->
<!------>
<cfloop query="getMembers">
<cfoutput query="getUsers">




 <table border="3" cellpadding="1" cellspacing="1" cols="10" align="center">
         <tr>
           <td>Name</td>
           <td>City</td>
           <td>Zip</td>
           
           <td>Day Phone</td>
           <td>Evening/Cell Phone</td>
           <td>Email</td>
           <td>Service</td>
           <td>comments</td>
           <td>Hit Date</td>
               <td>Member ID</td>
               <td>Company Name</td>
               <td>Contact Email</td>
               <td>Current Row</td>
               <td>OF</td>
               
         </tr>
         
           <tr>
             <td>#getUsers.U_Name#</td>
             <td>#getUsers.City#</td>
             <td>#getUsers.ZipCode#</td>
             
             <td>#getUsers.D_Phone#</td>
             <td>#getUsers.C_Phone#</td>
             <td>#getUsers.Email#</td>
             <td>#getUsers.Service#</td>
             <td>#getUsers.comments#</td>
             <td>#getUsers.hit_date#</td>    
           <td>#getMembers.memberID#</td>
           <td>#getMembers.CompanyName#</td>
           <td>#getMembers.Contact_Email#</td>
           <td>#getUsers.currentRow#</td>
               <td>#RecordCount#</td>
         </tr>
         
           <tr>
         
           </tr>
       
       </table>
</div>

 
      <img src="http://www.electriciansnet.com/picts/Fireworks/Spacers/chrome_w_1.png" alt="spc1" height="32"  width="100%" />
<!------> </cfoutput></cfloop>
 <cfdump var="#getUsers#"><cfdump var="#getMembers#">

0
 
ElectriciansnetAuthor Commented:
Please Go to http://www.electriciansnet.com/loop_test1.cfm to see what the above returns.
0
 
dgrafxCommented:
sorry i got real busy and didn't have time to look at this.
I did notice some errors in logic
I redid it here:

<cfquery name="getMembers" datasource="#enet#">
SELECT
members.companyName, members.memberID, members.contact_Email
from users
inner join members on
(users.qMemberID_1 = members.memberID
or users.qMemberID_2 = members.memberID
or users.qMemberID_3 = members.memberID)

Where users.hit_date >= dateAdd(dd,-30,getDate())
and members.member_since >= dateAdd(dd,-5,getDate())
and memberID IN
(SELECT a.memberID
FROM members a, members b
WHERE a.memberID != b.memberID AND a.contact_Email = b.contact_Email)
</cfquery>

<cfquery name="getUsers" datasource="#enet#">
Select users.City ,users.ZipCode, users.U_Name, users.D_Phone, users.C_Phone, users.Email,
users.Service , users.comments , users.hit_date, users.qMemberID_1 , users.qMemberID_2, users.qMemberID_3
from users
where
users.qMemberID_1 IN (#QuotedValueList(getMembers.MemberID)#) <!--- is this a varchar column? if no change to valuelist --->
or
users.qMemberID_2 IN (#QuotedValueList(getMembers.MemberID)#)
or
users.qMemberID_3 IN (#QuotedValueList(getMembers.MemberID)#)
</cfquery>

try that
0
 
ElectriciansnetAuthor Commented:
Thank you so much for your patience but I'm still getting errors in coldfusion. Here is what is hapening:
No memberID is not varchar, it is int but (with your query as is) even though there are no errors/ no rows returned in MSQuery, there are errors in CF:
____________________________________________________________________________


i [Macromedia][SQLServer JDBC Driver][SQLServer]Incorrect syntax near ')'.
 
The error occurred in C:\Inetpub\wwwroot\electriciansnet.com\loop_test1.cfm: line 57

55 : users.qMemberID_2 IN (#QuotedValueList(getMembers.MemberID)#)
56 : or
57 : users.qMemberID_3 IN (#QuotedValueList(getMembers.MemberID)#)
58 : </cfquery>
59 :

SQL          Select users.City ,users.ZipCode, users.U_Name, users.D_Phone, users.C_Phone, users.Email, users.Service , users.comments , users.hit_date, users.qMemberID_1 , users.qMemberID_2, users.qMemberID_3 from users where users.qMemberID_1 IN () or users.qMemberID_2 IN () or users.qMemberID_3 IN ()

This is what CF returned
___________________________________________________________________________

This is what I get if I don't use single quotes here:
AND a.contact_Email = b.contact_Email)
Its not doing it now because of above error but a minute ago the error said I could not use equal operator with data type text.

Forgive me but are you sure its not supposed to be "!=" on the right side like so:
AND 'a.contact_Email' != 'b.contact_Email')

When I use "!=" and single quotes over email, I lose all errors but multiple duplicate contact_eamails are returned. Thanks, Andy



0
 
ElectriciansnetAuthor Commented:
Hi DGRAFX.
I ended up getting it with this query:

Select memberid, companyname, contact_email, uid, u_name, email, sortcol
  FROM( Select memberid, companyname, contact_email, null AS uid, null AS u_name, null AS email, 1 as sortcol
          FROM members a ) AS memberrow
UNION ( Select memberid, null AS companyname, null AS contact_email, uid, u_name, email, 2 as sortcol
          FROM members b INNER JOIN users
            ON memberid = qmemberid_1 OR memberid = qmemberid_2 OR memberid = qmemberid_3)
ORDER BY memberid, sortcol, uid

But, can I wrap it in a cfloop inside of cfmail like you recommended with 2 queries?
0
 
dgrafxCommented:
a few things
1) WHERE a.memberID != b.memberID AND a.contact_Email = b.contact_Email
you had said there may be multiple member ids but it's actually the same entitly
this takes the entities (a.contact_Email = b.contact_Email) and chooses one instead of multiple

2) "could not use equal operator with data type text"
I wasn't aware that you had a data type text or ntext
what column is that?

3) remove quotedvaluelist if datatype is int - use valuelist

give the loop inside mail tag a try ???

the concept I use most of the time

a) i do a query to see who the emails get sent to - grabbing their name, email address and a few other things (just whats needed)
b) then i do the cfmail tag using this query as it's query
c) within mail tag i replace the "flags" in the content of the outgoing email with particulars for the user.
for ex.
<cfset emailcontent="^FNAME^,<br>This is a test.">
<cfmail ...
query="yourquery">
<cfset temp=replacenocase(emailcontent,"^FNAME^",yourquery.firstname,"all")>
<cfset temp=replacenocase(temp,"^LNAME^",yourquery.lastname,"all")>
etc ...
#temp#
</cfmail>



0
 
ElectriciansnetAuthor Commented:
Okay my friend, after about a week went by (before you started responding)I asked a different question but related to the same item, cfmail. Through the help of EE's Jim Brandley who is an SQL -but not CF -expert I got a query that only returns 1 email address. Jim did have the rows we did not want returned set to null and I got an error for that. I downloaded an IsNull UDF but could not figure out how to make this tag work:

<cfif NOT IsDefined (getUsers.CompanyName)  > </cfif>
<cfinclude template="../_udfs/isNull.cfm">
 So, Jim sent back the query below that returns blank rows and I tried to apply just the one query with your cfloop suggestion (which I like) but I got this error:

 Attribute validation error for tag CFMAIL.
The value of the attribute TO is invalid. The length of the string, 0 character(s), must be greater than or equal to 1 character(s)

Below is Jim's query which rolls both queries into getMembers. I tried (just to see) using ValueList and cfloop and it sent out dozens over super long emails for 9 returned rows. While I wait to hear back from you I will try your query again now that I have changed text columns to varchar.  Thanks, Andy

<cfquery name="getUsers" datasource="#enet#">
SELECT companyName, memberID, contact_Email, member_since, u_city,
       ZipCode, U_Name, D_Phone, C_Phone, Email, Service, comments,
       hit_date, sortcol
  FROM( SELECT COALESCE(companyname, '') companyName, memberid, contact_email, member_since, null u_city,
               null ZipCode, null U_Name, null D_Phone, null C_Phone, null email, null Service, null comments,
               null hit_date, 1 as sortcol
          FROM members INNER JOIN users
            ON memberid = qmemberid_1 OR memberid = qmemberid_2 OR memberid = qmemberid_3
         WHERE (qMemberID_1 IN (Select memberID From Members WHERE datepart(day, member_since) = datepart(day, getdate())-7)
            OR qMemberID_2 IN (Select memberID From Members WHERE datepart(day, member_since) = datepart(day, getdate()))
            OR qMemberID_3 IN (Select memberID From Members WHERE datepart(day, member_since) = datepart(day, getdate())))
           AND users.hit_date >= dateAdd(dd,-30,getDate())) memberRow
UNION ( SELECT '' companyname, memberid, '' contact_email, '' member_since, u_city,
               ZipCode, U_Name, D_Phone, C_Phone, email, Service, comments,
               hit_date, 2 as sortcol
          FROM members INNER JOIN users
            ON memberid = qmemberid_1 OR memberid = qmemberid_2 OR memberid = qmemberid_3
         WHERE (qMemberID_1 IN (Select memberID From Members WHERE datepart(day, member_since) = datepart(day, getdate())-7)
            OR qMemberID_2 IN (Select memberID From Members WHERE datepart(day, member_since) = datepart(day, getdate()))
            OR qMemberID_3 IN (Select memberID From Members WHERE datepart(day, member_since) = datepart(day, getdate())))
           AND users.hit_date >= dateAdd(dd,-30,getDate()))
ORDER BY memberid, sortcol, U_Name;
</cfquery>

<cfif getUsers.RecordCount NEQ 0 >

  <cfmail to = "#Contact_Email#"
from = "member_services@electriciansnet.com"
Subject = "30 Day Status Report"
query="getUsers"
type="HTML">
<cfloop query=" getUsers">
#stuff#
</cfloop>
</cfmail>
0
 
ElectriciansnetAuthor Commented:
Your probably gonna think I'm real stupid but here is the error from your query:

 [Macromedia][SQLServer JDBC Driver][SQLServer]Incorrect syntax near ')'.
 
The error occurred in C:\Inetpub\wwwroot\electriciansnet.com\loop_test1.cfm: line 58

56 : users.qMemberID_2 IN (#QuotedValueList(getMembers.MemberID)#)
57 : or
58 : users.qMemberID_3 IN (#QuotedValueList(getMembers.MemberID)#)
59 : </cfquery>
60 :


You said:

users.qMemberID_1 IN (#QuotedValueList(getMembers.MemberID)#) <!--- is this a varchar column? if no change to valuelist --->

No- memberID is of datatype int, SCOPE Identity
0
 
dgrafxCommented:
ok - whenever doing IN you need to make sure there is a value (not null and not zero length)
that's why in a query such as this I prefer to do
a) where whatever IN (Select whatever From table where ...)
instead of
b) where whatever IN (#aCFvariable#)

method a does not throw an error - just doesn't return a result because it's not in the list
method b throws an error if #aCFvar# is null or zero lenth

but if your column is int (to answer your question) you need to change to (#ValueList(getMembers.MemberID)#)

note: is it possible that you need to redo your db structure and application logic?
a task such as this should be easier than this.
if interested - post the general concept behind having 3 different memberid's in one table that need to match memberid in the other table.
just kinda describe what you're doing
I know what you are doing with sending the email - just not sure about whats up before this point.
0
 
ElectriciansnetAuthor Commented:
OK I have done like you suggested and changed the DB structure so that the SELECT below works but does not filter out duplicate contact_Email. Remember? I need 1 member for email with every user that has hit on that member in the last 30 days. The query only needs to produce results for any member with member_since (enroll date) with the same day of the month that cfschedue executes the page on a daily basis. i.e., member_since date = day 1, cfschedule executes on day 1 and only returns results for that member (or those members).

select users.u_city, users.ZipCode, users.U_Name, users.D_Phone, users.C_Phone, users.Email, users.Service, users.comments, users.hit_date, members.CompanyName, members.Contact_Email, members.member_since
FROM users INNER JOIN member_user ON users.uID = member_user.uID
INNER JOIN members ON members.memberID = member_user.memberID
WHERE datepart(day, member_since) = datepart(day, getdate())
 AND users.hit_date >= dateAdd(dd,-30,getDate())
0
 
ElectriciansnetAuthor Commented:
Here are the tables:

USE [enetdb1SQL]
GO
/****** Object:  Table [dbo].[members]    Script Date: 08/12/2007 23:59:35 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[members](
      [memberID] [int] IDENTITY(1,1) NOT NULL,
      [companyName] [varchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
      [streetNumber] [varchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
      [City] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
      [State] [varchar](40) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
      [Contact_First] [varchar](40) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
      [Contact_Last] [varchar](40) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
      [Contact_title] [varchar](40) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
      [Contact_Phone] [nvarchar](12) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
      [Contact_Email] [varchar](256) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
      [PassWord] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
      [Contact_URL] [varchar](256) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
      [License] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
      [CellNumber] [nvarchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
      [NetworkID] [nvarchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
      [Slogan] [varchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
      [member_Since] [datetime] NULL,
      [logo] [image] NULL,
 CONSTRAINT [PK__members__4EA8A765] PRIMARY KEY CLUSTERED
(
      [memberID] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO
SET ANSI_PADDING OFF
__________________________________________________________

USE [enetdb1SQL]
GO
/****** Object:  Table [dbo].[member_user]    Script Date: 08/13/2007 00:00:41 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[member_user](
      [memberID] [int] NOT NULL,
      [uID] [int] NOT NULL,
 CONSTRAINT [PK_MU] PRIMARY KEY CLUSTERED
(
      [memberID] ASC,
      [uID] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

GO
USE [enetdb1SQL]
GO
ALTER TABLE [dbo].[member_user]  WITH CHECK ADD  CONSTRAINT [FK_MU1] FOREIGN KEY([memberID])
REFERENCES [dbo].[members] ([memberID])
GO
ALTER TABLE [dbo].[member_user]  WITH CHECK ADD  CONSTRAINT [FK_MU2] FOREIGN KEY([uID])
REFERENCES [dbo].[users] ([uID])

__________________________________________________

USE [enetdb1SQL]
GO
/****** Object:  Table [dbo].[users]    Script Date: 08/13/2007 00:01:21 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[users](
      [uID] [int] IDENTITY(1,1) NOT NULL,
      [u_city] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
      [ZipCode] [varchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
      [U_Name] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
      [D_Phone] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
      [C_Phone] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
      [Email] [varchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
      [Service] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
      [comments] [varchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
      [hit_date] [datetime] NULL,
 CONSTRAINT [PK_users] PRIMARY KEY CLUSTERED
(
      [uID] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF

0
 
ElectriciansnetAuthor Commented:
You almost have the fix to my 10 day struggle but there is one problem (unless the cfloop fixes it and I just can't see it with a cfdump):

WHERE member_user.memberID = #q1.memberID#

the dump on q2 that  I'm doing on this only returns the 1st row in the users table. I will try it in the cfmail like you sent while I wait for your response. Thanks, Andy
0
 
ElectriciansnetAuthor Commented:
It worked perfectly! Except for 1 thing but I have been working on this for so long that it almost doesn't matter.
The user queries the member by entering their zip code on the home page form. Well there can be up to 3 members in with the same zip code. One of my user query tests (to produce rows for this task) queried test members "Insert 8" & "Insert 9" which share a common zip code..............Actually never mind! They were both supposed to get those results. IT WORKS PERFECTLY, THANK YOU VERY MUCH!!!
You should post this on easy cfm and others like it. If you don't want to I can and I can explain how I got the answer, you. Thanks again and I will accept this as the answer now. Andy
0
 
dgrafxCommented:
I'm glad it worked!
Actually, I'm too busy to visit any other sites.
EE is enough!
Go ahead and post it if you want though.

for future reference - when testing potential results -
the cfdump is excellent for certain tasks, but in a case like this where you need to see what a cfmail query will do,
then treat cfmail EXACTLY like an output query.
<cfmail query="yourquery"> = <cfoutput query="yourquery">
0
 
ElectriciansnetAuthor Commented:
OK but does this way require you to actually send out a mail? That's what I was trying to prevent.
0
 
dgrafxCommented:
that is the point
instead of
<cfmail ...
query="xxx">

replace that with
<cfoutput query="xxx">

and of course the end tags
this will be the exact same query - just of course without sending emails

good luck ...
0
 
ElectriciansnetAuthor Commented:
I'm sorry. I promise this will be the last question regarding this problem (besides the one above)  Is there a simple cfsetting or something that prevents anything but cfschedule from executing the mail page?
0
 
dgrafxCommented:
thats in your application security logic
what i do is
1) I have directories where only admins can enter
and of course bots are not admins
2) you need a public page for the scheduler to be run though
so ...
a basic level of security would be to say
<cfif listfind(Application.authIPs,cgi.remote_addr)>
 <cfif action is "memberemail">... scheduler code ...
    <cfinclude template="/protecteddirectory/memberemailpage.cfm">
<cfelseif action is "blah">
    <cfinclude template="/protecteddirectory/blahpage.cfm">
</cfif>
</cfif>
The var Application.authIPs would be a list of IPs allowed to access the scheduler page
the servers IP would be there and your testing IP so you could test by entering the page in your browser

Professional Grade ...
FusionGadgets.com
0
 
ElectriciansnetAuthor Commented:
Are you saying that bots/ spammers can run my other mail pages such as my basic contact.cfm?

I don't currently have a <cfschedule> tag anywhere. I have it set directly in CFadmin. Is that ok?

Is Application. actually the name of my cfapplication in Application.cfm which would be
<cfif listfind(Enetapps.127.0.0.1,cgi.remote_addr)>

 <cfif action is "30daysmail">
<cfschedule action ="update"
   task = "30daysmail"
   operation = "HTTPRequest"
   url = ""
   startDate = "8/5/07"
   startTime = "12:00 AM"
   interval = "daily"
   resolveURL = "Yes"
   publish = "No"
   requestTimeOut = "600">
    <cfinclude template="/mailer/30daysmail.cfm">
<cfelseif action is "blah"><!---???--->
    <cfinclude template="/protecteddirectory/blahpage.cfm"><!---???--->
</cfif>
</cfif>

I know I'm off base because why would I need to include 30daysmail.cfm if cfschedule is already pointing there.

0
 
dgrafxCommented:
ok - this is a real long story ...
Essentially it boils down to the fact that web dev is hard work and very complex.
That is to say that quality web applications have a lot of hard work behind them.

1) you should (need to) have code (set a cookie) that identifies (tries to anyway) bots, so right from the get go you have a var to test against.
ex.
on all your form action pages
<cfif cookie.isBot>
<cfabort>
</cfif>
2) Study the Application scope
it is a scope like session - cookie - variables
but each is different
it is a persistent variable - which is to say that if you set it once on one page - it will be available on other pages.
like in your control panel (you need one)
you might have a form that sets vars and "AuthIPs" is one of them.
To set an application var:
<cflock scope="APPLICATION" type="EXCLUSIVE" timeout="15">
<cfset Application.AuthIPs=form.AuthIPs>
</cflock>
where form.AuthIPs might equal "65.132.55.65,45.21.23.5" - this is just a list of authorized ips

3) Research the cfschedule tag
you really only need it to create a schedule programatically - but doing it in the cfadmin is fine.
even though you can use it to run a schedule (action="run") you don't need to

look at this code again
<cfif listfind(Application.authIPs,cgi.remote_addr)> if the ip of the machine trying to run this code is authorized
<cfif action is "memberemail"> lets say this page is named scheduler.cfm - then the scheduled page that runs daily or whatever (the URL value) = "/public/scheduler.cfm?action=memberemail" remember this page needs to reside in a public directory
    <cfinclude template="/protecteddirectory/memberemailpage.cfm"> now this is the page where your members / users are queries and the emails are sent out from
<cfelseif action is "blah"> this is to show that you can create other schedules and differentiate them by the action - but you call the same page - "/public/scheduler.cfm?action=blah"
    <cfinclude template="/protecteddirectory/blahpage.cfm">
</cfif>
</cfif>
notice how the cfschedule tag is not here.
thats not how it's used.
you might want to use it though (in your control panel) to create schedules via a form

if you're interested - we have misc code (4 sale) that determines if an entity is a bot or not and how to proceed, how to protect directories and forms - etc
just look at my contact info and send an email if interested

I wish you the best ...



0
 
ElectriciansnetAuthor Commented:
Thank you that explained a lot! I may need to look into purchasing some of your code. After all of these months of research and site construction I had no idea this was even an essential. The 1st thing I'm going to do is Google CF control panel. Thanks, Andy
0
 
dgrafxCommented:
Andy,
A control panel is something you write.
It is an admin only area where you control site settings.
You organize it into different areas
for ex one area may control users and their settings
another may control products (like if you have an ecommerce system)
another may control general website settings like authIPs I described
and since you will have code that keeps bots and non admins out it is a place to store code such as your code that your scheduler page calls.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.