Deleting Information from a Database

I l @ ve RuBoard

There will be times when you need to remove information from a database. The information could be outdated , a product may be sold outwhatever the reason, you still need to delete it. And deleting, like inserting or updating, is simple with ColdFusion.

1. Continuing with productmenu.cfm, add the following link directly after the initial link for updating:

    <a href="productdelete.cfm?productid=#productid#">Delete</a> 

   You have now created a link that will be inserted after every product's name that, when clicked, will delete it from the database. When this link is clicked, the product's entire record will be removed from the database . Once again, you've had to pass the product ID to the template working with the database in order for it to know what to delete.

2. Choose File > New and select Blank Document from the New Document window.

   This blank document is where the query to actually delete the information will take place. This query, like the others, will not contain any HTML.

   

3. Add the first line of the query:

    <cfquery name="productdelete" datasource="products"> 

   As usual, you must specify the <cfquery> tag with a name and a data source to work with.

   

4. Add the following SQL:

    delete  from tblProducts 

   By using the Delete keyword you are deleting all the information from the table you specify, (in this case the tblProducts table). Be very careful when using the Delete keywordone misstep and you can lose all the data in your table!

   

5. Add the clause where productid = #url.productid# to your Delete statement.

   Just like the Update statement, the Delete statement needs to know which specific customer to work with. If this where statement were not present, all the records in the table would be deleted.

   

6. Add the closing </cfquery> tag.

   

   Your Delete statement is now complete and functional. The last thing to do, as with the other processing pages, is to send the user back to another page after the processing occurs. In this case, it makes the most sense to return the user to the productmenu.cfm template, where they will be able to verify that the customer was indeed deleted.

7. Add the following CFML after your query:

    <cflocation url="productmenu.cfm"> 

   Once again, you are sending the user back to the productmenu.cfm template after the query has finished processing.

   

8. Choose File > Save and save the file as productdelete.cfm in the Lesson6/Start folder on your local hard drive.

   Now that the file is saved, you can test it out to make sure that it works. But remember that if you do delete products, the records are really gone, and if you wanted them in the database again, you would have to add them back manually.

   

9. Open a browser and type http://127.0.0.1/Lesson6/Start/productmenu.cfm into the address field.

   This will once again bring up your productmenu.cfm template, which now includes the Delete link. When you click this link, the product associated with that link will be removed from the database.

   

10. Click Delete next to one of the names .

    This will remove the product to the left of the link from the database and the productmenu.cfm will reload. In our example, notice that the leather case product is no longer on the list.

I l @ ve RuBoard