Easy and Simple Add, Edit, Delete MySQL Table Rows using PHP/MySQLi Tutorial

Introduction

This tutorial will guide you through the process of adding, editing, and deleting rows in a MySQL table using PHP and MySQLi. It is designed for beginners who want to learn how to manage database records through a web application interface. By the end of this tutorial, you'll have a solid understanding of CRUD operations (Create, Read, Update, Delete) and how to implement them using PHP.

Step 1: Set Up Your Environment

Before you start coding, ensure that you have the following installed:

• A local server environment (like XAMPP or WAMP)
• PHP
• MySQL

Practical Tips:

• Make sure your local server is running.
• Access phpMyAdmin to manage your databases.

Step 2: Create a MySQL Database and Table

1. Open phpMyAdmin.

2. Click on the "Databases" tab and create a new database (e.g., test_db).

3. After creating the database, select it and run the following SQL command to create a table:

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

Common Pitfalls:

• Ensure that the database and table names are correctly spelled.
• Check that the columns have the right data types.

Step 3: Connect to the Database

Create a PHP file (e.g., db.php) to handle database connections. Use the following code:

<?php
$host = "localhost";
$username = "root"; // Change if necessary
$password = ""; // Change if necessary
$dbname = "test_db";

$conn = new mysqli($host, $username, $password, $dbname);
if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
}
?>

Practical Tips:

• Always handle database connection errors gracefully.

Step 4: Create the Add Functionality

1. Create a form in an HTML file (e.g., add_user.php) to input new user data:

   <form action="insert_user.php" method="POST">
       <input type="text" name="name" placeholder="Enter Name" required>
       <input type="email" name="email" placeholder="Enter Email" required>
       <button type="submit">Add User</button>
   </form>
   

2. Create insert_user.php to handle form submission:

   <?php
   include 'db.php';
   
   if ($_SERVER["REQUEST_METHOD"] == "POST") {
       $name = $_POST['name'];
       $email = $_POST['email'];
   
       $sql = "INSERT INTO users (name, email) VALUES ('$name', '$email')";
       if ($conn->query($sql) === TRUE) {
           echo "New user added successfully";
       } else {
           echo "Error: " . $sql . "<br>" . $conn->error;
       }
       $conn->close();
   }
   ?>
   

Common Pitfalls:

• Sanitize user inputs to prevent SQL injection attacks.

Step 5: Create the Edit Functionality

1. Create a form for editing user data (e.g., edit_user.php):

   <form action="update_user.php" method="POST">
       <input type="hidden" name="id" value="<?php echo $user_id; ?>">
       <input type="text" name="name" value="<?php echo $user_name; ?>" required>
       <input type="email" name="email" value="<?php echo $user_email; ?>" required>
       <button type="submit">Update User</button>
   </form>
   

2. Create update_user.php to update user data:

   <?php
   include 'db.php';
   
   if ($_SERVER["REQUEST_METHOD"] == "POST") {
       $id = $_POST['id'];
       $name = $_POST['name'];
       $email = $_POST['email'];
   
       $sql = "UPDATE users SET name='$name', email='$email' WHERE id=$id";
       if ($conn->query($sql) === TRUE) {
           echo "User updated successfully";
       } else {
           echo "Error: " . $sql . "<br>" . $conn->error;
       }
       $conn->close();
   }
   ?>
   

Practical Tips:

• Use prepared statements for improved security.

Step 6: Create the Delete Functionality

1. Create a delete operation (e.g., delete_user.php):

   <?php
   include 'db.php';
   
   if (isset($_GET['id'])) {
       $id = $_GET['id'];
       $sql = "DELETE FROM users WHERE id=$id";
       if ($conn->query($sql) === TRUE) {
           echo "User deleted successfully";
       } else {
           echo "Error deleting user: " . $conn->error;
       }
       $conn->close();
   }
   ?>
   

Common Pitfalls:

• Confirm user deletion with a confirmation dialog to prevent accidental deletions.

Conclusion

In this tutorial, you learned how to set up a PHP application that can add, edit, and delete rows in a MySQL database. By implementing these CRUD operations, you can manage your data effectively.

Next Steps:

• Explore advanced features like user authentication.
• Consider using frameworks like Laravel for more complex applications.
• Experiment with AJAX to enhance user experience without page reloads.