Author Esenceweb
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.phpscript 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.
0 Replies to “Update Data Using PHP & MySql Database”
Leave a Reply
Your email address will not be published.