How to UPDATE data in a MySQL database of WordPress

A guide on how to use Prepared Statements in PHP to update data in the MySQL database of WordPress safely and effectively.

In this article, you'll learn how to connect to the MySQL database of WordPress and use PHP with Prepared Statements to execute an UPDATE statement, allowing you to update records in the data table.

<?php
// Connect to the MySQL database
$servername = "localhost";
$username = "root";
$password = "password";
$dbname = "wordpress_db";

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

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

// UPDATE statement with Prepared Statement
$update_query = "UPDATE wp_users SET user_email = ?, display_name = ? WHERE ID = ?";
$stmt = $conn->prepare($update_query);

// Parameters to bind
$new_email = "[email protected]";
$new_display_name = "New Display Name";
$user_id = 1;

// Bind parameters to Prepared Statement
$stmt->bind_param("ssi", $new_email, $new_display_name, $user_id);

// Execute the UPDATE statement
if ($stmt->execute()) {
    echo "Update successful!";
} else {
    echo "Error: " . $stmt->error;
}

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

Detailed explanation:

1. <?php: Opens the PHP tag.
2. // Connect to the MySQL database: Comment line.
3. ...: Declares variables to connect to the database, such as servername, username, password, and dbname.
4. $conn = new mysqli(...): Creates a connection to the database using MySQLi.
5. if ($conn->connect_error) {...}: Checks if the connection was successful.
6. $update_query = "UPDATE wp_users SET user_email = ?, display_name = ? WHERE ID = ?": Defines the UPDATE statement with parameters.
7. $stmt = $conn->prepare($update_query): Prepares the SQL statement for execution.
8. bind_param("ssi", ...): Binds parameters to the Prepared Statement with data type format.
9. if ($stmt->execute()) {...}: Executes the UPDATE statement and checks if it was successful.
10. echo "Update successful!";: Displays a success message.
11. stmt->close() and conn->close(): Closes the Prepared Statement and the connection to the database.

System Requirements:

• PHP 7.x or higher
• MySQL

How to install the libraries needed to run the PHP code above:

No additional libraries need to be installed if you are using a server that supports PHP and MySQL.

Tips:

• Make sure your MySQL server is running before attempting to connect.
• Double-check your connection details like servername, username, password, and dbname to avoid connection errors.
• Use Prepared Statements to protect against SQL injection attacks.