PHP MySQL Update Data

To modify existing data in a MySQL table, you use the UPDATE SQL statement. This allows you to change specific fields for one or more records based on a condition.

The SQL Statement

The UPDATE statement specifies which table to update, the new values for the columns, and most importantly, a WHERE clause to target specific records.

UPDATE Users 
SET lastname = 'Doe' 
WHERE id = 2;

Crucial Warning: If you omit the WHERE clause, EVERY record in the table will be updated! Always double-check your conditions.

1. MySQLi (Object-Oriented)

<?php
    $conn = new mysqli($servername, $username, $password, $dbname);

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

    $sql = "UPDATE Users SET lastname='Doe' WHERE id=2";

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

    $conn->close();
?>

2. MySQLi (Procedural)

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

    if (!$conn) {
        die("Connection failed: " . mysqli_connect_error());
    }

    $sql = "UPDATE Users SET lastname='Doe' WHERE id=2";

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

    mysqli_close($conn);
?>

3. PDO

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

        $sql = "UPDATE Users SET lastname='Doe' WHERE id=2";

        // Prepare statement
        $stmt = $conn->prepare($sql);

        // execute the query
        $stmt->execute();

        // echo a message to say the UPDATE succeeded
        echo $stmt->rowCount() . " records UPDATED successfully";
    } catch(PDOException $e) {
        echo $sql . "<br>" . $e->getMessage();
    }

    $conn = null;
?>

Checking Success

It is often useful to know how many rows were actually changed by your update command:

MySQLi (OO): $conn->affected_rows;
MySQLi (Proc): mysqli_affected_rows($conn);
PDO: $stmt->rowCount();

Summary

Use UPDATE to change existing records.
Use SET to define the new values.
The WHERE clause is mandatory for targeting specific rows.
Check affected rows to verify the result of the operation.

Tip: You can update multiple columns at once by separating them with commas: SET col1='val1', col2='val2'.

« Previous Lesson Next Lesson »