Managing the Resources Table - Exercise

Contact Us or call 1-877-932-8228
Managing the Resources Table - Exercise

Managing the Resources Table

Duration: 30 to 45 minutes.

This exercise involves making changes to three pages: ManagingData/Exercises/Resources.cfm, ManagingData/Exercises/ResourceAdd.cfm, and ManagingData/Exercises/ResourceEdit.cfm. The files have comments showing where to edit or add code.

  1. Open ManagingData/Exercises/Resources.cfm in your editor.
  2. If the user is not logged in, set userid to 0. Otherwise set userid to the user's userid as contained in the Session.
  3. Make the table headers into links that, when clicked, sort the table on that field.
  4. Open ManagingData/Exercises/ResourceAdd.cfm in your editor.
  5. Write a <cfquery> to insert the resource entry into the database. The database fields are resourcename, link, active, userid, and description.
  6. Open ManagingData/Exercises/ResourceEdit.cfm in your editor.
  7. Write a condition to check if the resource should be deleted or updated and write to queries to handle the appropriate operation.

Code Sample:

ManagingData/Exercises/Resources.cfm
               <!---
	If the user is not logged in, set userid to 0.
	Otherwise set userid to the user's userid as contained in the SESSION.
--->
<cfparam name="URL.orderfield" default="resourceid">

<cfset orderfields = "resourcename, description, firstname">

<cfif NOT ListContains(orderfields,URL.orderfield)>
	<cfset URL.orderfield = "resourceid">
</cfif>
<cfquery name="getresources" datasource="#APPLICATION.datasource#">
	SELECT resourceid, resourcename, link, active, description, u.userid, firstname, lastname, email
	FROM Resources r, Users u
	WHERE u.userid = r.userid
	ORDER BY #URL.orderfield#
</cfquery>
<html>
<head>
<title>Running Resources</title>
</head>

<body>
<h1>Running Resources</h1>
<cfif userid NEQ 0>
	<a href="ResourceAdd.cfm">Add a resource</a>
</cfif>
<table border="1" cellpadding="3" cellspacing="0" id="maintable">
<!---
	Make the table headers into links that, when clicked, sort the table on that field
--->
<tr>
	<th>Resource</th>
	<th>Description</th>
	<th>Added by</th>
	<th>Edit/Delete</th>
</tr>
<cfoutput query="getresources">
	<tr valign="top">
		<td>
			<a href="#link#">#resourcename#</a>
		</td>
		<td>#description#</td>
		<td>
			<a href="mailto:#email#">#firstname# #lastname#</a>
		</td>
		<cfif Variables.userid EQ getresources.userid OR  (isDefined("SESSION.role") AND SESSION.role EQ "admin")>
			<td>
			<form method="post" action="ResourceEdit.cfm" style="margin-bottom:0px">
				<input type="hidden" name="resourceid" value="#resourceid#">
				<input type="submit" value="EDIT" style="font-size:xx-small">
				<input type="submit" name="deleting" value="DELETE" style="font-size:xx-small">
			</form>
			</td>
		<cfelse>
			<td>PROTECTED</td>
		</cfif>
	</tr>
</cfoutput>
</table>
<cfinclude template="Includes/Footer.cfm">
</body>
</html>

Code Sample:

ManagingData/Exercises/ResourceAdd.cfm
<cfif NOT isDefined("SESSION.userid")>
	<cflocation url="index.cfm" addtoken="no">
</cfif>

<cfparam name="FORM.resourcename" default="">
<cfparam name="FORM.link" default="">
<cfparam name="FORM.description" default=""> 

<html>
<head>
<title>Add a Resource</title>
</head>

<body>
<cfif isDefined("FORM.submitted")>
<!---
	Write a <cfquery> to insert the resource entry into the database.
	The database fields are resourcename, link, active, userid, and description.
--->
	<p class="success">Resource Added.<br>
	<a href="resources.cfm">Return to Resource List</a>.</p>
	<h1>Add Another Resource</h1>
<cfelse>
	<h1>Add Resource</h1>
</cfif>

<cfoutput>
<form method="post" action="#CGI.SCRIPT_NAME#">
</cfoutput>
<input type="hidden" name="submitted" value="true">
<cfoutput><input type="hidden" name="userid" value="#SESSION.userID#"></cfoutput>
<table>
<tr>
	<td>Resource Name:</td>
	<td><input name="resourcename" type="text" size="30"maxlength="50"></td>
</tr>
<tr>
	<td>URL:</td>
	<td><input name="link" type="text" size="30" maxlength="50"></td>
</tr>
<tr valign="top">
	<td>Description:</td>
	<td>
		<textarea name="description" rows="5" cols="40" wrap="soft"></textarea>
	</td>
</tr>
<tr>
	<td colspan="2" align="right"><input type="submit" value="Add Resource"></td>
</tr>
</table>
</form>
<cfinclude template="Includes/Footer.cfm">
</body>
</html>

Code Sample:

ManagingData/Exercises/ResourceEdit.cfm
<cfif NOT isDefined("SESSION.userid") OR NOT isDefined("FORM.resourceid")>
	<cflocation url="index.cfm" addtoken="no">
</cfif>

<html>
<head>
<title>Edit Resource</title>
</head>

<body>
<cfif WRITE CONDITION TO CHECK IF RESOURCE SHOULD BE DELETED>
<!---
	Write a <cfquery> to update the resource entry in the database.
	The database fields are resourcename, link, active, description, and resourceid.
--->
	<cflocation url="Resources.cfm" addtoken="no">
<cfelseif WRITE CONDITION TO CHECK IF RESOURCE SHOULD BE EDITED>
<!---
	Write a <cfquery> to update the resource entry in the database.
	The database fields are resourcename, link, active, description, and resourceid.
--->
	<p class="success">Resource Updated.<br>
	<a href="resources.cfm">Return to Resource List</a>.</p>
<cfelse>
	<h1>Update Resource</h1>
</cfif>

<cfquery name="getresourceinfo" datasource="#APPLICATION.datasource#">
	SELECT userid, resourcename, description, link, active
	FROM Resources
	WHERE resourceid = <cfqueryparam value="#FORM.resourceid#" cfsqltype="cf_sql_integer">
</cfquery>

<cfoutput>
<form method="post" action="#CGI.SCRIPT_NAME#">
<input type="hidden" name="editing" value="true">
<input type="hidden" name="resourceid" value="#FORM.resourceid#">
<table>
<tr>
	<td>Resource Name:</td>
	<td><input name="resourcename" value="#getresourceinfo.resourcename#" type="text" size="30" maxlength="50"></td>
</tr>
<tr>
	<td>URL:</td>
	<td><input name="link" value="#getresourceinfo.link#" type="text" size="30"  maxlength="50"></td>
</tr>
<tr>
	<td>Active:</td>
	<td>
		<input name="active" value="1" type="radio"<cfif getresourceinfo.active EQ 1> checked</cfif>>Yes
		<input name="active" value="0" type="radio"<cfif getresourceinfo.active EQ 0> checked</cfif>>No
	</td>
</tr>
<tr valign="top">
	<td>Description:</td>
	<td>
		<textarea name="description" rows="5" cols="40" wrap="soft">#getResourceInfo.description#</textarea>
	</td>
</tr>
<tr>
	<td colspan="2" align="right"><input type="submit" value="Update Resource"></td>
</tr>
</table>
</form>
</cfoutput>
<cfinclude template="Includes/Footer.cfm">
</body>
</html>

Solution:

ManagingData/Solutions/Resources.cfm
               <cfif isDefined("SESSION.userid")>
	<cfset userid = SESSION.userid>
<cfelse>
	<cfset userid = 0>
</cfif>
---- C O D E   O M I T T E D ----
<tr>
	<th><a href="resources.cfm?orderfield=resourcename">Resource</a></th>
	<th><a href="resources.cfm?orderfield=description">Description</a></th>
	<th><a href="resources.cfm?orderfield=firstname">Added by</a></th>
	<th>Edit/Delete</th>
</tr>
---- C O D E   O M I T T E D ----

Solution:

ManagingData/Solutions/ResourceAdd.cfm
---- C O D E   O M I T T E D ----
<cfif isDefined("FORM.submitted")>
		<cfquery datasource="#APPLICATION.datasource#">
			INSERT INTO Resources
			(resourcename, link, description, active, userid)
			VALUES('#FORM.resourcename#','#FORM.link#','#FORM.description#',1,#SESSION.userid#)
		</cfquery>
	<p class="success">Resource Added.<br>
	<a href="resources.cfm">Return to Resource List</a>.</p>
	<h1>Add Another Resource</h1>
<cfelse>
---- C O D E   O M I T T E D ----

Solution:

ManagingData/Solutions/ResourceEdit.cfm
---- C O D E   O M I T T E D ----
<cfif isDefined("FORM.deleting")>
	<cfquery datasource="#APPLICATION.datasource#">
		DELETE FROM Resources
		WHERE resourceid = <cfqueryparam value="#FORM.resourceid#" cfsqltype="cf_sql_integer">
	</cfquery>
	
	<cflocation url="Resources.cfm" addtoken="no">
<cfelseif isDefined("FORM.editing")>
	<cfquery datasource="#APPLICATION.datasource#">
		UPDATE Resources
		SET resourcename = '#FORM.resourcename#',
			description = '#FORM.description#',
			link = '#FORM.link#',
			active = #FORM.active#
		WHERE resourceid = <cfqueryparam value="#FORM.resourceid#" cfsqltype="cf_sql_integer">
	</cfquery>
	<p class="success">Resource Updated.<br>
	<a href="resources.cfm">Return to Resource List</a>.</p>
<cfelse>
---- C O D E   O M I T T E D ----

Next