PHP MySQL Update Data

Modifying existing records in MySQL database

🔄 What is MySQL Update?

MySQL UPDATE statement modifies existing records in a database table. You can change one or multiple columns based on specific conditions using PHP. 


<?php
// Simple update example
$sql = "UPDATE users SET email='[email protected]' WHERE id=1";
mysqli_query($conn, $sql);
?>

Update Data Methods

📝

Single Column

Update one field at a time 

UPDATE users SET name='John'
📋

Multiple Columns

Update several fields together 

UPDATE users SET name='John', age=25
🎯

With Condition

Update specific records only 

UPDATE users SET status='active' WHERE id=5
⚡

Prepared Statement

Secure update with parameters 

$stmt->bind_param("si", $name, $id)

🔹 Basic UPDATE Statement

The UPDATE statement modifies existing records in a table. Always use WHERE clause to specify which records to update, otherwise all records will be changed. 

<?php
// Database connection
$servername = "localhost";
$username = "root";
$password = "";
$dbname = "myDB";

$conn = mysqli_connect($servername, $username, $password, $dbname);

// Update single record
$sql = "UPDATE users SET email='[email protected]' WHERE id=1";

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

mysqli_close($conn);
?>

Output:

Record updated successfully

🔹 Update Multiple Columns

You can update multiple columns in a single UPDATE statement by separating them with commas: 

<?php
$conn = mysqli_connect("localhost", "root", "", "myDB");

// Update multiple columns
$sql = "UPDATE users 
        SET name='John Doe', 
            email='[email protected]', 
            age=30 
        WHERE id=1";

if (mysqli_query($conn, $sql)) {
    echo "Multiple columns updated!";
} else {
    echo "Error: " . mysqli_error($conn);
}

mysqli_close($conn);
?>

Output:

Multiple columns updated!

🔹 Prepared Statement Update

Using prepared statements is the secure way to update data, protecting against SQL injection attacks: 

<?php
$conn = mysqli_connect("localhost", "root", "", "myDB");

// Prepare statement
$stmt = $conn->prepare("UPDATE users SET name=?, email=? WHERE id=?");

// Bind parameters (s=string, i=integer)
$name = "Jane Smith";
$email = "[email protected]";
$id = 2;

$stmt->bind_param("ssi", $name, $email, $id);

// Execute
if ($stmt->execute()) {
    echo "Record updated securely!";
} else {
    echo "Error: " . $stmt->error;
}

$stmt->close();
mysqli_close($conn);
?>

Output:

Record updated securely!

🔹 Update with Form Data

Common scenario: updating records from HTML form submissions: 

<?php
if ($_SERVER["REQUEST_METHOD"] == "POST") {
    $conn = mysqli_connect("localhost", "root", "", "myDB");
    
    $id = $_POST['user_id'];
    $name = $_POST['name'];
    $email = $_POST['email'];
    
    // Prepare and execute
    $stmt = $conn->prepare("UPDATE users SET name=?, email=? WHERE id=?");
    $stmt->bind_param("ssi", $name, $email, $id);
    
    if ($stmt->execute()) {
        echo "User profile updated!";
    }
    
    $stmt->close();
    mysqli_close($conn);
}
?>

<!-- HTML Form -->
<form method="post">
    <input type="hidden" name="user_id" value="1">
    <input type="text" name="name" placeholder="Name">
    <input type="email" name="email" placeholder="Email">
    <button type="submit">Update</button>
</form>

🔹 Important Tips

⚠️ Best Practices:

  • Always use WHERE: Without WHERE, all records will be updated!
  • Use prepared statements: Prevents SQL injection attacks
  • Check affected rows: Use mysqli_affected_rows() to verify updates
  • Validate input: Always validate and sanitize user input
  • Handle errors: Always check if the query executed successfully

🧠 Test Your Knowledge

What happens if you forget the WHERE clause in UPDATE?