Update Data using PHP & MySql Database

Author Esenceweb Dec 26, 2024 0 Comments 4531 Views 100 Likes

Update Data using PHP & MySQL Database

Updating data in a MySQL database is a fundamental operation in web development. This guide will demonstrate how to update records in a MySQL database using core PHP.

Prerequisites

Basic understanding of PHP and MySQL.
A MySQL database set up with a table to update records.
A web server (e.g., XAMPP, WAMP, or a live server) to run PHP scripts.

Steps to Update Data in MySQL

Create a Database and Table

CREATE DATABASE demo_db;

USE demo_db;

CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(100) NOT NULL,
    age INT NOT NULL
);

INSERT INTO users (name, email, age) VALUES
('John Doe', 'john.doe@example.com', 25),
('Jane Smith', 'jane.smith@example.com', 30);

Establish a Database Connection in PHP

<?php
// Database connection settings
$host = 'localhost';
$user = 'root';
$password = ''; // Use your database password
$database = 'demo_db';

// Create connection
$conn = new mysqli($host, $user, $password, $database);

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

Create a Form to Collect Data

<?php
// Fetch the existing data for the user to edit
$id = $_GET['id'];
$sql = "SELECT * FROM users WHERE id = $id";
$result = $conn->query($sql);
$user = $result->fetch_assoc();
?>

<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <title>Update User</title>
</head>
<body>
    <h2>Update User Information</h2>
    <form action="update.php" method="POST">
        <input type="hidden" name="id" value="<?php echo $user['id']; ?>">
        <label for="name">Name:</label>
        <input type="text" id="name" name="name" value="<?php echo $user['name']; ?>" required><br><br>

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

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

        <button type="submit">Update</button>
    </form>
</body>
</html>

Handle the Update Request in PHP

Create a file named update.php to process the form submission and update the database.

<?php
// Include the database connection file
include 'db_connection.php';

// Get data from the form
$id = $_POST['id'];
$name = $_POST['name'];
$email = $_POST['email'];
$age = $_POST['age'];

// Prepare the SQL query to update data
$sql = "UPDATE users SET name = ?, email = ?, age = ? WHERE id = ?";
$stmt = $conn->prepare($sql);
$stmt->bind_param("ssii", $name, $email, $age, $id);

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

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

Explanation of the Code

Database Connection: Establishes a connection to the MySQL database.
Fetching Data: Retrieves the existing data for the record to populate the form.
Form Submission: Sends the updated data to the update.php script via POST.
Prepared Statement: Prevents SQL injection by using parameterized queries to update the record.
Execution: Updates the record in the database and provides feedback to the user.

Final Notes

Ensure proper validation and sanitization of input data to secure your application.
Use HTTPS for secure data transmission.
Test thoroughly to handle edge cases, such as invalid inputs or database errors.