ZKM128
asked on
Using <CFIF inside <cfmail <cfoutput
Hello,
I am sending notification emails to members in an organisation using <cfmail> in ColdFusion.
The cfm page is reading member records in the database and sending emails to members using the database field values FirstName, LastName, emailAddress, Age etc.
The email message that members receive should show different fees/prices depending on their age.
So, basically what I need to do is, read member's age from database records, calculate appropriate fees and display on the email message generated by ColdFusion.
I am trying to use <cfif> to determine/set correct fees but I don't know the correct syntax.
The code shown above causes error.
Please show me the correct way of writing <cfif> conditional codes inside the <cfmail> tag.
Thank you.
I am sending notification emails to members in an organisation using <cfmail> in ColdFusion.
The cfm page is reading member records in the database and sending emails to members using the database field values FirstName, LastName, emailAddress, Age etc.
The email message that members receive should show different fees/prices depending on their age.
So, basically what I need to do is, read member's age from database records, calculate appropriate fees and display on the email message generated by ColdFusion.
I am trying to use <cfif> to determine/set correct fees but I don't know the correct syntax.
<cfquery name="qryMembers" datasource="cfdsnMembers">
SELECT FirstName, LastName, emailAddress, Age FROM MembersTable
</cfquery>
<cfmail query = "qryMembers" from="Rainbow Club <info@rainbowclub.com>" to="#FirstName# #LastName# <#emailAddress#>" Subject="Half yearly membership fee" type="html">
<p>Dear #FirstName# #LastName#,</p>
<p>The amount of your half yearly membership fee is:
<cfif #Age# < 25>$100<cfelse 25 <= #Age# < 55>$120</cfif>
<p>
</cfmail>The code shown above causes error.
Please show me the correct way of writing <cfif> conditional codes inside the <cfmail> tag.
Thank you.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
When I delete the code <cfif Age lt 25>$100<cfelseif Age lt 55>$120</cfif> and send the email, the emails are sent OK.
So, I think <cfif Age lt 25>$100<cfelseif Age lt 55>$120</cfif> is definitely causing the error.
Here's the code I have used to send test emails:
So, I think <cfif Age lt 25>$100<cfelseif Age lt 55>$120</cfif> is definitely causing the error.
Here's the code I have used to send test emails:
<cfquery name="qryMembers" datasource="cfdsnMembers">
SELECT FirstName, LastName, emailAddress, Age FROM MembersTable
</cfquery>
<cfmail query="qryMembers" from="info@rainbowclub.com" to="#emailAddress#" subject="CFMail CFIF conditional statement testing" type="html">
<p>Dear #FirstName# #LastName#,</p>
<p>Your age is
<cfif Age lt 25>$100<cfelseif Age lt 55>$120</cfif>
</p>
</cfmail>ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I'm sorry, I stupidly forgot to change the data type of the 'Age' field in my testing database table to number/integer (int) format and left it as string (nvarcha) format and that was causing the error.
Thank you very much for your help and for letting me know to use lt & lte instead of < & <=/
Thank you very much for your help and for letting me know to use lt & lte instead of < & <=/
> <cfif qryMembers.age lt 25>$100<cfelseif qryMembers.age lt 55>$120</cfif>
So membership should be free for those 55 and over? :) Just confirming you do not need a default "cfesle" case.
So membership should be free for those 55 and over? :) Just confirming you do not need a default "cfesle" case.
ASKER
We will be offering different membership fee for members aged more than 55 but at the moment the membership fee hasn't been decided yet. I will need to modify the codes a bit later. Thank you very much for your help experts :-)
Just to add to the topic of best practices, I (and likely agx as well) would suggest keeping your fees inside of a database table instead of hard coding, It will make it a lot easier to manage if you use the fee in different places and if they ever change.
Table structure...
FromAge ToAge Fee
0 25 $100
26 55 $120
56 ??
Table structure...
FromAge ToAge Fee
0 25 $100
26 55 $120
56 ??
Yep. Then you can use a simple JOIN to get the current "Fee". If you want to leave the upper limit of the 55+ null, something like:
SELECT m.FirstName, m.LastName, m.emailAddress
FROM MembersTable m LEFT JOIN MembershipFees fee
ON m.Age BETWEEN fee.FromAge AND fee.ToAgee
OR m.Age >= fee.FromAge and fee.ToAge IS NULL
Though if the ranges are under 25, and under 55, the ranges should be:
FromAge ToAge Fee
0 24 $100 <=== under 25
25 54 $120 <=== 25 and over, but under 55 years of age
55 (null) ?? <=== 55+
SELECT m.FirstName, m.LastName, m.emailAddress
FROM MembersTable m LEFT JOIN MembershipFees fee
ON m.Age BETWEEN fee.FromAge AND fee.ToAgee
OR m.Age >= fee.FromAge and fee.ToAge IS NULL
Though if the ranges are under 25, and under 55, the ranges should be:
FromAge ToAge Fee
0 24 $100 <=== under 25
25 54 $120 <=== 25 and over, but under 55 years of age
55 (null) ?? <=== 55+
ASKER
Yes, I will try to keep the fees inside the database table instead of hard coding. I was doing most of the mathematical calculations by hard coding on the web pages since I have decided to change my back-end database from MS Access to MS SQL, due to the complexity and lack of knowledge about SQL Server.
Thank you so much for your help experts :-)
Thank you so much for your help experts :-)
Well fortunately MS Access syntax is very similar to SQL Server's (for the most part). So it's less of a learning curve than if you switched to MySQL or Oracle.
One thing, I just noticed a logic error in the sample query above. Since it matches on "IS NULL", it should use an INNER JOIN instead of OUTER (or LEFT JOIN), ie
FROM MembersTable m INNER JOIN MembershipFees fee
Edit: Anyway, glad to help :)
One thing, I just noticed a logic error in the sample query above. Since it matches on "IS NULL", it should use an INNER JOIN instead of OUTER (or LEFT JOIN), ie
FROM MembersTable m INNER JOIN MembershipFees fee
Edit: Anyway, glad to help :)
ASKER
This line <cfif Age lt 25>$100<cfelseif Age lt 55>$120</cfif> is still causting the error.
Am I allowed to type <cfif> tag in normal way inside the <cfmail> tag? Or is there another way to type the <cfif and <cfelseif tags inside <cfmail> tag?