PHP MySQL – Update Data

Welcome to The Coding College! In this tutorial, we’ll explore how to update data in a MySQL database using PHP. The UPDATE statement is an essential SQL feature that allows you to modify existing records in your database dynamically and securely.

What is the UPDATE Statement?

The UPDATE statement in SQL is used to modify the data in one or more rows of a table. To ensure only specific rows are updated, you need to use the WHERE clause. If the WHERE clause is omitted, all rows in the table will be updated (use with caution!).

Syntax:

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

Why Update Data Dynamically Using PHP?

With PHP, you can create dynamic applications that allow users or systems to:

  • Update user profiles.
  • Modify product details in an e-commerce platform.
  • Edit database records based on form submissions or API calls.

Example: Basic Update Query in PHP

Scenario:

Suppose you have a table Users and want to update a user’s email address based on their ID.

Example Table: Users

idusernameemail
1JohnDoe[email protected]
2JaneSmith[email protected]
3MarkLee[email protected]

We want to change JaneSmith‘s email to [email protected].

Example 1: Using MySQLi (Object-Oriented)

<?php
// Database connection
$servername = "localhost";
$username = "root";
$password = "";
$database = "my_database";

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

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

// Update email for user with ID = 2
$sql = "UPDATE Users SET email = '[email protected]' WHERE id = 2";

if ($conn->query($sql) === TRUE) {
    echo "Record updated successfully";
} else {
    echo "Error updating record: " . $conn->error;
}

// Close connection
$conn->close();
?>

Example 2: Using Prepared Statements for Security

When user input is involved (e.g., form submissions), always use prepared statements to prevent SQL injection attacks.

<?php
// Database connection
$servername = "localhost";
$username = "root";
$password = "";
$database = "my_database";

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

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

// Variables for the update
$user_id = 2;
$new_email = "[email protected]";

// Prepare the SQL statement
$sql = "UPDATE Users SET email = ? WHERE id = ?";
$stmt = $conn->prepare($sql);

// Bind parameters
$stmt->bind_param("si", $new_email, $user_id); // "s" for string, "i" for integer

// Execute the statement
if ($stmt->execute()) {
    echo "Record updated successfully";
} else {
    echo "Error updating record: " . $stmt->error;
}

// Close statement and connection
$stmt->close();
$conn->close();
?>

Explanation:

  • Prepared Statements: Securely handle user input.
  • bind_param("si", $new_email, $user_id): Binds $new_email as a string and $user_id as an integer to the SQL query.
  • This method prevents malicious inputs, like SQL injection.

Updating Multiple Columns

If you need to update more than one column, you can simply add more column-value pairs in the SET clause.

<?php
// Update username and email for user with ID = 1
$sql = "UPDATE Users SET username = ?, email = ? WHERE id = ?";
$stmt = $conn->prepare($sql);
$stmt->bind_param("ssi", $new_username, $new_email, $user_id);

// Example values
$new_username = "JohnUpdated";
$new_email = "[email protected]";
$user_id = 1;

$stmt->execute();
?>

Best Practices for Updating Data

  • Always Use the WHERE Clause: Without it, all rows in the table will be updated. 
UPDATE Users SET email = '[email protected]'; -- Updates all rows
  • Use Prepared Statements: Prevents SQL injection attacks.
  • Backup Your Database: Before running updates, especially in production.
  • Test Queries First: Run your queries on a test database to ensure they work as intended.
  • Log Changes: Record the changes in an audit log table for tracking.

Full Example: Update User Data via HTML Form

Here’s an example of how to dynamically update a user’s data through a form submission.

HTML Form:

<form action="update_user.php" method="POST">
    <label for="user_id">User ID:</label>
    <input type="number" name="user_id" id="user_id" required>
    
    <label for="email">New Email:</label>
    <input type="email" name="email" id="email" required>
    
    <button type="submit">Update User</button>
</form>

PHP Script (update_user.php):

<?php
// Database connection
$servername = "localhost";
$username = "root";
$password = "";
$database = "my_database";

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

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

// Get user inputs
if ($_SERVER["REQUEST_METHOD"] == "POST") {
    $user_id = intval($_POST["user_id"]);
    $new_email = $_POST["email"];

    // Prepare the SQL statement
    $sql = "UPDATE Users SET email = ? WHERE id = ?";
    $stmt = $conn->prepare($sql);
    $stmt->bind_param("si", $new_email, $user_id);

    // Execute and confirm update
    if ($stmt->execute()) {
        echo "User with ID $user_id updated successfully.";
    } else {
        echo "Error updating user: " . $stmt->error;
    }

    $stmt->close();
}

$conn->close();
?>

Updating with a Condition Other Than ID

You can use any column or condition in the WHERE clause. For example, to update users with a specific username:

$sql = "UPDATE Users SET email = '[email protected]' WHERE username = 'MarkLee'";

Or use logical conditions:

UPDATE Users 
SET email = '[email protected]' 
WHERE id > 5 AND username LIKE 'Test%';

Conclusion

Updating data in a MySQL database with PHP is a fundamental skill for developers. By following best practices, such as using prepared statements and testing queries, you can ensure secure and efficient database updates.

Leave a Comment