Tutorials


PHP MySQL Updating records in a database


Updating records in a MySQL database using a form in PHP (procedural style) involves the following steps:

  1. Create an HTML form to allow users to edit data.
  2. Retrieve the existing record from the database to pre-fill the form.
  3. Submit the form data to a PHP script that processes the update.
  4. Write the SQL UPDATE query to modify the record.
  5. Execute the query using mysqli_query().
  6. Redirect the user or show a success/failure message.

Example: Updating a User's Information

Assume you have a users table with columns: id, username, email, and created_at. We will update the username and email fields for a specific user.

Step 1: Create the Edit Form

This form will allow the user to edit their username and email. First, we need to pre-fill the form with the current data from the database.

<?php
// Step 1: Connect to the database
$servername = "localhost";
$username = "root";
$password = "";
$dbname = "test_db";

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

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

// Step 2: Get the user's current data
// Assuming we are passing the user's ID as a GET parameter in the URL
$user_id = $_GET['id'];  // Get the user ID from the URL
$sql = "SELECT * FROM users WHERE id = $user_id";
$result = mysqli_query($conn, $sql);

// Check if the user exists
if (mysqli_num_rows($result) > 0) {
    $user = mysqli_fetch_assoc($result);  // Fetch the user data
} else {
    die("User not found.");
}
?>

<!-- Step 3: Create the form, pre-fill with current data -->
<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <title>Edit User</title>
</head>
<body>
    <h2>Edit User Information</h2>
    <form action="update_user.php" method="POST">
        <input type="hidden" name="id" value="<?php echo $user['id']; ?>">  <!-- Hidden ID field -->
        
        <label for="username">Username:</label>
        <input type="text" id="username" name="username" value="<?php echo $user['username']; ?>" required><br><br>

        <label for="email">Email:</label>
        <input type="email" id="email" name="email" value="<?php echo $user['email']; ?>" required><br><br>

        <input type="submit" value="Update">
    </form>
</body>
</html>

<?php
// Close the connection
mysqli_close($conn);
?>

Explanation:

  • Connect to the database: A connection to the database is established using mysqli_connect().
  • Retrieve the current user data: Using the user_id from the URL, we query the database to fetch the current details of the user (username and email).
  • Pre-fill the form: The HTML form is pre-filled with the current data using PHP's echo to insert the values into the form fields.
  • The form submits the updated data to update_user.php via a POST request.

Step 2: Processing the Form Submission (update_user.php)

This PHP script handles the form submission and updates the record in the database.

<?php
// Step 1: Check if the form was submitted
if ($_SERVER['REQUEST_METHOD'] == 'POST') {
    // Step 2: Sanitize and validate the input data
    $id = intval($_POST['id']);
    $username = trim($_POST['username']);
    $email = trim($_POST['email']);

    // Validate input (example of simple validation)
    if (empty($username) || empty($email)) {
        die("All fields are required.");
    }

    if (!filter_var($email, FILTER_VALIDATE_EMAIL)) {
        die("Invalid email format.");
    }

    // Step 3: Connect to the MySQL database
    $servername = "localhost";
    $db_username = "root";
    $db_password = "";
    $dbname = "test_db";

    // Create the connection
    $conn = mysqli_connect($servername, $db_username, $db_password, $dbname);

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

    // Step 4: Write the SQL query to update the record
    $sql = "UPDATE users SET username = '$username', email = '$email' WHERE id = $id";

    // Step 5: Execute the query
    if (mysqli_query($conn, $sql)) {
        echo "Record updated successfully.";
        // Optionally, redirect to another page
        // header("Location: success.php");
    } else {
        echo "Error updating record: " . mysqli_error($conn);
    }

    // Step 6: Close the database connection
    mysqli_close($conn);
}
?>

Explanation:

  • Sanitize and validate input: The user’s input (username and email) is trimmed and validated. In this case, we check if the fields are empty and if the email is valid.

  • Connect to the database: The connection is established using mysqli_connect().

  • Write the SQL UPDATE query: The UPDATE query modifies the username and email fields for the user with the matching id.

    UPDATE users SET username = 'new_username', email = 'new_email' WHERE id = 1;

  • Execute the query: The mysqli_query() function runs the UPDATE query, and the result is checked to see if the update was successful.

  • Close the connection: The database connection is closed using mysqli_close().

Security Considerations

  1. SQL Injection: The above example directly embeds the user input into the SQL query, which is vulnerable to SQL injection. To avoid this, you should use prepared statements.

    Example with prepared statements:

    // Step 4: Write the SQL query using prepared statements
$sql = "UPDATE users SET username = ?, email = ? WHERE id = ?";
$stmt = mysqli_prepare($conn, $sql);

// Step 5: Bind parameters and execute the query
mysqli_stmt_bind_param($stmt, 'ssi', $username, $email, $id);
if (mysqli_stmt_execute($stmt)) {
    echo "Record updated successfully.";
} else {
    echo "Error updating record: " . mysqli_error($conn);
}

  2. Input Validation: Always validate the input to ensure that data is in the correct format. For example, use filter_var() to validate email addresses and trim unnecessary white spaces.

  3. Escaping Output: When outputting user-provided data (e.g., pre-filling form fields), ensure the data is properly escaped using htmlspecialchars() to prevent XSS (Cross-Site Scripting) attacks.

    Example:

    <input type="text" name="username" value="<?php echo htmlspecialchars($user['username']); ?>">

Conclusion

To update records using PHP MySQL procedural style:

  1. Create an HTML form pre-filled with the current record.
  2. Submit the updated data to a PHP script via POST.
  3. Process the form data: Validate the input, connect to the database, write the SQL UPDATE query, and execute it using mysqli_query().
  4. Handle the result: Display a success or error message based on the outcome.


Next Previous




@aCodeTutorials All Rights Reserved
privacy policy | about