• Home
• Tutorials
• Forums
• Reference
• Developers
• Advertise
• About Us

Delete Multiple Database Records Using A Checkbox
SELECT AND DELETE MULTIPLE DATABASE RECORDS WITH A CHECKBOX

---

Deleting one record or "all" records from a database table is fairly straightforward and there is a good tutorial (#156) on that topic at easyCFM.com. Sometimes, though, to save time you may want to select multiple records for deletion just by checking a box next to the record listing.

This tutorial explains one method of accomplishing this. The first step is pretty easy:

1. Create a SELECT query to list all the records and display them in a table form (just to keep things nice and neat) with a checkbox next to each record. My table is a mailing list. The trick here is to create a unique formfield name for each checkbox created for each displayed record which I'll explain in step 2.

<!--- Get Information from mailing list table --->
<CFQUERY DATASOURCE="#DSN#" NAME="getMailingList">
SELECT memberID, Firstname, Lastname, Email, city, state, country, inputDate, EmailType
FROM tMembers
Order By Lastname, Firstname
</CFQUERY>

<!--- Display Page Header --->
<CFINCLUDE TEMPLATE="adminheader.cfm">

<!--- Create a Form to display the query results and send form info to our action page --->

<CFFORM ACTION="mailingListDelete.cfm" METHOD="POST">
<!--- Outer Container Table for Form (Adds borders and dresses up the display) --->

<TABLE WIDTH="600" BORDER="1" ALIGN="CENTER">
<TR>
<TD>

<!--- Table to hold query info --->
<TABLE WIDTH="600">
<TR>
<TD COLSPAN="8"><DIV ALIGN="CENTER"><B>Mailing List</B></DIV></TD>
</TR>
<TR>
<TD COLSPAN="8"><DIV ALIGN="CENTER">

<CFOUTPUT>#getMailingList.RecordCount#</CFOUTPUT> Mailing List Members<BR>
To remove person from mailing list, select the checkbox
next to their name and click Delete Members</DIV></TD>
</TR>

<!--- Header row to display column names --->
<TR>
<TH>Del</TH>
<TH>Name</TH>
<TH>City</TH>
<TH>State</TH>
<TH>Country</TH>
<TH>Email</TH>
<TH>Date Reg.</TH>
<TH>Email</TH>
</TR>

<CFOUTPUT QUERY="getMailingList">

2. The first column in my table includes a checkbox with a unique name tied to my primary key, called "memberID". I've named the checkbox "DEL_#memberID#". As the output query goes through each item, it will add the unique memberID to DEL_, which means I'll end up with unique checkbox form names. For example, DEL_145 (where the member ID is "145"). When we get to the action page, we'll trim off the beginning and use the memberID to select the records to delete.

<TR>
<TD><INPUT TYPE="checkbox" NAME="DEL_#memberID#" VALUE="YES"></TD>
<TD>#LastName#, #FirstName#</TD>
<TD>#City#</TD>
<td>#State#</td>
<TD>#Country#</TD>
<td>#Email#</td>
<td>#DateFormat(inputDate,"mm/dd/yy")#</td>
<td>#EmailType#</td>
</TR>
</CFOUTPUT>
<tr>
<td colspan="8">
<INPUT TYPE="submit" VALUE="Delete Members"></td>

</tr>
</TABLE></TD></TR></TABLE>
</CFFORM>
</body></html>

---

Okay...that takes care of our mailing list form table with the checkboxes. Now we need to create our action page, called "mailingListDelete.cfm". We're going to loop through all of the form field names (actually, the only formfield we have is called DEL_#memberID#).

3. Verify that a record was selected for deletion and a field name was passed to the action page. In case someone clicked "Delete Members" without selecting a record to delete, we need to bypass the delete procedure and display a message to the user.

<CFIF IsDefined("Form.FieldNames")>

4. We know that each Checkbox formfield begins with DEL_ (which is 4 characters), so we're going to remove the first four characters from the formfield name with the RemoveChar function to end up with the memberID.

<!--- This loop pulls the memberID from the Delete FieldName --->
<!--- then runs the delete query for that memberID --->

<cfloop index="i" list="#FORM.FieldNames#" DELIMITERS="," >
<cfif LEFT(i, 4) IS "DEL_" >

<CFSET selectMemberID = RemoveChars(i, 1, 4)>
<CFSET selectMemberID = #Evaluate(selectMemberID)#>
</cfif>

<!--- Delete Query for selected member IDs --->
<CFQUERY DATASOURCE="#DSN#" NAME="deleteMembers">
DELETE
FROM tMembers
WHERE memberID = #SelectMemberID# [SEE TUTORIAL UPDATE AT BOTTOM OF PAGE]
</CFQUERY>
</cfloop>

5. That's it! Now I'm just going to display a message to the user that the selected records have been deleted, or if no records were selected by checking the checkbox, I'm going to tell them that.

<!--- Display Page Header --->
<CFINCLUDE TEMPLATE="adminheader.cfm">

<!--- Table to display success message to user --->
<table WIDTH="600" HEIGHT="50%" ALIGN="CENTER">
<tr>
<td ALIGN="CENTER"> <H2>SELECTED MAILING LIST MEMBERS DELETED! </H2>
<H2><A HREF="mailingList.cfm">DELETE MORE MEMBERS</A></H2></td></tr></table>

<CFELSE>
<!--- Table to display message to user that no records were selected for deletion --->

<!--- Display Page Header --->
<CFINCLUDE TEMPLATE="adminheader.cfm">
<table WIDTH="600" HEIGHT="50%" ALIGN="CENTER">
<tr>
<td ALIGN="CENTER"> <H2>NO MAILING LIST MEMBERS SELECTED FOR DELETION </H2>
<H2><A HREF="mailingList.cfm">GO TO MEMBERLIST</A></H2></td></tr></table>

</CFIF>
</body></html>

---

TUTORIAL UPDATES 03/11/04:

Since this tutorial was originally submitted, a couple of issues have arisen which need to be addressed:
1. If the fields you are using to select a record to delete are NOT numerical, but are textfields, you must be sure to put single quotes around your variables. For example, if your product ID includes alpha-numeric characters, you need to add quotes:

WHERE productID = '#SelectproductID#'

2. It was brought to my attention by another Forum member that my original tutorial included a name for the "submit" button, which would produce an error, so I have removed the name= variable from the submit button in this tutorial. Sorry if it caused anyone problems!