Bird
Raised Fist0
PHPprogramming~5 mins

Insert, update, delete operations in PHP

Choose your learning style10 modes available
LearnWhyDeepVisualTryChallengeProjectRecallTime
Introduction

These operations let you add, change, or remove data in a database. They help keep your data up to date and useful.

When you want to add a new user to a website.
When you need to change a user's email address.
When you want to remove a product that is no longer available.
When updating stock quantities after a sale.
When cleaning up old or incorrect records.
Syntax
PHP
<?php
// Insert
$sql = "INSERT INTO table_name (column1, column2) VALUES ('value1', 'value2');";

// Update
$sql = "UPDATE table_name SET column1 = 'value1' WHERE condition;";

// Delete
$sql = "DELETE FROM table_name WHERE condition;";
?>

These are SQL commands used inside PHP to work with databases.

Always use a WHERE clause in update and delete to avoid changing all rows.

Examples
Adds a new user named Alice with her email.
PHP
<?php
// Insert a new user
$sql = "INSERT INTO users (name, email) VALUES ('Alice', 'alice@example.com')";
Changes Alice's email to a new one.
PHP
<?php
// Update user's email
$sql = "UPDATE users SET email = 'newemail@example.com' WHERE name = 'Alice'";
Removes the user named Alice from the database.
PHP
<?php
// Delete a user
$sql = "DELETE FROM users WHERE name = 'Alice'";
Sample Program

This program connects to a MySQL database, inserts a user named Bob, updates his email, then deletes him. It prints messages to show each step worked.

PHP
<?php
// Connect to database
$mysqli = new mysqli('localhost', 'user', 'password', 'testdb');
if ($mysqli->connect_error) {
    die('Connect Error (' . $mysqli->connect_errno . ') ' . $mysqli->connect_error);
}

// Insert a new record
$insert_sql = "INSERT INTO users (name, email) VALUES ('Bob', 'bob@example.com')";
if ($mysqli->query($insert_sql) === TRUE) {
    echo "Insert successful\n";
} else {
    echo "Insert error: " . $mysqli->error . "\n";
}

// Update the record
$update_sql = "UPDATE users SET email = 'bob.new@example.com' WHERE name = 'Bob'";
if ($mysqli->query($update_sql) === TRUE) {
    echo "Update successful\n";
} else {
    echo "Update error: " . $mysqli->error . "\n";
}

// Delete the record
$delete_sql = "DELETE FROM users WHERE name = 'Bob'";
if ($mysqli->query($delete_sql) === TRUE) {
    echo "Delete successful\n";
} else {
    echo "Delete error: " . $mysqli->error . "\n";
}

$mysqli->close();
OutputSuccess
Important Notes

Always check for errors after running SQL commands.

Use prepared statements to protect against SQL injection in real projects.

Remember to close the database connection when done.

Summary

Insert adds new data to a table.

Update changes existing data based on a condition.

Delete removes data based on a condition.

Practice

(1/5)
1. Which SQL statement is used in PHP to add new data into a database table?
easy
A. UPDATE
B. INSERT INTO
C. DELETE FROM
D. SELECT

Solution

  1. Step 1: Understand the purpose of each SQL command

    INSERT INTO adds new rows, UPDATE changes existing rows, DELETE FROM removes rows, and SELECT retrieves data.

  2. Step 2: Match the command to adding new data

    Since adding new data means inserting new rows, the correct command is INSERT INTO.

  3. Final Answer:

    INSERT INTO -> Option B

  4. Quick Check:

    Insert = Add new data [OK]
Hint: Insert adds new rows; update and delete modify existing ones [OK]
Common Mistakes:
  • Confusing UPDATE with INSERT
  • Thinking DELETE adds data
  • Using SELECT to add data
2. Which of the following is the correct PHP syntax to update a user's email in a MySQL database using mysqli_query?
easy
A. $sql = "DELETE FROM users SET email = 'new@example.com' WHERE id = 1";
B. $sql = "INSERT INTO users email = 'new@example.com' WHERE id = 1";
C. $sql = "SELECT email = 'new@example.com' FROM users WHERE id = 1";
D. $sql = "UPDATE users SET email = 'new@example.com' WHERE id = 1";

Solution

  1. Step 1: Identify the correct SQL syntax for updating

    The correct syntax for updating is UPDATE table SET column = value WHERE condition.

  2. Step 2: Check each option's syntax

    $sql = "UPDATE users SET email = 'new@example.com' WHERE id = 1"; matches the correct syntax. Options A, B, and D misuse SQL commands or syntax.

  3. Final Answer:

    $sql = "UPDATE users SET email = 'new@example.com' WHERE id = 1"; -> Option D

  4. Quick Check:

    Update syntax = UPDATE ... SET ... WHERE ... [OK]
Hint: Update uses SET with WHERE to change specific rows [OK]
Common Mistakes:
  • Using INSERT syntax for update
  • Omitting WHERE clause in update
  • Using DELETE or SELECT instead of UPDATE
3. What will be the output of the following PHP code snippet?
$conn = new mysqli('localhost', 'user', 'pass', 'db');
$sql = "DELETE FROM products WHERE id = 10";
if ($conn->query($sql) === TRUE) {
    echo "Deleted successfully";
} else {
    echo "Error deleting record";
}
medium
A. No output
B. Error deleting record
C. Deleted successfully
D. Syntax error

Solution

  1. Step 1: Understand the code logic

    The code tries to delete a product with id 10. If the query runs without error, it prints "Deleted successfully".

  2. Step 2: Assume the query runs correctly

    Since the SQL syntax is correct and connection is assumed valid, the query returns TRUE, so the success message prints.

  3. Final Answer:

    Deleted successfully -> Option C

  4. Quick Check:

    Successful DELETE query prints success message [OK]
Hint: If query returns TRUE, success message prints [OK]
Common Mistakes:
  • Assuming syntax error without checking code
  • Confusing TRUE with FALSE return values
  • Ignoring the if-else structure
4. Identify the error in this PHP code that tries to update a user's name:
$sql = "UPDATE users SET name = 'John'";
$conn->query($sql);
medium
A. Missing WHERE clause to specify which user to update
B. Incorrect SQL keyword, should be INSERT
C. Quotes around 'John' should be double quotes
D. Missing semicolon after query execution

Solution

  1. Step 1: Review the SQL update statement

    The statement updates the name but does not specify which row to update because it lacks a WHERE clause.

  2. Step 2: Understand the impact of missing WHERE

    Without WHERE, all rows in the table will be updated, which is usually unintended and considered an error.

  3. Final Answer:

    Missing WHERE clause to specify which user to update -> Option A

  4. Quick Check:

    Update without WHERE affects all rows [OK]
Hint: Always include WHERE in update to target specific rows [OK]
Common Mistakes:
  • Thinking quotes type causes error
  • Confusing UPDATE with INSERT
  • Ignoring missing WHERE consequences
5. You want to delete all users from the users table who have not logged in for over a year. Which PHP code snippet correctly performs this operation assuming the last login date is stored in last_login column?
hard
A. $sql = "DELETE FROM users WHERE last_login < DATE_SUB(NOW(), INTERVAL 1 YEAR)"; $conn->query($sql);
B. $sql = "UPDATE users SET last_login = NULL WHERE last_login > DATE_SUB(NOW(), INTERVAL 1 YEAR)"; $conn->query($sql);
C. $sql = "DELETE FROM users WHERE last_login > DATE_ADD(NOW(), INTERVAL 1 YEAR)"; $conn->query($sql);
D. $sql = "INSERT INTO users (last_login) VALUES (DATE_SUB(NOW(), INTERVAL 1 YEAR))"; $conn->query($sql);

Solution

  1. Step 1: Understand the condition for deletion

    We want to delete users whose last_login is older than one year, so the condition is last_login < DATE_SUB(NOW(), INTERVAL 1 YEAR).

  2. Step 2: Check each option's SQL logic

    $sql = "DELETE FROM users WHERE last_login < DATE_SUB(NOW(), INTERVAL 1 YEAR)"; $conn->query($sql); correctly deletes users with last login older than one year. $sql = "UPDATE users SET last_login = NULL WHERE last_login > DATE_SUB(NOW(), INTERVAL 1 YEAR)"; $conn->query($sql); updates instead of deletes. $sql = "DELETE FROM users WHERE last_login > DATE_ADD(NOW(), INTERVAL 1 YEAR)"; $conn->query($sql); deletes users with last login in the future, which is wrong. $sql = "INSERT INTO users (last_login) VALUES (DATE_SUB(NOW(), INTERVAL 1 YEAR))"; $conn->query($sql); inserts new rows, which is incorrect.

  3. Final Answer:

    $sql = "DELETE FROM users WHERE last_login < DATE_SUB(NOW(), INTERVAL 1 YEAR)"; $conn->query($sql); -> Option A

  4. Quick Check:

    Delete with condition older than 1 year = $sql = "DELETE FROM users WHERE last_login < DATE_SUB(NOW(), INTERVAL 1 YEAR)"; $conn->query($sql); [OK]
Hint: Use DELETE with WHERE and DATE_SUB for time conditions [OK]
Common Mistakes:
  • Using UPDATE instead of DELETE
  • Wrong date comparison operators
  • Using INSERT instead of DELETE
Recommended

Ready to prove it?

Pass the test and this pattern moves unknown strong while earning +1 Grip toward your certificate.

10 questions~5 mineasy hard