In this chapter, we’ll explore how to use PHP to update and delete records in a MySQL database. Imagine you’re curating a dynamic digital museum where artifacts (data) need to be updated or removed. With PHP and SQL, you have the tools to make these changes seamlessly.
Setting the Scene
Continuing with our digital museum analogy, sometimes exhibits need updating or removing. The UPDATE and DELETE SQL commands allow you to modify or remove records in your database, ensuring your data remains current and relevant.
Updating records in a database involves specifying which record to update and the new values for the fields.
Step 1 : Connect to the Database
First, establish a connection to your database. This step is identical to the one used for selecting data.
php
Copy code
<?php
$servername = “localhost”;
$username = “root”;
$password = “”;
$dbname = “your_database_name”;
// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);
// Check connection
if ($conn->connect_error) {
die(“Connection failed: ” . $conn->connect_error);
}
echo “Connected successfully”;
?>
Step 2: Write the Update Query
Next, write an SQL UPDATE query to modify a record. Suppose we want to update the age of a student named Alice.
php
Copy code
<?php
$sql = “UPDATE students SET age = 24 WHERE name = ‘Alice'”;
if ($conn->query($sql) === TRUE) {
echo “Record updated successfully”;
} else {
echo “Error updating record: ” . $conn->error;
}
?>
Deleting records involves specifying which record(s) to remove using the DELETE command.
Step 1: Write the Delete Query
To delete a student named Bob from the students table, you would use the following SQL DELETE query:
php
Copy code
<?php
$sql = “DELETE FROM students WHERE name = ‘Bob'”;
if ($conn->query($sql) === TRUE) {
echo “Record deleted successfully”;
} else {
echo “Error deleting record: ” . $conn->error;
}
?>
Here’s a complete PHP script that connects to the database, updates a record, and deletes another record.
php
Copy code
<?php
$servername = “localhost”;
$username = “root”;
$password = “”;
$dbname = “your_database_name”;
// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);
// Check connection
if ($conn->connect_error) {
die(“Connection failed: ” . $conn->connect_error);
}
// Update record
$update_sql = “UPDATE students SET age = 24 WHERE name = ‘Alice'”;
if ($conn->query($update_sql) === TRUE) {
echo “Record updated successfully<br>”;
} else {
echo “Error updating record: ” . $conn->error . “<br>”;
}
// Delete record
$delete_sql = “DELETE FROM students WHERE name = ‘Bob'”;
if ($conn->query($delete_sql) === TRUE) {
echo “Record deleted successfully<br>”;
} else {
echo “Error deleting record: ” . $conn->error . “<br>”;
}
$conn->close();
?>
1. Create a Database and Table:
SQL
Copy code
CREATE DATABASE school;
USE school;
CREATE TABLE students (
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(50) NOT NULL,
age INT NOT NULL
);
2. Insert Sample Data:
SQL
Copy code
INSERT INTO students (name, age) VALUES (‘Alice’, 23), (‘Bob’, 25), (‘Carol’, 22);
3. PHP Script to Update and Delete Data: Save the following PHP script in a file named update_delete_students.php and run it on your server.
php
Copy code
<?php
$servername = “localhost”;
$username = “root”;
$password = “”;
$dbname = “school”;
// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);
// Check connection
if ($conn->connect_error) {
die(“Connection failed: ” . $conn->connect_error);
}
// Update record
$update_sql = “UPDATE students SET age = 24 WHERE name = ‘Alice'”;
if ($conn->query($update_sql) === TRUE) {
echo “Record updated successfully<br>”;
} else {
echo “Error updating record: ” . $conn->error . “<br>”;
}
// Delete record
$delete_sql = “DELETE FROM students WHERE name = ‘Bob'”;
if ($conn->query($delete_sql) === TRUE) {
echo “Record deleted successfully<br>”;
} else {
echo “Error deleting record: ” . $conn->error . “<br>”;
}
$conn->close();
?>
In this chapter, you’ve learned how to use PHP to update and delete records in a MySQL database. These operations are crucial for maintaining accurate and up-to-date data in your applications. By combining PHP and SQL, you have powerful tools to manage your data dynamically and efficiently. Keep practicing, and soon you’ll be adept at handling any database operations required for your projects. Happy coding!