Topic 29 of 48 · Full Stack Essentials

PHP with Update & Delete

Lesson TL;DRTopic 4: PHP with Update & Delete 📖 13 min read · 🎯 intermediate · 🧭 Prerequisites: formbuttons, javascriptbom Why this matters You've already learned how to add data to a database. But real apps d...
13 min read·intermediate·php · mysql · mysqli · update

Topic 4: PHP with Update & Delete

📖 13 min read · 🎯 intermediate · 🧭 Prerequisites: form-buttons, javascript-bom

Why this matters

You've already learned how to add data to a database. But real apps don't just collect data — they fix it. A student changes their phone number. A course gets cancelled. A typo slips in and needs correcting. If your PHP code can only insert and read, you're stuck — you can't touch what's already there. Today we fix that. We're going to use PHP with MySQL's UPDATE and DELETE commands, so your app can correct a mistake or remove a record entirely. That's what makes a database feel alive.

What You'll Learn

  • How to connect to a MySQL database using PHP's mysqli extension
  • How to write and execute a SQL UPDATE query from PHP to modify existing records
  • How to write and execute a SQL DELETE query from PHP to remove records
  • How to combine both operations in a single, complete PHP script
  • How to set up a practice database with sample data to test both operations

The Analogy

Think of your MySQL database as a city registry office that keeps resident records on index cards in a filing cabinet. When a resident moves and their address changes, a clerk pulls out the card, crosses out the old address, and writes the new one — that is an UPDATE. When a resident leaves the city permanently, the clerk pulls the card and shreds it — that is a DELETE. PHP is the courier who carries the clerk's instructions to the cabinet: it knows the right drawer, the right card, and exactly what the clerk wants done. Without PHP doing the legwork, those instructions never reach the cabinet at all.

Chapter 1: Connecting to the Database

Every operation against MySQL — whether a SELECT, an UPDATE, or a DELETE — begins with the same handshake: opening a mysqli connection and verifying it succeeded. This code is identical to what you used for SELECT/FETCH in the previous lesson, so treat it as boilerplate you always write first.

<?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(host, user, pass, db) opens the connection.
  • $conn->connect_error holds the error string if the connection failed; it is null on success.
  • die() halts the script immediately and prints the error — essential so no subsequent queries run against a dead connection.

Chapter 2: Updating Records with PHP

The SQL UPDATE statement modifies one or more rows that match a WHERE condition. In PHP you pass the query string to $conn->query() and check whether the return value is strictly TRUE.

Syntax reminder:

UPDATE table_name SET column1 = value1, column2 = value2 WHERE condition;

PHP example — update Alice's age to 24:

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

What each part does:

  • "UPDATE students SET age = 24 WHERE name = 'Alice'" — targets every row in the students table where name equals 'Alice' and sets age to 24.
  • $conn->query($sql) === TRUE — strict equality check. query() returns TRUE on a successful non-result-set statement (INSERT, UPDATE, DELETE) and FALSE on failure.
  • $conn->error — contains the MySQL error message string when the query fails.

Caution: Omitting the WHERE clause updates every row in the table. Always double-check your WHERE before executing an UPDATE.

Chapter 3: Deleting Records with PHP

The SQL DELETE statement removes rows that match a WHERE condition. The PHP pattern is structurally identical to UPDATE.

Syntax reminder:

DELETE FROM table_name WHERE condition;

PHP example — delete the student named Bob:

<?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 WHERE name = 'Bob'" — removes every row in students where name equals 'Bob'.
  • The success/failure check uses the same === TRUE pattern as UPDATE.

Caution: DELETE FROM students with no WHERE clause deletes every row in the table. The table structure survives, but all data is gone. There is no undo in plain SQL.

Chapter 4: Putting It All Together

Here is a complete, self-contained PHP script that connects to the database, runs an UPDATE, runs a DELETE, and then closes the connection 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();
?>

Flow summary:

flowchart TD
    A[PHP Script Starts] --> B[new mysqli - open connection]
    B --> C{connect_error?}
    C -- yes --> D[die - halt with error]
    C -- no --> E[Run UPDATE query]
    E --> F{query === TRUE?}
    F -- yes --> G[echo success message]
    F -- no --> H[echo conn->error]
    G --> I[Run DELETE query]
    H --> I
    I --> J{query === TRUE?}
    J -- yes --> K[echo success message]
    J -- no --> L[echo conn->error]
    K --> M[conn->close]
    L --> M
    M --> N[Script ends]

Note $conn->close() at the end — it explicitly releases the database connection. PHP will close it automatically at script end, but calling it explicitly is good practice in longer scripts.

🧪 Try It Yourself

Task: Create the school database, populate it with sample students, then run update_delete_students.php to mutate the data.

Step 1 — Create the database and table (run in phpMyAdmin's SQL tab or via MySQL CLI):

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 this file as update_delete_students.php in your XAMPP/WAMP htdocs folder and visit http://localhost/update_delete_students.php in your browser:

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

Success criteria: Your browser should display:

Record updated successfully
Record deleted successfully

Then verify in phpMyAdmin: Alice's age should now be 24, and Bob's row should be gone entirely. Carol's row should be untouched.

🔍 Checkpoint Quiz

Q1. What does $conn->query($sql) === TRUE check, and why use === instead of ==?

A) It checks whether the query string is non-empty; === prevents type coercion. B) It checks whether the query executed successfully; === distinguishes TRUE from a truthy result-set object returned by SELECT queries. C) It checks whether the database connection is open; === is stricter about boolean types. D) It checks whether any rows were affected; === ensures the count is exactly 1.

Q2. Given this snippet, what happens if name = 'Alice' matches zero rows in the table?

$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;
}

A) PHP throws a fatal error because no rows were found. B) $conn->query() returns FALSE and the error branch executes. C) $conn->query() returns TRUE and prints "Record updated successfully" — zero affected rows is still a successful query. D) The script halts silently without printing anything.

Q3. What is the danger of running DELETE FROM students; without a WHERE clause?

A) MySQL will refuse to execute it and return a syntax error. B) Only the first row in the table will be deleted. C) Every row in the students table will be permanently deleted, leaving an empty table. D) The entire students table and its schema will be dropped from the database.

Q4. You have a products table with columns id, name, and price. Write the PHP line that sets price = 9.99 for the product with id = 7. Use $conn as the existing connection object.

A1. B — query() on a non-SELECT statement returns boolean TRUE on success or FALSE on failure. For SELECT it returns a mysqli_result object, which is truthy but not === TRUE. Using strict equality ensures you don't accidentally treat a result-set object as a successful write operation.

A2. C — MySQL considers "update zero rows" a successful execution; no error occurred, the query was valid and ran. query() returns TRUE. You can check $conn->affected_rows if you need to know whether any data actually changed.

A3. C — Without a WHERE clause DELETE FROM students removes every row. The table structure (columns, types, indexes) remains, but all data is gone. This is not the same as DROP TABLE, which removes the structure too.

A4.

$sql = "UPDATE products SET price = 9.99 WHERE id = 7";
$conn->query($sql);

(Add the === TRUE success/error check for production-quality code.)

🪞 Recap

  • Open every database operation with new mysqli(host, user, pass, db) and check $conn->connect_error before proceeding.
  • UPDATE table SET col = val WHERE condition modifies matching rows; always supply a WHERE clause unless you intend to update every row.
  • DELETE FROM table WHERE condition removes matching rows permanently; omitting WHERE wipes the entire table.
  • Both UPDATE and DELETE return TRUE from $conn->query() on success and FALSE on failure — inspect $conn->error when things go wrong.
  • Call $conn->close() when you are finished to release the database connection explicitly.

📚 Further Reading

Like this topic? It’s one of 48 in Full Stack Essentials.

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

Course detailsBlock your seat →