The Workings of the Bulletin Board Application

Using the Bulletin Board application, employees can post their queries to a centralized database, BulletinBoard. The employees can post, view, and delete messages. When the application is loaded, the Login page is displayed, as shown in Figure 10.4.

Figure 10.4: The Login page.

The Login page requires the user to enter a unique username and password. After the user clicks the Sign In button, checklogin.cfm validates the user by referring to the Users table in the BulletinBoard database. If the username and password are invalid or don't exist in the database, an error message is displayed on the Login page, as shown in Figure 10.5.

Figure 10.5: The Login page with an error message.

Before delving into the details of the application's workings, first let's configure the data source to be used for the project.

Configuring the Data Source

The development team uses the SQL Server 2000 database named BulletinBoard as the back-end database for the Bulletin Board application. To interact with the database, a data source needs to be configured. To do this, start the ColdFusion Administrator in ColdFusion Server 5.0. Then, follow these steps:

1. Select ODBC under the Data Sources section to the left of the ColdFusion Administrator.

2. Name the data source Forum.

3. Select the Microsoft SQL Server driver from the ODBC drop-down list to describe the ODBC driver, and then click the Add button.

4. In the Add Data Source section of the ColdFusion Administrator, enter the server name, database name, username, and password to connect to the SQL Server 2000 database. The data source is now added to the data sources list.

Validating the Bulletin Board Application's Users

When the users submit the login information in the Login page, the check.cfm page is loaded. This file is used to connect to the database with the help of the SELECT statement and extract the username and password from the Users table that match the username and password that a user enters in the Login page. You use the WHERE clause to find the matching record. The following code shows how the <CFQUERY> tag is used:

 <CFQUERY NAME="User1" DATASOURCE="Forum">     SELECT * FROM Users     WHERE UserName='#Form.user#' and Password ='#Form.pass#' </CFQUERY> 

The <CFQUERY> tag requires the name of the data source to interact with the database. Use the DATASOURCE attribute to set the value of the specific data source. To do this, set this attribute to New, which is the data source that you created. The output of the query can be used in the latter part of the code by referring to the query results by the name specified in the NAME attribute of the <CFQUERY> tag. The following code snippet tests if RECORDCOUNT of the query User1 is equal to 0:

 <cfif #user1.RecordCount# eq 0> 

If it is 0, the login.htm page is loaded again and an error message is displayed on it.

Working of the User Main Page

The User Main page contains links that allow employees to post, view, and delete messages from the BulletinBoard database. The following sections discuss the implementation of each functionality.

Posting a Message

When a user clicks the Post Your Message link, it opens the PostMessage.htm page. Here, the user needs to enter the message to be posted to the BulletinBoard database. The following code in the PostMessage.htm file creates a form and the fields:

 <form name="new1" action="post.cfm" method="post">     <div align="center">       <font face="Verdana">           User Name <input type="text" name ="user" ><br>           Email <input type="text" name ="email" ><br>           Subject <input type ="text" name="subject"><br>           Message </font> <textarea cols="20" name="query" rows="5"></textarea>           <input type="submit" value="Send"><input type="reset">     </div> </form> 

Figure 10.6 shows the Post Message page that allows the user to post the messages in the BulletinBoard database.

Figure 10.6: The Post Message page.

When the user clicks the Send button, the PostMessage.cfm page opens. If the user has filled out all the fields correctly, the message is posted in the BulletinBoard database. The following code shows how the post.cfm file inserts the message into the BulletinBoard database:

 <CFQUERY Name="user1" DATASOURCE="Forum">     Insert into Query     values('#Form.user#','#Form.email#','#Form.subject#',              '#Form.query#','#pdate#'); </CFQUERY> 

As mentioned, the <CFQUERY> tag requires the name of the data source to interact with the database. You use the DATASOURCE attribute to set the value of the specific data source. Then, you need to use the INSERT statement to insert the message into the BulletinBoard database.

Figure 10.7 shows how the PostMessage.cfm page displays the message when the query is submitted to the BulletinBoard database.

Figure 10.7: Inserting the message into the database.

Viewing Queries

When the user clicks the View Your Message link on the User Main page, the View.cfm page opens. This page displays the username, post date, e-mail ID, and subject of the message that the user posted. When the user clicks the subject, the message.cfm page is displayed with the message.

Figure 10.8 shows the View.cfm page displaying a message.

Figure 10.8: The View Message page.

Deleting a Message

When a user clicks the Delete Your Message link, it opens the DeleteMessage.cfm page. This page allows the user to delete the message from the BulletinBoard database.

Figure 10.9 shows the Delete Message page, which displays the message along with the Delete hyperlink for deleting the query from the BulletinBoard database.

Figure 10.9: The Delete Message page.

When the user clicks the Delete hyperlink, the delete.cfm file is opened and the message is deleted from the BulletinBoard database, as shown in Figure 10.10.

Figure 10.10: Deleting the query from the database.

Now that you understand the code for all the Web pages, the following section will provide the entire listing of the code for all the Web pages.

Listing 10.1 is the complete code for the Login.htm file.

Listing 10.1: Login.htm

 <html> <head> <title> My Forum </title> </head> <body>     <pre><img src="/books/1/524/1/html/2/main1.jpg" width="754" height="105" border="0" usemap="#Map">     <map name="Map"><area shape="poly" coords="573,50" href="#"></map>     <form name="new1" action="check.cfm" method="post">       <table width="43%" border="0" align="center">         <tr>           <td colspan="2">&nbsp;</td></tr><tr><td colspan="2">             <div align="center">               <font size="4" face="Haettenschweiler"><u>                 Enter the username and password</u>               </font>             </div>           </td>         </tr>         <tr>           <td width="29%">&nbsp;</td><td width="71%">&nbsp;           </td>         </tr>         <tr>           <td width="29%">             <div align="center">               <font size="2" face="Verdana"><b>UserName</b></font>             </div>           </td>           <td width="71%">             <div align="center">               <input type="text" name ="user" >             </div>           </td>         </tr>         <tr>           <td height="25" width="29%">             <div align="center">               <font size="2" face="Verdana"><b>Password</b></font>             </div>           </td>           <td width="71%">             <div align="center">               <input type ="password" name="pass">             </div>           </td>         </tr>         <tr>            <td width="29%">&nbsp;</td><td width="71%">&nbsp;            </td></tr>         <tr>           <td width="29%">             <div align="right">               <input name="submit" type="submit" value="Sign In">             </div>           </td>           <td width="71%">             <div align="center"><input name="reset" type="reset">             </div>           </td>         </tr>       </table>     <p>&nbsp;</p><p>&nbsp;</p><p>&nbsp;</p><p><br>     <br>     </p></form>     </pre> </body> </html> 

Listing 10.2 shows the code in the Check.cfm file.

Listing 10.2: Check.cfm

 <html> <head> <title> My Forum </title> </head> <body>      <img src="/books/1/524/1/html/2/main1.jpg" width="770" height="105"><br>      <cfquery name="user1" datasource="Forum">        select * from Users        WHERE UserName='#Form.user#' and Password ='#Form.pass#'      </cfquery>      <font face="Haettenschweiler"><cfoutput query="user1">        Welcome #Form.user#<br>      <a href="viewpost.htm"> Click here to join the forum</a>      </cfoutput>      <cfoutput>      <cfif #user1.RecordCount# eq 0>        Invalid UserName And Password<br>        <a href="login.htm"> click here to go back </a>      </cfif>      </cfoutput></font> </body> 

Listing 10.3 shows the code in the ViewPost.htm file.

Listing 10.3: ViewPost.htm

 <html> <head> <title> My Forum </title> </head> <body> <table width="350" border="0" cellspacing="0" cellpadding="0">     <tr>       <td><img src="/books/1/524/1/html/2/main1.jpg" width="730" height="105"></td>     </tr> </table> <table width="732" border="0" cellspacing="0" cellpadding="0" height="20">     <tr>       <td colspan="3" height="27">         <font face="Haettenschweiler" size="3" color="#0000FF"></font>         <font color="#990066">           Click on the links to navigate to the respective pages</font>           <font face="Haettenschweiler" size="3" color="#0000FF">         </font></td>       </tr>       <tr>         <td width="224" height="7">           <div align="center">             <a href="post.htm">             <font face="Haettenschweiler" size="4">               Post Your Message</font></a></div>         </td>         <td width="281" height="7">           <div align="center">             <a href="View.cfm">               <font face="Haettenschweiler" size="4">View Your Message</font></a>           </div>         </td>         <td width="227" height="7">           <div align="center">             <font face="Haettenschweiler" size="4">             <a href="DeleteMessage.cfm">Delete               Your Message</a></font>           </div>        </td>     </tr> </table> <p align="center">&nbsp;</p> <p align="center"><a href="post.htm"><font face="Haettenschweiler" size="4"> <br> </font></a></p> </body> </html> 

Listing 10.4 shows the code in the PostMessage.htm file.

Listing 10.4: PostMessage.htm

 <html> <head> <title> My Forum </title> </head> <body> <img src="/books/1/524/1/html/2/main1.jpg" width="731" height="105"> <table width="730" border="0" cellspacing="0" cellpadding="0" height="14">     <tr>       <td>         <div align="center">           <font face="Haettenschweiler" size="5">Post Message Page</font></div>       </td>     </tr> </table> <pre> <form name="new1" action="PostMessage.cfm" method="Post">     <div align="center">       <font face="Verdana">User ID<input type="text" name ="user" >       <br>       Email <input type="text" name ="email" >       <br>       Subject <input type ="text" name="subject">       <br>       Message </font> <textarea cols="20" name="query" rows="5">       </textarea>       <input type="submit" value="Send">       <input type="reset">       </div></form> </pre> </body> </html> 

The code in the PostMessage.cfm file is shown in Listing 10.5.

Listing 10.5: PostMessage.cfm

 <html> <head> <title> My Forum </title> </head> <body>     <img src="/books/1/524/1/html/2/main1.jpg" width="739" height="105"><br>     <cfset pdate=#DateFormat(Now(),"mm/dd/yyyy")#>     <cfquery name="user1" datasource="Forum">       insert into Query       values('#Form.user#','#Form.email#','#Form.subject#',            '#Form.query#','#pdate#');     </cfquery>     <table width="728" border="0" cellspacing="0" cellpadding="0">       <tr>       <td>         <div align="center">           <font face="Haettenschweiler" size="4">             Your Query has been posted into the database.<br>           <a href="viewpost.htm""> Back to Main Page</a> </font>         </div>       </td>      </tr>     </table> </body> </html> 

The code in the View.cfm file is shown in Listing 10.6.

Listing 10.6: View.cfm

 <html> <head> <title> My Forum </title> </head> <body>      <img src="/books/1/524/1/html/2/main1.jpg" width="775" height="105">      <cfquery name="user1" datasource="Forum">        select * from Query      </cfquery>      <table border="1" width="702" align="center">        <tr> </tr>        <tr>          <td width=20%>            <font face="Haettenschweiler" size="3" color="#990033">              UserName            </font>          </td>          <td width=80%>            <font face="Haettenschweiler" size="3" color="#990033">              Post Date </font>          <td width=80%>            <font face="Haettenschweiler" size="3" color="#990033">Email</font>          <td width=80%>            <font face="Haettenschweiler" size="3" color="#990033">Subject</font>       </tr>     <cfoutput query="user1">       <tr>         <td width=20%> <font face="Haettenschweiler" size="3">#UserID# </font>          </td>          <td width=80%>           <font face="Haettenschweiler">             #DateFormat(PostingDate,"mm/dd/yyyy")#</font>          <td width=80%>           <font face="Haettenschweiler">             <a href="mailto:#Email#"><font size="2" face="Verdana">#Email#           </font></a> </font>          <td width=80%> <font face="Haettenschweiler"><br>          <br>          <a href="Message.cfm?mes=#Message#">          <font face="Verdana" size="2">#Subject#</font></a></font><br>        </tr>      </cfoutput>      </table>      <font face="Haettenschweiler">      <a href="viewpost.htm">Back to User Main Page</a></font> </body> </html> 

Listing 10.7 shows the code in Message.cfm file.

Listing 10.7: Message.cfm

 <html> <head><title> My Forum</title></head> <body> <img src="/books/1/524/1/html/2/main1.jpg" width="758" height="105"><br> <table width="592" border="0" cellspacing="0" cellpadding="0">   <tr>     <td height="34"> <font face="Verdana" size="2">#mes#</font></td>   </tr>   <tr>     <td><cfoutput><a href="view.cfm"> <font face="Haettenschweiler">Return to         View Message Page</font></a> </cfoutput></td>     </tr> </table> </body> </html> 

Listing 10.8 shows the code in the DeleteMessage.cfm file.

Listing 10.8: DeleteMessage.cfm

 <html> <head> <title> My Forum </title> </head> <body> <img src="/books/1/524/1/html/2/main1.jpg" width="777" height="105"> <cfquery name="user1" datasource="new"> select * from query1  </cfquery> <table border="1" width="754" align="center" height="49">   <tr> </tr>   <tr>     <td width=20%>       <font face="Haettenschweiler" color="#993333">UserName</font></td>     <td width=80%>       <font face="Haettenschweiler" color="#993333">Posted On</font>     <td width=80%>        <font face="Haettenschweiler" color="#993333">E-Mail ID</font>      <td width=80%>        <font face="Haettenschweiler" color="#993333">Subject</font>      <td width=80%>        <font face="Haettenschweiler" color="#993333">Delete Message</font>    </tr>    <cfoutput query="user1">      <tr>       <td width=20%>          <font face="Haettenschweiler" size="3" color="##000000">#UserID#          </font></td>       <td width=80%>         <font face="Haettenschweiler" size="3" color="##000000">          #DateFormat(PostingDate,"mm/dd/yyyy")#</font>       <td width=80%>         <font face="Haettenschweiler" size="3" color="##000000">          <a href="mailto:#Email#">#Email#          </a>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;        </font>      <td width=80%>         <font face="Haettenschweiler" size="3" color="##000000">         <a href="Message.cfm?mes=#Message#">#Subject#</a></font>      <td width=80%>         <font face="Haettenschweiler" size="3" color="##000000">         <a href="delete.cfm?sub=#Subject#">Delete</a><br>         </font>     </tr>   </cfoutput> </table> <a href="viewpost.htm"> <font face="Haettenschweiler">Back to User Main page</font></a> </body> </html> 

Listing 10.9 shows the code in the Delete.cfm file.

Listing 10.9: Delete.cfm

 <html> <head> <title> My Forum </title> </head> <body> <img src="/books/1/524/1/html/2/main1.jpg" width="758" height="105"><br> <cfquery name="user1" datasource="Forum"> Delete from Query where Subject='#sub#' </cfquery> <table width="737" border="0" cellspacing="0" cellpadding="0">   <tr>     <td height="19">       <div align="center">        <font face="Haettenschweiler">           Your Message has been deleted from the database        </font>       </div>     </td>   </tr>   <tr>     <td>       <div align="center">         <font face="Haettenschweiler">          <a href="DeleteMessage.cfm"> Back to Delete Message page</a></font>       </div>     </td>   </tr> </table> </body> </html>