Section 12.6. Deleting Data | Learning PHP and MySQL

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

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)){ die ("Could not connect to the database: <br />". DB::errorMessage($connection)); } $query = "SELECT * FROM `purchases` NATURAL JOIN `books`"; $result = $connection->query($query); if (DB::isError($result)){ die("Could not query the database: <br />".$query." ".DB::errorMessage($result)); } echo '<table border="1">'; echo "<tr><th>User</th><th>Title</th><th>Purchased</th><th>Remove</th></tr>"; while ($result_row = $result->fetchRow(DB_FETCHMODE_ASSOC)) { echo "<tr><td>"; echo $result_row["user_id"] . '</td><td>'; echo $result_row["title"] . '</td><td>'; echo $result_row["purchased"] . '</td><td>'; echo '<a href="delete.php?purchase_id='.$result_row["purchase_id"].'">Click to remove from purchases</a></td></tr>'; } echo '</table>'; $connection->disconnect(); ?>

In Example 12-9, you're using the SELECT command so that data won't be erroneously deleted when the query is run. The results of this query display which row(s) are affected by the deletion. The script that handles the actual deletion is shown in Example 12-10.

Example 12-10. The delete.php code for performing a delete

 <?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)){ die ("Could not connect to the database: <br />". DB::errorMessage($connection)); } $purchase_id = $_GET["purchase_id"]; $query = "DELETE FROM `purchases` WHERE `purchase_id`=$purchase_id"; $result = $connection->query($query); if (DB::isError($result)){ die("Could not query the database: <br />".$query." ".DB::errorMessage($result)); } ?> <html> <head> <title>Item deleted!</title> <meta http-equiv="refresh" content="4; url=deletion_link.php"> </head> <body> Item deleted!<br /> <?php $query = "SELECT * FROM `purchases` NATURAL JOIN `books` NATURAL JOIN `authors`"; $result = $connection->query($query); if (DB::isError($result)){ die("Could not query the database: <br />".$query." ".DB::errorMessage($result)); } echo '<table border="1">'; echo "<tr><th>User</th><th>Title</th><th>Pages</th>"; echo "<th>Author</th><th>Purchased</th></tr>"; while ($result_row = $result->fetchRow(DB_FETCHMODE_ASSOC)) { echo "<tr><td>"; echo $result_row["user_id"] . '</td><td>'; echo $result_row["title"] . '</td><td>'; echo $result_row["pages"] . '</td><td>'; echo $result_row["author"] . "</td><td>"; echo $result_row["purchased"] . "</td></tr>"; } echo "</table>"; $connection->disconnect(); ?> </body> </html>

Figure 12-10 shows how the browser window looks after going to deletion_link.php.

Figure 12-10. Each purchase has a link for its removal

Click on the last removal link to see Figure 12-11.

Figure 12-11. A successful delete, and the book is removed from the purchases

The purchase is no longer in the table. It's a good idea to confirm with the user before completing a deletion. This is usually handled by an intermediate screen that summarizes what's going to be deleted and requires the user to click a button that confirms the deletion.

12.6.1. Generating Unique Identifiers

In our examples so far, we always let MySQL pick the primary key when doing inserts by sending NULL in the key field. The downside of this is that you don't know what key value MySQL assigned your row. If you add a book and then an author, how do you know what the foreign key value is for the book to add in the authors table? Well, you can use the mysql_insert_id command to get the last auto-assigned primary key from an AUTO_INCREMENT column.

Its syntax is:

 int mysql_insert_id ( [resource link_identifier] )

If the last query generated an auto-increment, that value is returned. Zero is returned if the last query didn't generate a key. FALSE is returned if there isn't a valid database connection.

We'll add a title and an author in Example 12-11.

Example 12-11. Using mysql_insert_id to link up an author to a title

 <?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)){ die ("Could not connect to the database: <br />". DB::errorMessage($connection)); } $query = "INSERT INTO `books` VALUES (NULL,'Python in a Nutshell',600)"; $result = $connection->query($query); if (DB::isError($result)){ die("Could not query the database: <br />".$query." ".DB::errorMessage($result)); } $last_value = mysql_insert_id(); echo "The id that was created is: $last_value<br />"; $query = "INSERT INTO `authors` VALUES (NULL,$last_value,'Alex Martelli')"; $result = $connection->query($query); if (DB::isError($result)){ die("Could not query the database: <br />".$query." ".DB::errorMessage($result)); } echo "Inserted successfully!"; $connection->disconnect(); ?>

Execute mysql_insert_id directly after the INSERT statement to minimize the possibility of another INSERT statement being executed before you read the value. In a multitasking environment, you have to be aware that other processes or users may also be using the data to execute queries. Figure 12-12 shows the output of the PHP code.

Figure 12-12. We can see that the book was assigned a key value of 9

Let's check Figure 12-13 to make sure that the values were saved correctly in the database by selecting from both tables in the mysql command-line client.

Figure 12-13. Our new entries for the book and author are present

The title_id value of 9 was correctly added to the authors table.