Tutorial aim: How to greet your users / customers on their birthday

A huge variety of web sites today are database-driven, therefore, it is easy to get your DBMS to print people’s names on the front page of your website or even send them birthday e-cards when their month and day of birth matches the current date.

For the sake of this tutorial, I have created a MS-ACCESS database called SAMPLE which contains a single table called USER. The table itself consists of the following fields:

As far as ColdFusion is concerned, all we need is to use a couple of date/time functions to get the job done. They are as follows:

Day(date)

By setting a variable called TodayDate and assigning it to the Now() function we can return the current date:

<cfset TodayDate = Now()>

Now that we have determined the current date, we can use the Month() and Day() functions to extract the month and day date parts from the TodayDate variable.

<!--- Extracting the month date part --->
<cfset CurrentMonth = Month(TodayDate)>

<!--- Extracting the day date part --->
<cfset CurrentDay = Day(TodayDate)>

The next step is to query the database and compare dates in the WHERE clause.

<cfquery name = "qGetUser" datasource = "SAMPLE">

            SELECT usrFirstName, usrLastName, usrEmail, ursBirthDate
            FROM USER
            WHERE Month(usrBirthDate) = #CurrentMonth#
            AND Day(usrBirthDate) = #CurrentDay#
            AND usrStatus = 1 <!--- This makes sure that only active users are included --->

</cfquery>

<!--- Now we use RecordCount to determine if there are any matches found. If so, we can use query results to print people’s names in a certain location on our website, or email a birthday e-card to the respective user. --->

<cfif qGetUser.RecordCount>

    <table border="0" cellpadding="2" cellspacing="0" width="400">
        <tr>
            <td valign="top">Happy Birthday to:</td>
        </tr>
        <cfoutput query="qGetUser">
        <tr>
            <td valign="top">#currentrow#.</td>
            <td valign="top">#usrFirstName# #usrLastName#</td>
            <td valign="top">#DateFormat(usrBirthDate,"mm/dd")#</td>
          </tr>
        </cfoutput>
    </table>
    <!--- Using cfmail to send e-cards to each single user whose birthday matches the current date. --->

    <cfmail to="#usrEmail#" from="webmaster@mysite.com" subject="Happy Birthday !" bcc="webmaster@mysite.com" type="html" query="qGetUser">
        <p>Dear #usrFirstName# </p>
        We would like to congratulate you on your birthday on the #DateFormat(usrBirthDate,”mm/dd”)# as well as sending to you the e-card below.
    </cfmail>

<cfelse>

    <strong>There are no users whose birthday match the current date !</strong>

</cfif>

The above-mentioned approach applies even to DBMS`s other than MS-ACCESS such as MySQL. If you consult the MySQL documentation, you will notice that the SQL functions we have used above are also the same. Therefore, you could create a view or stored procedure should they suit your needs.