Deleting Records
The SQL DELETE Statement is used to delete exisiting database records.
The SQL DELETE Statement is shown below:
DELETE FROM TableName WHERE criteria
The following represents a user record that will be deleted from a Personnel Database table. Clicking a Delete button calls a PHP rountine that executes an SQL DELETE statement to remove this record from the database table.
First Name:
Last Name:
Telephone:
Email:
AutoNum: (Not visible to the user)
In addition to the form controls shown above, the page also includes a hidden textbox named "AutoNum" as shown below:
<input type="hidden" name="AutoNum" value="1111">
The hidden text box value is equal to AutoNum field of the database table. This is a primary key field used to uniquely identify each record. It is important to delete records based on a value that uniquely identifies the value. Otherwise, there is the risk of deleting multiple records that were not intended to be removed. For example, if we delete a record based on the user’s email address, there is the possibility that more that one user (think about a family who may be sharing a single email address) may have the same e-mail address. By deleting a record based on the email address, all records would be removed. Using an auto increment field to delete records ensures that only unique data is removed from the table. The following code demonstrates how the page works:
<?php
if (isset($_POST['submitb']))
{
//Retrieve unique auto increment value
$UserNum = $_POST['AutoNum'];
//Establish a connection to the Database
//Reference the mysqli_connection.php file created above
include('mysqli_connection.php');
//Issue SQL DELETE Statement
$sqlDelete = "DELETE FROM Personnel WHERE AutoNum = $UserNum";
$rsDelete = mysqli_query($conn,$sqlDelete);
if (mysqli_affected_rows($conn) == 1)
{
echo "Record successfully deleted!";
}
mysqli_close($conn);
}
?>
After the "Delete Record" button is clicked, a connection is established with the Database. Next, an SQL DELETE statement is issued to delete the record from Personnel table with an AutoNum field value equal to the value of the AutoNum hidden textbox. The SQL statement is then executed. The results of the mysqli_query() function are assigned to the $rsDelete variable. The last step is to verify that the record deletion was a success and display a confirmation message. The mysqli_affected_rows() function is used to determine the number of rows in a result or the number of rows affected by the mysqli_query() statement. Since a single record is being deleted, if the result of mysql_affected_rows() is equal to 1, the record was deleted successfully. Finally, the connection to the database connection is closed.