PHP : MySQL Update Data

To update data in a MySQL database table using PHP, you can use the UPDATE SQL statement. This allows you to modify existing rows in a table based on certain conditions. Below are examples using both mysqli and PDO to update data in a users table.

Using mysqli:

Here's an example of using mysqli to update the email of a user in the users table where the id is equal to 1:

// Database credentials
$servername = "localhost";
$username = "your_username";
$password = "your_password";
$database = "your_database";

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

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

// New email and ID of the user to update
$newEmail = "newemail@example.com";
$id = 1;

// SQL query to update data
$sql = "UPDATE users SET email = ? WHERE id = ?";
$stmt = $conn->prepare($sql);
$stmt->bind_param("si", $newEmail, $id); // "si" for string and integer
$stmt->execute();

if ($stmt->affected_rows > 0) {
    echo "Record updated successfully";
} else {
    echo "No records updated";
}

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

Using PDO:

Here's the same example using PDO:

// Database credentials
$servername = "localhost";
$username = "your_username";
$password = "your_password";
$database = "your_database";

try {
    // Create connection
    $conn = new PDO("mysql:host=$servername;dbname=$database", $username, $password);
    $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

    // New email and ID of the user to update
    $newEmail = "newemail@example.com";
    $id = 1;

    // SQL query to update data
    $sql = "UPDATE users SET email = :email WHERE id = :id";
    $stmt = $conn->prepare($sql);
    $stmt->bindParam(':email', $newEmail);
    $stmt->bindParam(':id', $id, PDO::PARAM_INT);
    $stmt->execute();

    if ($stmt->rowCount() > 0) {
        echo "Record updated successfully";
    } else {
        echo "No records updated";
    }
} catch (PDOException $e) {
    echo "Error: " . $e->getMessage();
} finally {
    // Close connection
    $conn = null;
}
 

Explanation:

• Both examples use an SQL UPDATE users SET email = ? WHERE id = ? query to update the email column of a user in the users table where the id matches the specified value ( 1 in this case).
• The WHERE clause is used to specify the condition for the update.
• In the mysqli example, bind_param("si", $newEmail, $id) is used to bind the parameters $newEmail as a string and $id as an integer.
• In the PDO example, bindParam( :email , $newEmail) and bindParam( :id, $id, PDO::PARAM_INT) are used to bind the parameters.

Important Notes:

• Adjust the $newEmail and $id variables to the values you want to update.
• The UPDATE statement can be used with different columns and conditions for more complex updates.
• Always verify that the connection is successful before executing queries.
• Close the connection after use to free up resources.

Running these scripts will update the email of a user in the users table where the id matches the specified value ( 1 ). Adjust the condition and columns in the UPDATE statement as needed for your specific query.