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.