• +91-9142290450

  • Hajipur, Bihar, 844101

☰
❮
❯

MySQL Update Data

← Previous Next →

Updating data in a database is an essential operation in any dynamic application. Whether you need to correct a user’s email, change a student’s age, or update product prices, the UPDATE statement in MySQL allows you to modify existing records.

In PHP applications, using MySQLi or PDO with the UPDATE statement ensures that updates are safe, efficient, and maintainable. This tutorial explains the syntax, implementation, and best practices for updating data.

What is UPDATE Statement?

The UPDATE statement modifies existing records in a table. Without proper filtering, it can unintentionally update all rows, so always use the WHERE clause to target specific records.

Basic Syntax

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

Example Table: students

id name age email
1 Neha 16 neha@example.com
2 Anika 17 anika@example.com
3 Riya 16 riya@example.com 
UPDATE students SET email = 'neha.new@example.com' WHERE name = 'Neha';

This updates Neha’s email without affecting other students.

Updating Multiple Columns

You can update multiple columns in a single statement:

UPDATE students
SET age = 17, email = 'riya.new@example.com'
WHERE name = 'Riya';

Updating All Rows

If no WHERE clause is used, all records are updated:

UPDATE students SET age = age + 1;

This increments the age of all students by 1. Be cautious: updating all rows unintentionally can cause data loss.

Using UPDATE in PHP MySQLi

Procedural Style

<?php
$conn = mysqli_connect("localhost", "root", "", "school_db");
if (!$conn) die("Connection failed: " . mysqli_connect_error());

$sql = "UPDATE students SET email = 'anika.new@example.com' WHERE name = 'Anika'";

if (mysqli_query($conn, $sql)) {
    echo "Record updated successfully!";
} else {
    echo "Error updating record: " . mysqli_error($conn);
}

mysqli_close($conn);
?>

Object-Oriented Style

<?php
$conn = new mysqli("localhost", "root", "", "school_db");
if ($conn->connect_error) die("Connection failed: " . $conn->connect_error);

$sql = "UPDATE students SET age = 18 WHERE name = 'Sanya'";
if ($conn->query($sql) === TRUE) {
    echo "Record updated successfully!";
} else {
    echo "Error updating record: " . $conn->error;
}

$conn->close();
?>

Using PDO for Updates

PDO provides a secure and flexible way to update data, especially with dynamic user input.

<?php
try {
    $conn = new PDO("mysql:host=localhost;dbname=school_db", "root", "");
    $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

    $stmt = $conn->prepare("UPDATE students SET email = :email, age = :age WHERE name = :name");

    $name = "Neha";
    $email = "neha.updated@example.com";
    $age = 17;

    $stmt->bindParam(':name', $name);
    $stmt->bindParam(':email', $email);
    $stmt->bindParam(':age', $age);
    $stmt->execute();

    echo "Record updated successfully!";
} catch(PDOException $e) {
    echo "Error: " . $e->getMessage();
}

$conn = null;
?>

Explanation:

  • :name, :email, and :age are placeholders.

  • bindParam() ensures values are safely passed to the query, preventing SQL injection.

  • Using PDO allows for more robust error handling with try-catch blocks.

Updating Multiple Records Dynamically

You can update multiple rows based on different conditions:

<?php
$students = [
    ['name' => 'Riya', 'age' => 17],
    ['name' => 'Anika', 'age' => 18]
];

$stmt = $conn->prepare("UPDATE students SET age = :age WHERE name = :name");

foreach ($students as $student) {
    $stmt->bindParam(':name', $student['name']);
    $stmt->bindParam(':age', $student['age']);
    $stmt->execute();
}

This approach is useful for bulk updates in applications like school management or e-commerce systems.

Best Practices

  1. Always use WHERE clauses unless intentionally updating all rows.

  2. Use prepared statements for dynamic data to prevent SQL injection.

  3. Validate and sanitize input before updating the database.

  4. Check the number of affected rows to verify the update.

  5. Wrap multiple updates in transactions when updating related tables to maintain data integrity.

Common Errors

Error Cause Solution
No rows updated WHERE condition does not match Verify condition and table data
SQL syntax error Missing comma or incorrect syntax Double-check column names and commas
SQL injection risk Concatenating user input Use prepared statements
Accidental update Missing WHERE clause Always include WHERE to target specific rows

Real-World Examples

  1. User Profile Update: Updating a user’s email or password in a registration system.

  2. E-commerce: Updating product stock quantities or prices.

  3. School Management: Adjusting students’ grades or ages in bulk.

Example:

UPDATE products SET price = price * 1.05 WHERE category = 'Electronics';

This increases prices of all electronics by 5%.

Summary of the Tutorial

  • The UPDATE statement modifies existing data in a MySQL table.

  • Always use WHERE clauses to target specific rows.

  • Combine with PHP MySQLi or PDO to update records dynamically and securely.

  • Prepared statements are crucial for protecting your application from SQL injection.

  • Use transactions for multiple updates to maintain data consistency.

Updating data is essential for dynamic, real-time applications, ensuring that information is accurate, secure, and up-to-date.

Practice Questions

  1. Write a SQL query to update the email of a student named "Neha" to "neha.new@example.com".

  2. Write a PHP MySQLi procedural script to update the age of a student named "Anika" to 18.

  3. Write a PHP MySQLi object-oriented script to increase the age of all students by 1.

  4. Write a PDO PHP script to update both the name and email of a student using prepared statements.

  5. Write a SQL query to update multiple students’ ages using the IN clause.

  6. Explain why it’s important to include a WHERE clause when using the UPDATE statement.

  7. Write a PHP script to update a student’s email based on user input safely using prepared statements.

  8. Write a SQL query to set the age of students aged 16 to 17.

  9. Explain a real-world scenario where updating multiple records in one query is useful.

  10. Write a PHP script that checks the number of affected rows after an update and prints a confirmation message.

← Previous Next →

Try a Short Quiz.

coding learning websites codepractice

No quizzes available.

PHP

online coding class codepractice

PHP Basics

PHP Data

Control Flow

PHP Functions

PHP Arrays

PHP Forms

PHP Advanced

Cookies & Sessions

Filters & JSON

PHP Exceptions

PHP OOP

MySQL with PHP

PHP and XML

AJAX with PHP

codepractice logo
Login
Go Back Top