Topic 37 of 56 · Full Stack Advanced

Topic 4 : PHP with Update & Delete

Lesson TL;DRTopic 4: PHP with Update & Delete 📖 5 min read · 🎯 beginner · 🧭 Prerequisites: workingwithpackagelockjsontolockthenodemodulesversions, lifecyclemethods Why this matters Up until now, you've been ad...
5 min read·beginner·php · mysql · sql · update

Topic 4: PHP with Update & Delete

📖 5 min read · 🎯 beginner · 🧭 Prerequisites: working-with-package-lock-json-to-lock-the-node-modules-versions, lifecycle-methods

Why this matters

Up until now, you've been adding data to your database — which feels great. But here's the thing — real apps don't just collect data, they change it. A user updates their profile. An order gets cancelled. A product goes out of stock. If you can only INSERT records, your app is already broken for half the things users need to do. Today we tackle UPDATE and DELETE in PHP — the two SQL operations that keep your data accurate and your database clean. Master these, and your app can finally handle the real world.

What You'll Learn

  • How to write and execute a SQL UPDATE query from PHP using mysqli
  • How to write and execute a SQL DELETE query from PHP using mysqli
  • How to combine both operations in a single connected PHP script
  • How to set up a practice database, table, and sample data to test against

The Analogy

Think of your MySQL database as a living museum archive. When a curator discovers that an artifact's catalog entry has the wrong date, they pull the card, erase the old value, and write the correct one — that's an UPDATE. When a replica is decommissioned and has no place in the collection anymore, the catalog card is pulled entirely and shredded — that's a DELETE. The archive never alters itself on its own; a curator (your PHP script) must walk in, locate the right card by some identifying detail (the WHERE clause), and make the change deliberately. Get the WHERE wrong and you update every artifact in the building — so precision is everything.

Chapter 1: Connecting to the Database

Every PHP database operation starts the same way: open a connection with mysqli. The connection object ($conn) is the curator's badge — without it, nothing in the archive can be touched.

<?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";
?>

Key points:

  • new mysqli(...) opens the connection and returns an object.
  • $conn->connect_error is null on success and a string on failure.
  • die() stops execution immediately and prints the error — useful during development, but replace with graceful error handling in production.

Chapter 2: Updating Records with PHP

Updating a record means telling MySQL which row to change (via WHERE) and what to change it to (via SET).

Step 1 — Write the UPDATE query

Suppose Alice's age needs to be corrected from 23 to 24 in a students table:

<?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;
}
?>
  • UPDATE students — names the target table.
  • SET age = 24 — specifies the column and its new value.
  • WHERE name = 'Alice' — restricts the change to matching rows only. Omitting WHERE updates every row in the table.
  • $conn->query($sql) === TRUE — the strict equality check (===) distinguishes a successful write (boolean TRUE) from a successful read that returns a result object.

Chapter 3: Deleting Records with PHP

Deleting a record removes it permanently. The DELETE statement targets rows by a condition, just like UPDATE.

Step 1 — Write the DELETE query

To remove a student named Bob from the students table:

<?php
$sql = "DELETE FROM students WHERE name = 'Bob'";

if ($conn->query($sql) === TRUE) {
    echo "Record deleted successfully";
} else {
    echo "Error deleting record: " . $conn->error;
}
?>
  • DELETE FROM students — names the target table.
  • WHERE name = 'Bob' — restricts deletion to matching rows. Omitting WHERE deletes every row in the table — a catastrophic and unrecoverable operation in most setups.
  • Always double-check your WHERE clause in a SELECT first before running a DELETE in production.

Chapter 4: Putting It All Together

Here is a complete, self-contained PHP script that opens one connection, performs an UPDATE, performs a DELETE, and then closes cleanly:

<?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();
?>

One connection, two operations, one close(). Always close the connection when you're finished — leaving it open wastes server resources.

🧪 Try It Yourself

Task: Create the school database and students table, seed it with sample data, then run a PHP script to update Alice's age and delete Bob's record.

Step 1 — Create the database and table (run in MySQL):

CREATE DATABASE school;
USE school;
CREATE TABLE students (
    id   INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(50) NOT NULL,
    age  INT NOT NULL
);

Step 2 — Insert sample data:

INSERT INTO students (name, age) VALUES ('Alice', 23), ('Bob', 25), ('Carol', 22);

Step 3 — Save the following as update_delete_students.php and run it on your local server (e.g., place it in htdocs for XAMPP or www for WAMP):

<?php
$servername = "localhost";
$username   = "root";
$password   = "";
$dbname     = "school";

$conn = new mysqli($servername, $username, $password, $dbname);

if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
}

// Update Alice's age
$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 Bob's 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();
?>

Success criteria: Your browser (or terminal) should display:

Record updated successfully
Record deleted successfully

Then verify in MySQL with SELECT * FROM students; — Alice should show age = 24 and Bob should be gone, while Carol remains untouched.

🔍 Checkpoint Quiz

Q1. Why is the WHERE clause critical in both UPDATE and DELETE statements?

A) It selects the database to connect to
B) It restricts which rows are affected, preventing accidental mass changes
C) It is required by PHP's mysqli library or the query will throw an exception
D) It improves query execution speed by using an index

Q2. Given the following snippet, what does === TRUE accomplish that == TRUE would not?

if ($conn->query($sql) === TRUE) {

A) It checks that the query string is non-empty
B) It distinguishes a successful write (boolean TRUE) from a result set object, which is also truthy
C) It verifies that the affected row count is greater than zero
D) It has no practical difference from == TRUE in this context

Q3. What is the output of the following script if no student named 'Zara' exists in the table?

<?php
$sql = "DELETE FROM students WHERE name = 'Zara'";
if ($conn->query($sql) === TRUE) {
    echo "Record deleted successfully";
} else {
    echo "Error deleting record: " . $conn->error;
}
?>

A) Error deleting record: followed by an error message
B) Record deleted successfully — DELETE with no matching rows still executes without error
C) The script halts with a fatal PHP error
D) Nothing is printed

Q4. You need to change the age of every student in the students table to 18. How would you write the UPDATE query?

A1. B — The WHERE clause restricts which rows are affected. Without it, UPDATE changes every row in the table and DELETE removes every row — both are irreversible disasters in most scenarios.

A2. B — $conn->query() returns boolean TRUE for successful writes (INSERT/UPDATE/DELETE) but returns a mysqli_result object for successful reads (SELECT). A mysqli_result object is truthy, so == TRUE would incorrectly match it. The strict === TRUE ensures you're checking for an actual boolean.

A3. B — MySQL executes the DELETE successfully even when zero rows match the WHERE condition. The query did not fail; it simply affected 0 rows. PHP receives TRUE and echoes "Record deleted successfully".

A4.

UPDATE students SET age = 18;

Omitting the WHERE clause applies the SET to every row in the table. Use this intentionally and cautiously — there is no built-in undo.

🪞 Recap

  • UPDATE table SET column = value WHERE condition modifies specific rows; always include WHERE or every row changes.
  • DELETE FROM table WHERE condition removes specific rows; always include WHERE or every row is deleted.
  • Both operations use $conn->query($sql) === TRUE (strict equality) to detect success in mysqli.
  • A single PHP script can chain multiple UPDATE and DELETE calls over one open connection before calling $conn->close().
  • Always verify your WHERE clause with a SELECT first when working against production data.

📚 Further Reading

Like this topic? It’s one of 56 in Full Stack Advanced.

Block your seat for ₹2,500 and join the next cohort.

Course detailsBlock your seat →