This is a complete CRUD application: it creates, reads, updates, and deletes employee records. It's built from three files, a schema, a list page, and a form page, and every code block here is one of those files in full. Nothing depends on an existing project, so you can build it from this page alone.
Learning Objectives
After working through this project, you'll be able to:
- Design a table and its constraints for a set of records.
- Write the four CRUD operations with queryExecute and named parameters.
- Use one form for both adding and editing, and validate it on the server.
- Search records with LIKE using a parameter, not string concatenation.
- Identify the access-control and CSRF gaps that remain in a real CRUD app.
How the Application Fits Together
Schema Defines the Table
constraints live in the database
List Page Reads and Deletes
employees.cfm, searchable
Form Page Creates and Updates
employee-form.cfm, validated
Redirect After Every Write
so a refresh can't resubmit
Step 1: The Schema
The table holds the fields the form collects. The email column is UNIQUE and NOT NULL, so the database itself refuses duplicates even if the application check is skipped. This is SQL Server syntax, the MySQL version differs only in the auto-increment keyword.
CREATE TABLE employees (
id INT IDENTITY(1,1) PRIMARY KEY,
first_name VARCHAR(50) NOT NULL,
last_name VARCHAR(50) NOT NULL,
email VARCHAR(100) NOT NULL UNIQUE,
department VARCHAR(50),
salary DECIMAL(10,2) NOT NULL,
hire_date DATE NOT NULL
);Step 2: The Application Settings
Application.cfc stores the datasource name once, so every page reads it from application scope rather than repeating the name. Change the value here if your datasource has a different name.
component {
this.name = "EmployeeApp";
this.sessionManagement = true;
function onApplicationStart() {
application.datasource = "employeedb";
return true;
}
}Step 3: The List Page: Search and Delete
employees.cfm lists records, filters them with a search term, and handles delete. Delete is a POST, never a GET link, so that a link or an image on another site can't delete a record. The search term is a bound parameter, and the % wildcards are added in code, not typed into the SQL.
<cfparam name="url.q" default="">
<cfparam name="form.deleteId" default="0">
<cfif cgi.request_method EQ "POST" AND val(form.deleteId) GT 0>
<cfset queryExecute(
"DELETE FROM employees WHERE id = :id",
{ id: { value: val(form.deleteId), cfsqltype: "cf_sql_integer" } },
{ datasource: application.datasource }
)>
<cflocation url="employees.cfm" addtoken="false">
</cfif>
<cfset employees = queryExecute(
"SELECT id, first_name, last_name, email, department, salary, hire_date
FROM employees
WHERE first_name LIKE :q OR last_name LIKE :q OR department LIKE :q
ORDER BY last_name, first_name",
{ q: { value: "%" & trim(url.q) & "%", cfsqltype: "cf_sql_varchar" } },
{ datasource: application.datasource, maxrows: 100 }
)>
<h1>Employees</h1>
<form method="get" action="employees.cfm">
<input type="text" name="q" value="#encodeForHTMLAttribute(url.q)#" placeholder="Search name or department">
<button type="submit">Search</button>
</form>
<p><a href="employee-form.cfm">Add employee</a></p>
<table>
<tr><th>Name</th><th>Email</th><th>Department</th><th>Salary</th><th>Hired</th><th></th></tr>
<cfoutput query="employees">
<tr>
<td>#encodeForHTML(first_name)# #encodeForHTML(last_name)#</td>
<td>#encodeForHTML(email)#</td>
<td>#encodeForHTML(department)#</td>
<td>#salary#</td>
<td>#dateFormat(hire_date, "yyyy-mm-dd")#</td>
<td>
<a href="employee-form.cfm?id=#id#">Edit</a>
<form method="post" action="employees.cfm" style="display:inline"
onsubmit="return confirm('Delete this employee?')">
<input type="hidden" name="deleteId" value="#id#">
<button type="submit">Delete</button>
</form>
</td>
</tr>
</cfoutput>
</table>The maxrows option caps the result at 100 rows. A list with no cap is a slow page waiting to happen once the table grows, but pagination needs a database-specific OFFSET clause, which the Practice Project leaves as an extension.
Step 4: The Form: Add and Edit in One Page
employee-form.cfm handles both cases. With an id in the URL, it loads that row to edit it. Without one, it adds a new row. Validation runs on the server for every field, and the email uniqueness check excludes the record being edited, so saving an employee without changing their email doesn't count as a duplicate.
<cfparam name="url.id" default="0">
<cfset id = val(url.id)>
<cfset errors = {}>
<cfset values = { first_name = "", last_name = "", email = "", department = "", salary = "", hire_date = "" }>
<cfif cgi.request_method EQ "GET" AND id GT 0>
<cfset existing = queryExecute(
"SELECT first_name, last_name, email, department, salary, hire_date FROM employees WHERE id = :id",
{ id: { value: id, cfsqltype: "cf_sql_integer" } },
{ datasource: application.datasource }
)>
<cfif existing.recordCount>
<cfset values = existing.getRow(1)>
<cfset values.salary = values.salary & "">
<cfset values.hire_date = dateFormat(values.hire_date, "yyyy-mm-dd")>
</cfif>
</cfif>
<cfif cgi.request_method EQ "POST">
<cfloop collection="#values#" item="key">
<cfset values[key] = trim(form[key] ?: "")>
</cfloop>
<cfif NOT len(values.first_name)><cfset errors.first_name = "Enter a first name."></cfif>
<cfif NOT len(values.last_name)><cfset errors.last_name = "Enter a last name."></cfif>
<cfif NOT isValid("email", values.email)><cfset errors.email = "Enter a valid email address."></cfif>
<cfif NOT isNumeric(values.salary)><cfset errors.salary = "Salary must be a number."></cfif>
<cfif NOT isDate(values.hire_date)><cfset errors.hire_date = "Enter a valid hire date."></cfif>
<cfif structIsEmpty(errors)>
<cfset duplicate = queryExecute(
"SELECT id FROM employees WHERE email = :email AND id <> :id",
{
email: { value: values.email, cfsqltype: "cf_sql_varchar" },
id: { value: id, cfsqltype: "cf_sql_integer" }
},
{ datasource: application.datasource }
)>
<cfif duplicate.recordCount>
<cfset errors.email = "Another employee already uses this email.">
</cfif>
</cfif>
<cfif structIsEmpty(errors)>
<cfif id GT 0>
<cfset queryExecute(
"UPDATE employees SET first_name = :fn, last_name = :ln, email = :em,
department = :dp, salary = :sa, hire_date = :hd WHERE id = :id",
{
fn: { value: values.first_name, cfsqltype: "cf_sql_varchar" },
ln: { value: values.last_name, cfsqltype: "cf_sql_varchar" },
em: { value: values.email, cfsqltype: "cf_sql_varchar" },
dp: { value: values.department, cfsqltype: "cf_sql_varchar" },
sa: { value: values.salary, cfsqltype: "cf_sql_decimal" },
hd: { value: values.hire_date, cfsqltype: "cf_sql_date" },
id: { value: id, cfsqltype: "cf_sql_integer" }
},
{ datasource: application.datasource }
)>
<cfelse>
<cfset queryExecute(
"INSERT INTO employees (first_name, last_name, email, department, salary, hire_date)
VALUES (:fn, :ln, :em, :dp, :sa, :hd)",
{
fn: { value: values.first_name, cfsqltype: "cf_sql_varchar" },
ln: { value: values.last_name, cfsqltype: "cf_sql_varchar" },
em: { value: values.email, cfsqltype: "cf_sql_varchar" },
dp: { value: values.department, cfsqltype: "cf_sql_varchar" },
sa: { value: values.salary, cfsqltype: "cf_sql_decimal" },
hd: { value: values.hire_date, cfsqltype: "cf_sql_date" }
},
{ datasource: application.datasource }
)>
</cfif>
<cflocation url="employees.cfm" addtoken="false">
</cfif>
</cfif>
<cfoutput>
<h1>#id GT 0 ? "Edit Employee" : "Add Employee"#</h1>
<form method="post" action="employee-form.cfm?id=#id#">
<label>First name</label>
<input type="text" name="first_name" value="#encodeForHTMLAttribute(values.first_name)#">
<cfif structKeyExists(errors, "first_name")><div>#encodeForHTML(errors.first_name)#</div></cfif>
<label>Last name</label>
<input type="text" name="last_name" value="#encodeForHTMLAttribute(values.last_name)#">
<cfif structKeyExists(errors, "last_name")><div>#encodeForHTML(errors.last_name)#</div></cfif>
<label>Email</label>
<input type="email" name="email" value="#encodeForHTMLAttribute(values.email)#">
<cfif structKeyExists(errors, "email")><div>#encodeForHTML(errors.email)#</div></cfif>
<label>Department</label>
<input type="text" name="department" value="#encodeForHTMLAttribute(values.department)#">
<label>Salary</label>
<input type="text" name="salary" value="#encodeForHTMLAttribute(values.salary)#">
<cfif structKeyExists(errors, "salary")><div>#encodeForHTML(errors.salary)#</div></cfif>
<label>Hire date (yyyy-mm-dd)</label>
<input type="text" name="hire_date" value="#encodeForHTMLAttribute(values.hire_date)#">
<cfif structKeyExists(errors, "hire_date")><div>#encodeForHTML(errors.hire_date)#</div></cfif>
<button type="submit">Save</button>
</form>
</cfoutput>A GET request only loads the row, it never writes. Every write happens on POST, followed by a redirect, so refreshing the browser can't submit the same insert twice.
A Real Detail: The Loop Copies Form Values Into the Struct
The cfloop over values copies each form field into the same struct, after trimming it. That keeps the field list in one place: add a field to the struct and the loop picks it up, instead of listing each form field twice. The form[key] ?: "" part treats a missing field as empty rather than an error.
Security Review: What This Application Still Lacks
| Gap | Where | What to add |
|---|---|---|
| No login: anyone can add, edit, or delete employees | employees.cfm, employee-form.cfm | Put a session check on both pages, as the Login System lesson shows, and redirect to a login page |
| Delete has no CSRF token | employees.cfm delete form | Issue a per-session token in the form and check it on POST, so another site can't submit the delete |
| No rate limit or audit log | Both pages | Log every write with the user and the record id, so a bad change can be traced |
| Salary is stored as typed | employee-form.cfm | Accept only values in a sensible range, and store a number, not the text the user typed |
The application already does the things that are easy to forget: every query takes its values as parameters, every output is encoded, writes happen only on POST, and the database enforces the unique email.
Common Beginner Mistakes
Deleting with a GET link
A link or image on another site can trigger a GET request, so a visitor's browser can delete a record without them meaning to. Deletes should be POST requests.
Checking for duplicate emails without excluding the record being edited
Saving an employee without changing their email then fails as a duplicate of themselves. Exclude the current id from the check.
Rendering with a list of rows and relying on the database for everything
The database enforces UNIQUE, but the form needs its own error message. Check in the application first so the user sees a clear message, and keep the constraint as the safety net.
Interview Questions
Why do writes redirect after saving?
A redirect after a POST (the Post/Redirect/Get pattern) means a browser refresh reloads the list page, not the insert. Without it, refreshing resubmits the form.
Why bind the search term instead of concatenating it into the SQL?
Concatenating a search string lets a crafted search change the query. A bound parameter is always treated as data, even if it contains SQL.
What should happen if two users try to save the same new email at once?
The UNIQUE constraint lets one insert succeed and rejects the other, so the application needs to catch that database error and show the same duplicate-email message the pre-check shows.
Summary
You built a complete CRUD application: a schema with a unique email constraint, a searchable list with a POST-based delete, and one form that adds and edits records, validated on the server and redirected after every write. The security review lists the work still open, login, CSRF on delete, and an audit trail, which is the next layer for a real deployment.
What's Next?
The last Practice Project is Blog CMS, which adds published and draft content and a public page per post.