HostMySite.Com is sponsoring this tutorial, please visit their site today!

Delete Multiple Database Records Using A Checkbox by: Marlene Murphy

[Download in PDF Format] [Download in FlashPaper Format]

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">

<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>

<!--- 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!

HostMySite.Com is sponsoring this tutorial, please visit their site today!

Please rate this tutorial:

Post a new comment on this tutorial
post a new comment on this particular tutorial
Your Name:
Your Email:
Comment Title:
Comments:
Key Phrase: