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:

Column name

Data type

Description

usrID

Auto-number

Primary Key

usrFirstName

Text

User First Name

usrLastName

Text

User Last Name

usrEmail

Text

User Email Address

usrBirthDate

Date/Time

User Birth Date

usrStatus

Yes/No

User Status

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:

Function name

Description

Syntax

 

 

 

Now()

Returns the current date and time of the computer running the ColdFusion server

 

Month()

Returns the ordinal for the month, ranging from 1 (January) to 12 (December).

Month(date)

Day()

Returns the ordinal for the day of the month, ranging from 1 to 31.

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.

About This Tutorial
Author: Julio Cesar Sousa Amaral
Skill Level: Beginner 
 
 
 
Platforms Tested: CF5
Total Views: 69,805
Submission Date: January 02, 2008
Last Update Date: June 05, 2009
All Tutorials By This Autor: 1
Discuss This Tutorial
  • ActionPage.cfm Birthday Listing SELECT usrFirstName, usrLastName, usrBirthDate FROM USER WHERE Month(usrBirthDate) = #Month(CurrentDate)# AND usrStatus = 1

    Happy Birthday to:
     
    #currentrow#. #usrFirstName# #usrLastName# #DateFormat(usrBirthDate,"mm/dd")#
    There are no users whose birthday match the current date ! SELECT usrFirstName, usrLastName, usrBirthDate FROM USER WHERE Month(usrBirthDate) = #CurrentMonth# AND usrStatus = 1
    Happy Birthday to:
    #currentrow#. #usrFirstName# #usrLastName# #DateFormat(usrBirthDate,"mm/dd")#

  • Hi Don, This is relatively simple. You could build a drop down list containing the twelve months of the year. When a user chooses a particular month, the ordinal for that month is passed to the WHERE clause and this way users can query not only for the previous month, but also for any other month available. And best of all, you could get your application to display by default all of the birthday dates for a particular month when no month variable is passed. This will ensure that users are always viewing something before they choose to select a different month. I have already written the whole script which does this and I will be posting it as soon as possible.

  • This worked like a charm in my application. How would I query for a previous month or two. Thank for the help. Don

Advertisement


Website Designed and Developed by Pablo Varando.