Seamless PHP CRUD Operations with jQuery, Ajax, and MySQL Best in 2024

Blog, Software Engineering, Software Testing

PHP CRUD Operations without Page Refresh using jQuery, Ajax, and MySQL

6 min read

Kanishka Sharma

September 30, 2024

PHP CRUD Operations with jQuery Here’s an example of how to implement PHP CRUD (Create, Read, Update, Delete) operations without a page refresh using jQuery, Ajax, and MySQL.

PHP CRUD Operations with jQuery

Step 1: Database Setup (MySQL)

First, create a database and a table where the CRUD operations will be performed.

CREATE DATABASE my_database;

USE my_database;

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

PHP CRUD Operations with jQuery

Step 2: HTML & jQuery Setup

Create an HTML page where users can interact with the CRUD operations. Use jQuery and Ajax to send the requests asynchronously.

<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <title>PHP CRUD with Ajax</title>
    <script src="https://code.jquery.com/jquery-3.6.0.min.js"></script>
    <link rel="stylesheet" href="https://stackpath.bootstrapcdn.com/bootstrap/4.5.2/css/bootstrap.min.css">
</head>
<body>
    <div class="container mt-4">
        <h1 class="text-center">PHP CRUD Operations without Page Refresh</h1>

        <!-- Add New User Form -->
        <form id="userForm">
            <input type="hidden" id="user_id">
            <div class="form-group">
                <label>Name</label>
                <input type="text" id="name" class="form-control" required>
            </div>
            <div class="form-group">
                <label>Email</label>
                <input type="email" id="email" class="form-control" required>
            </div>
            <button type="submit" class="btn btn-primary">Save</button>
        </form>

        <hr>

        <!-- User List -->
        <table class="table table-bordered mt-4">
            <thead>
                <tr>
                    <th>ID</th>
                    <th>Name</th>
                    <th>Email</th>
                    <th>Actions</th>
                </tr>
            </thead>
            <tbody id="userTableBody">
                <!-- User rows will be loaded here -->
            </tbody>
        </table>
    </div>

    <script>
        $(document).ready(function() {
            loadUsers(); // Load users when the page is loaded

            // Add or Update User
            $('#userForm').on('submit', function(e) {
                e.preventDefault();
                var userId = $('#user_id').val();
                var name = $('#name').val();
                var email = $('#email').val();

                $.ajax({
                    url: 'crud.php',
                    type: 'POST',
                    data: { user_id: userId, name: name, email: email },
                    success: function(response) {
                        $('#userForm')[0].reset();
                        $('#user_id').val('');
                        loadUsers();
                        alert(response);
                    }
                });
            });

            // Edit User
            $(document).on('click', '.editBtn', function() {
                var id = $(this).data('id');

                $.ajax({
                    url: 'crud.php',
                    type: 'POST',
                    data: { action: 'edit', id: id },
                    success: function(data) {
                        var user = JSON.parse(data);
                        $('#user_id').val(user.id);
                        $('#name').val(user.name);
                        $('#email').val(user.email);
                    }
                });
            });

            // Delete User
            $(document).on('click', '.deleteBtn', function() {
                var id = $(this).data('id');
                if (confirm("Are you sure you want to delete this user?")) {
                    $.ajax({
                        url: 'crud.php',
                        type: 'POST',
                        data: { action: 'delete', id: id },
                        success: function(response) {
                            loadUsers();
                            alert(response);
                        }
                    });
                }
            });

            // Load Users (Read)
            function loadUsers() {
                $.ajax({
                    url: 'crud.php',
                    type: 'GET',
                    success: function(response) {
                        $('#userTableBody').html(response);
                    }
                });
            }
        });
    </script>
</body>
</html>

PHP CRUD Operations with jQuery

Step 3: Backend (PHP)

Create a file named crud.php to handle the backend logic for the CRUD operations.

<?php
// Database connection
$servername = "localhost";
$username = "root";
$password = "";
$dbname = "my_database";
$conn = new mysqli($servername, $username, $password, $dbname);

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

// Create or Update user
if ($_SERVER['REQUEST_METHOD'] == 'POST') {
    $name = $_POST['name'];
    $email = $_POST['email'];

    if (!empty($_POST['user_id'])) {
        // Update user
        $id = $_POST['user_id'];
        $sql = "UPDATE users SET name='$name', email='$email' WHERE id=$id";
        if ($conn->query($sql) === TRUE) {
            echo "User updated successfully";
        } else {
            echo "Error: " . $conn->error;
        }
    } else {
        // Create new user
        $sql = "INSERT INTO users (name, email) VALUES ('$name', '$email')";
        if ($conn->query($sql) === TRUE) {
            echo "New user created successfully";
        } else {
            echo "Error: " . $conn->error;
        }
    }
}

// Edit user (Fetch user data for editing)
if (isset($_POST['action']) && $_POST['action'] == 'edit') {
    $id = $_POST['id'];
    $sql = "SELECT * FROM users WHERE id=$id";
    $result = $conn->query($sql);
    $user = $result->fetch_assoc();
    echo json_encode($user);
}

// Delete user
if (isset($_POST['action']) && $_POST['action'] == 'delete') {
    $id = $_POST['id'];
    $sql = "DELETE FROM users WHERE id=$id";
    if ($conn->query($sql) === TRUE) {
        echo "User deleted successfully";
    } else {
        echo "Error: " . $conn->error;
    }
}

// Fetch all users (Read)
if ($_SERVER['REQUEST_METHOD'] == 'GET') {
    $sql = "SELECT * FROM users";
    $result = $conn->query($sql);
    if ($result->num_rows > 0) {
        while ($row = $result->fetch_assoc()) {
            echo "<tr>
                <td>{$row['id']}</td>
                <td>{$row['name']}</td>
                <td>{$row['email']}</td>
                <td>
                    <button class='btn btn-sm btn-info editBtn' data-id='{$row['id']}'>Edit</button>
                    <button class='btn btn-sm btn-danger deleteBtn' data-id='{$row['id']}'>Delete</button>
                </td>
            </tr>";
        }
    } else {
        echo "<tr><td colspan='4'>No users found</td></tr>";
    }
}

$conn->close();
?>

PHP CRUD Operations with jQuery

Explanation:

HTML & jQuery:
- The HTML page contains a form to create and edit users. It uses jQuery to send Ajax requests to the backend.
- CRUD operations (Create, Read, Update, Delete) are handled via Ajax requests to the crud.php file.
PHP Backend:
- Handles the database operations for creating, reading, updating, and deleting records in the MySQL database.
- The backend processes both POST and GET requests to execute the appropriate action based on the data received.

PHP CRUD Operations with jQuery

Step 4: Testing the CRUD

Create: Fill in the form, click the submit button, and the new user will be added to the database without a page refresh.
Read: The user list is fetched and displayed dynamically when the page loads.
Update: Click the “Edit” button next to a user to load their data into the form, update the details, and click submit to save the changes.
Delete: Click the “Delete” button next to a user to remove them from the database.

With these steps, you’ve implemented PHP CRUD operations with jQuery, Ajax, and MySQL without page refresh!

PHP CRUD Operations with jQuery

Kanishka Sharma

September 30, 2024

Rahul gupta

September 25, 2024

Super

Best Installing Laravel 11 A Step-by-Step Guide for Beginners

September 30, 2024

[…] You’ve successfully installed Laravel 11! This powerful framework now forms the backbone of your web development project. As you proceed, don’t forget to check Laravel’s official documentation for the latest updates and tips on how to leverage the full potential of this modern PHP framework. […]