📖 PHP Update and Delete MySQL

A common practice for updating and deleting records in a database is to add update and delete links to scripts that can manage those tasks. If you are working with a list of items from the database, it is customary to provide links for each item at the end of each iteration to allow the user to select a delete or update action. The delete and update links will point to a delete or update script and pass the corresponding primary key to that script.

Example

<a href="delete.php?memberID=xxx">Delete</a>
<a href="update.php?memberID=xxx">Update</a>

You can also use a form button for this same operation. With a form, you would normally use the POST method and a hidden field to pass the memberID value.

Example

<form action="profile.php" method="post">
	<div class="form-group">
		<input type="hidden" name="memberID" value="$memberID" class="btn btn-primary">
		<input type="submit" name="update" value="Update Profile" class="btn btn-primary">
	</div>
</form>

Update Script

This method displays an update form populated with the existing data record from the database for the member being updated. The form can be edited and submitted for processing.

Create the profile.php Page

  • Get the id from the querystring ($_GET['memberID']).
  • Display the information in a form for the user to update.
  • Read the data from the database for the appropriate primary key.
  • Pre-fill in the form values from the existing database record.
  • The primary key (memberID) cannot be updated in the database. It is used to uniquely identify the user.
  • The primary key can be in a hidden field if the user does not need to see it, or in a field with readonly as an attribute to prevent the user from changing it.
  • When the user submits the form, perform the same validation as for the registration form and display any errors with sticky form fields.
  • If the data is valid, create an update query to update the database.
  • Display the results including any errors, appropriate update confirmation message by passing the message(s) back to the page for display.

Update Existing Data

UPDATE table_name
Required. Identifies the table that contains the data to be udpated.
SET columnName = value
Required. Identifies the table the data will be retrieved from.
WHERE condition
Optional. Specifies which row(s) are to be updated. To accurately identify a specific record to be updated, use the table primary key for the record being updated as the condition. If left off, all rows in the table will be updated. Any simple or complex condition may be used.
LIMIT int
Optional. Specifies the maximum number of records to be effected. Often set to 1 to avoid excessive accidental deletions.
; (semi-colon)
Required. Indicates the end of the SQL statement.

The UPDATE statement is used to edit existing records of a MySQL table.

Format

UPDATE table_name SET column1 = value1, column2 = value2, column3 = value3,... WHERE condition LIMIT int;

Example

$memberID = $_GET['memebrID']; // or $_POST['memberID'] as required

$query = "UPDATE `membership` SET `firstname` = 'Billy', `lastname` = 'Bob' WHERE `memberID` = $memberID;";
$result = mysqli_query($conn, $query) or die(mysqli_error($conn));
if (!$result) {
	die(mysqli_error($conn));
} else {
	$row_count = mysqli_affected_rows($conn);
	if ($row_count == 1) {
		echo "<p>Record updated</p>";
	} else {
		echo "<p>Update failed</p>";
	}
}

Delete Script

This method displays one more step for the user before they commit to the delete. For example, if the data shown on the list page does not include all fields and you want to the user to verify the full contents before deleting. It's also a good idea to remind users they are about to delete a record that cannot be undone.

Create the delete-verify.php Page

  • Display the information on a web page with a confirm delete button.
  • Get the id from the querystring ($_GET['memberID']).
  • Read the data from the database using the appropriate primary key.
  • Display and confirm delete with PHP.
  • Create and execute the delete query.
  • Once the delete has been confirmed, create and execute the delete query.
  • Display the results including any errors, appropriate delete confirmation message by passing the message(s) back to the page for display.

Deleting Existing Records

DELETE FROM table_name
Required. Identifies the table that contains the data to be deleted.
WHERE condition
Optional. Specifies which row(s) are to be updated. To accurately identify a specific record to be deleted, use the table primary key for the record being deleted as the condition. If left off, all rows in the table will be deleted. Any simple or complex condition may be used.
; (semi-colon)
Required. Indicates the end of the SQL statement.

The DELETE statement is used to remove an existing record(s) of a MySQL table.

Format

DELETE FROM table_name WHERE condition;

Example

$memberID = $_GET['memebrID'];

$query = "DELETE FROM `membership` WHERE `memberID` = $memberID LIMIT 1;";
$result = mysqli_query($conn,$query);
if (!$result) {
	die(mysqli_error($conn));
} else {
	$row_count = mysqli_affected_rows($conn);
	if ($row_count == 1) {
		echo "<p>Record deleted</p>";
	} else {
		echo "<p>Delete failed</p>";
	}
}

Remove a File from the Server

unlink(filename);