MySQL Update Data

PHP Update Data in MySQL

Update Data in a MySQL Table using MySQLi and PDO

The SQL UPDATE statement modifies existing records in a database table.

Basic Syntax:

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

Warning: The `WHERE` clause is crucial! It specifies which record(s) to update. Omitting the `WHERE` clause will update **all** records in the table, which is usually unintended and potentially destructive.

Security Best Practice: Prepared Statements

To prevent SQL injection vulnerabilities, always use prepared statements when executing queries with data that comes from external sources (like user input). Prepared statements separate the SQL command from the data, making it impossible for malicious input to alter the query structure.

Let’s assume we have a MyGuests table with columns id, firstname, lastname, email, and reg_date.

Example Scenario: Update the lastname for the guest with id = 2.

Example: MySQLi (Object-Oriented Style) with Prepared Statement

<?php
$servername = "localhost";
$username = "your_username"; // Replace with your DB username
$password = "your_password"; // Replace with your DB password
$dbname = "myDB"; // Replace with your DB name

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

// Check connection
if ($conn->connect_error) {
    // Use error logging in production instead of die()
    die("Connection failed: " . $conn->connect_error);
}

// SQL query with placeholders (?)
$sql = "UPDATE MyGuests SET lastname = ? WHERE id = ?";

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

if ($stmt === false) {
    // Handle error, e.g., log it
    die("Error preparing statement: " . $conn->error);
}

// Data to bind
$new_lastname = 'DoeUpdated';
$guest_id = 2;

// Bind parameters (s = string, i = integer)
// The types must match the placeholders in order
$stmt->bind_param("si", $new_lastname, $guest_id);

// Execute the statement
if ($stmt->execute()) {
    // Check how many rows were affected
    $affected_rows = $stmt->affected_rows;
    echo "Record updated successfully. {$affected_rows} row(s) affected.";
} else {
    // Handle execution error
    echo "Error updating record: " . $stmt->error;
}

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

Example: MySQLi (Procedural Style) with Prepared Statement

<?php
$servername = "localhost";
$username = "your_username"; // Replace with your DB username
$password = "your_password"; // Replace with your DB password
$dbname = "myDB"; // Replace with your DB name

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

// Check connection
if (!$conn) {
    // Use error logging in production instead of die()
    die("Connection failed: " . mysqli_connect_error());
}

// SQL query with placeholders (?)
$sql = "UPDATE MyGuests SET lastname = ? WHERE id = ?";

// Prepare the statement
$stmt = mysqli_prepare($conn, $sql);

if ($stmt === false) {
    // Handle error
    die("Error preparing statement: " . mysqli_error($conn));
}

// Data to bind
$new_lastname = 'DoeProcUpdated';
$guest_id = 2;

// Bind parameters (s = string, i = integer)
mysqli_stmt_bind_param($stmt, "si", $new_lastname, $guest_id);

// Execute the statement
if (mysqli_stmt_execute($stmt)) {
    $affected_rows = mysqli_stmt_affected_rows($stmt);
    echo "Record updated successfully. {$affected_rows} row(s) affected.";
} else {
    // Handle execution error
    echo "Error updating record: " . mysqli_stmt_error($stmt);
}

// Close statement and connection
mysqli_stmt_close($stmt);
mysqli_close($conn);
?>

Example: PDO with Prepared Statement

<?php
$servername = "localhost";
$username = "your_username"; // Replace with your DB username
$password = "your_password"; // Replace with your DB password
$dbname = "myDBPDO"; // Replace with your DB name
$charset = 'utf8mb4'; // Recommended charset

// Data Source Name (DSN)
$dsn = "mysql:host=$servername;dbname=$dbname;charset=$charset";

$options = [
    PDO::ATTR_ERRMODE            => PDO::ERRMODE_EXCEPTION, // Turn on errors in the form of exceptions
    PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,       // Make the default fetch be an associative array
    PDO::ATTR_EMULATE_PREPARES   => false,                  // Disable emulation of prepared statements for security
];

try {
    // Create PDO instance
    $pdo = new PDO($dsn, $username, $password, $options);

    // SQL query with named placeholders (:lastname, :id) or positional placeholders (?)
    // Using named placeholders here for clarity
    $sql = "UPDATE MyGuests SET lastname = :lastname WHERE id = :id";

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

    // Data to bind
    $new_lastname = 'DoePDOUpdated';
    $guest_id = 2;

    // Bind values to the placeholders
    // Can also bind directly in execute() array
    $stmt->bindValue(':lastname', $new_lastname, PDO::PARAM_STR);
    $stmt->bindValue(':id', $guest_id, PDO::PARAM_INT);

    /* Alternatively, bind in execute:
    $stmt->execute([
        ':lastname' => $new_lastname,
        ':id' => $guest_id
    ]);
    */

    // Execute the statement
    $stmt->execute();

    // Check how many rows were affected
    $affected_rows = $stmt->rowCount();
    echo "Record updated successfully. {$affected_rows} row(s) affected.";

} catch (PDOException $e) {
    // Handle PDO exceptions (connection or query errors)
    // Log the error in production
    echo "Database error: " . $e->getMessage();
    // For more detail: throw new PDOException($e->getMessage(), (int)$e->getCode());
}

// Connection is automatically closed when $pdo object goes out of scope or script ends.
// You can explicitly close by setting $pdo = null;
$pdo = null;
?>

Choosing Placeholders in PDO: Named placeholders (:name) can make queries more readable, especially with many parameters. Positional placeholders (?) are slightly more concise.

After running one of these examples to update the record where id = 2, the MyGuests table data might look like this (depending on which example was run):

id | firstname | lastname        | email            | reg_date
---|-----------|-----------------|------------------|--------------------
 1 | John      | Doe             | john@example.com | 2023-10-27 10:00:00
 2 | Mary      | DoeUpdated      | mary@example.com | 2023-10-27 10:05:00
...