In Codeville, the Grand Council of Developers faced the need to manage their data more effectively. They realized that apart from inserting and retrieving data, they also needed to update and delete records in their database. Let’s join the council as they learn how to implement these operations using PHP and MySQL.
Before performing update and delete operations, the council ensured their environment was ready with a functioning LAMP stack (Linux, Apache, MySQL, PHP).
Database and Table Setup
The council used a Tasks table similar to the one from the previous chapter:
SQL
Copy code
CREATE DATABASE TaskManagement;
USE TaskManagement;
CREATE TABLE Tasks (
TaskID INT AUTO_INCREMENT PRIMARY KEY,
Title VARCHAR(255) NOT NULL,
Description TEXT NOT NULL,
AssignedTo VARCHAR(100) NOT NULL,
Priority VARCHAR(50) NOT NULL,
DueDate DATE NOT NULL,
SubmissionDate TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
Mayor Binary outlined the need to update task details, such as changing the priority or the assigned person.
HTML Form for Update
The council created a form to update task details. This form fetches the existing details of the task and allows the user to modify them.
php
Copy code
<?php
// Database configuration
$servername = “localhost”;
$username = “root”;
$password = “”;
$dbname = “TaskManagement”;
// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);
// Check connection
if ($conn->connect_error) {
die(“Connection failed: ” . $conn->connect_error);
}
$taskId = $_GET[‘id’];
$taskQuery = “SELECT * FROM Tasks WHERE TaskID = $taskId”;
$taskResult = $conn->query($taskQuery);
$task = $taskResult->fetch_assoc();
?>
<!DOCTYPE html>
<html lang=”en”>
<head>
<meta charset=”UTF-8″>
<meta name=”viewport” content=”width=device-width, initial-scale=1.0″>
<title>Update Task</title>
</head>
<body>
<h1>Update Task</h1>
<form action=”update_task.php” method=”post”>
<input type=”hidden” name=”taskId” value=”<?php echo $task[‘TaskID’]; ?>”>
<label for=”title”>Task Title:</label>
<input type=”text” id=”title” name=”title” value=”<?php echo $task[‘Title’]; ?>” required>
<br>
<label for=”description”>Description:</label>
<textarea id=”description” name=”description” required><?php echo $task[‘Description’]; ?></textarea>
<br>
<label for=”assigned_to”>Assigned To:</label>
<input type=”text” id=”assigned_to” name=”assigned_to” value=”<?php echo $task[‘AssignedTo’]; ?>” required>
<br>
<label for=”priority”>Priority:</label>
<select id=”priority” name=”priority” required>
<option value=”low” <?php if ($task[‘Priority’] == ‘low’) echo ‘selected’; ?>>Low</option>
<option value=”medium” <?php if ($task[‘Priority’] == ‘medium’) echo ‘selected’; ?>>Medium</option>
<option value=”high” <?php if ($task[‘Priority’] == ‘high’) echo ‘selected’; ?>>High</option>
</select>
<br>
<label for=”due_date”>Due Date:</label>
<input type=”date” id=”due_date” name=”due_date” value=”<?php echo $task[‘DueDate’]; ?>” required>
<br>
<button type=”submit”>Update Task</button>
</form>
</body>
</html>
<?php
$conn->close();
?>
PHP Script for Update
Here’s the update_task.php script that processes the update form and updates the task details in the database:
php
Copy code
<?php
// Database configuration
$servername = “localhost”;
$username = “root”;
$password = “”;
$dbname = “TaskManagement”;
// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);
// Check connection
if ($conn->connect_error) {
die(“Connection failed: ” . $conn->connect_error);
}
// Get form data
$taskId = $_POST[‘taskId’];
$title = $_POST[‘title’];
$description = $_POST[‘description’];
$assigned_to = $_POST[‘assigned_to’];
$priority = $_POST[‘priority’];
$due_date = $_POST[‘due_date’];
// Prepare and bind
$stmt = $conn->prepare(“UPDATE Tasks SET Title = ?, Description = ?, AssignedTo = ?, Priority = ?, DueDate = ? WHERE TaskID = ?”);
$stmt->bind_param(“sssssi”, $title, $description, $assigned_to, $priority, $due_date, $taskId);
// Execute the statement
if ($stmt->execute()) {
echo “Task updated successfully!”;
} else {
echo “Error: ” . $stmt->error;
}
// Close connection
$stmt->close();
$conn->close();
?>
Mayor Binary emphasized the importance of being able to delete tasks that were no longer needed.
HTML Form for Delete
The council added a delete button to the task listing page that, when clicked, confirms the deletion.
php
Copy code
<?php
// Fetch tasks (same as before)
$tasksQuery = “SELECT * FROM Tasks”;
$tasksResult = $conn->query($tasksQuery);
?>
<!DOCTYPE html>
<html lang=”en”>
<head>
<meta charset=”UTF-8″>
<meta name=”viewport” content=”width=device-width, initial-scale=1.0″>
<title>Task List</title>
<style>
table {
width: 100%;
border-collapse: collapse;
}
th, td {
padding: 12px;
border: 1px solid #ddd;
text-align: left;
}
th {
background-color: #f2f2f2;
}
</style>
</head>
<body>
<h1>Task List</h1>
<table>
<thead>
<tr>
<th>Task ID</th>
<th>Title</th>
<th>Description</th>
<th>Assigned To</th>
<th>Priority</th>
<th>Due Date</th>
<th>Submission Date</th>
<th>Actions</th>
</tr>
</thead>
<tbody>
<?php
if ($tasksResult->num_rows > 0) {
while($row = $tasksResult->fetch_assoc()) {
echo “<tr>
<td>” . $row[‘TaskID’] . “</td>
<td>” . $row[‘Title’] . “</td>
<td>” . $row[‘Description’] . “</td>
<td>” . $row[‘AssignedTo’] . “</td>
<td>” . $row[‘Priority’] . “</td>
<td>” . $row[‘DueDate’] . “</td>
<td>” . $row[‘SubmissionDate’] . “</td>
<td>
<a href=’update_form.php?id=” . $row[‘TaskID’] . “‘>Update</a>
<a href=’delete_task.php?id=” . $row[‘TaskID’] . “‘ onclick=\”return confirm(‘Are you sure you want to delete this task?’);\”>Delete</a>
</td>
</tr>”;
}
} else {
echo “<tr><td colspan=’8′>No tasks found</td></tr>”;
}
?>
</tbody>
</table>
</body>
</html>
<?php
$conn->close();
?>
PHP Script for Delete
Here’s the delete_task.php script that deletes a task from the database:
php
Copy code
<?php
// Database configuration
$servername = “localhost”;
$username = “root”;
$password = “”;
$dbname = “TaskManagement”;
// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);
// Check connection
if ($conn->connect_error) {
die(“Connection failed: ” . $conn->connect_error);
}
// Get task ID
$taskId = $_GET[‘id’];
// Prepare and bind
$stmt = $conn->prepare(“DELETE FROM Tasks WHERE TaskID = ?”);
$stmt->bind_param(“i”, $taskId);
// Execute the statement
if ($stmt->execute()) {
echo “Task deleted successfully!”;
} else {
echo “Error: ” . $stmt->error;
}
// Close connection
$stmt->close();
$conn->close();
?>
By mastering update and delete operations, the Grand Council of Developers in Codeville ensured they could manage their data effectively. These operations are essential for maintaining accurate and up-to-date information in any database-driven application. Feel free to experiment with the provided examples and modify them to suit different scenarios or additional requirements. If you have any questions or need further assistance, I’m here to help. Happy coding!