Deleting records from your database.

This tutorial will demonstrate how to delete records from the database. 

IMPORTANT NOTE: The first thing you must understand is that delete is final (in other words, there's no going back so backup your database before doing anything!)

OK, deleting data from your database is really a very simple procedure, let's start a step before that which is on the page where you list your records. (We'll call it list.cfm)

<!--- LIST.CFM --->
<!--- first, let's get our records to display to the user --->
<cfquery name="qMyQuery" datasource="MyDSN">
    SELECT         ID, TITLE, DESCRIPTION
    FROM           TableName
    ORDER BY     Title
</cfquery>

<!--- Now display the records to the user --->
<cfoutput query="qMyQuery">
    #title# - #description#<BR>
     <a href="delete.cfm?id=#id#" onClick="return confirm('Are You Sure You Want To Delete This Record?');">Delete</a>
    <HR>
</cfquery>
<!--- Now put an option to delete all records at once --->
<a href="delete.cfm" onClick="return confirm('Are You Sure You Want To Delete ALL Records?');">Delete All Records</a>

Notice you passed a value called ID (This is the ID that makes this record different), that also created a hyperlink to a page called "delete" this is where the delete will take place. Let's take a look at the delete.cfm page now... Also note that there is an OnClick value, this will confirm that you really do want to delete it, in case you click on a wrong link! :)

<!--- DELETE.CFM --->
<!--- This is the query that will delete the data --->
<cfquery name="qDelete" datasource="MyDSN">
    DELETE FROM TableName
    WHERE ID = #id#
</cfquery>

Ok, now let me explain the code a bit... the code is basically a query that deletes a record/row from a table where the ID is the ID that was passed from the LIST page. If you wanted to delete all records in a table, then simply remove the WHERE code...

It's that simple, but remember (actually deleting data is not a good thing...) as an alternative to deleting data, I suggest you add a field to the database table called "Active" (BIT or Yes/No Format) or something like that... then if the user wants to delete, simply set that value to 0 for delete, and 1 for active or available. By not deleting you can always access it at a later time, if you delete a value... then it's gone forever.....

Questions? Comments? Email me...