How to write PHP script to update an existing MySQL table?

In PHP, you can update existing records in a MySQL table using the SQL UPDATE statement. Modern PHP uses MySQLi or PDO extensions to interact with databases securely and efficiently.

Using MySQLi (Procedural)

The procedural MySQLi approach provides a straightforward way to update database records −

<?php
    $servername = "localhost";
    $username = "root";
    $password = "password";
    $dbname = "TUTORIALS";

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

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

    // SQL to update record
    $sql = "UPDATE tutorials_tbl SET tutorial_title='Learning JAVA' WHERE tutorial_id=3";

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

    mysqli_close($conn);
?>

Using MySQLi (Object-Oriented)

The object-oriented MySQLi approach offers better organization and error handling −

<?php
    $servername = "localhost";
    $username = "root";
    $password = "password";
    $dbname = "TUTORIALS";

    // Create connection
    $conn = new mysqli($servername, $username, $password, $dbname);

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

    // SQL to update record
    $sql = "UPDATE tutorials_tbl SET tutorial_title='Learning JAVA' WHERE tutorial_id=3";

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

    $conn->close();
?>

Using Prepared Statements

For security and handling dynamic values, use prepared statements to prevent SQL injection −

<?php
    $servername = "localhost";
    $username = "root";
    $password = "password";
    $dbname = "TUTORIALS";

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

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

    $tutorial_id = 3;
    $new_title = "Learning JAVA";

    $stmt = $conn->prepare("UPDATE tutorials_tbl SET tutorial_title=? WHERE tutorial_id=?");
    $stmt->bind_param("si", $new_title, $tutorial_id);

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

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

Comparison

Conclusion

Use MySQLi or PDO instead of deprecated mysql_* functions. Prepared statements are recommended for handling user input to prevent SQL injection attacks and ensure data security.