O'Reilly logo

Learning PHP and MySQL by Jon A. Phillips, Michele E. Davis

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

Deleting Data

Use the DELETE command to completely remove existing data from the database. Remember though, once you've deleted data, it can no longer be retrieved; it is permanently gone. Make sure you have appropriate checks and balances in place for the deletion of existing data. Use the WHERE command so that you don't delete data from all the rows in your table.

The command TRUNCATE TABLE `tablename` deletes an entire table, which means the table structure and the records, and then recreates the structure. Technically, your final result is the same, but our example is a safer way to perform a delete. The advantage of TRUNCATE is that it's much faster for deleting large tables.

Warning

While it's great to be able to DROP and TRUNCATE tables from PHP, you probably don't want to leave this capability anywhere on your web site for an average user.

There is a way to safeguard against erroneous selections by running the query using SELECT instead of DELETE. Deleting data from a MySQL database through PHP works similarly to any of the other queries. If you do this, query results display which row or rows are going to be affected by your deletion. Let's modify the example to provide a link that deletes the current row. In Example 12-9, you'll delete a purchase.

Example 12-9. Providing a link to delete a purchase in deletion_link.php

<?php require_once('db_login.php'); require_once('DB.php'); $connection = DB::connect("mysql://$db_username:$db_password@$db_host/$db_database"); if (DB::isError($connection)){ ...

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required