by: vyomscodePosted on:

Fetching, Editing, and Deleting Data Using PHP, JavaScript (AJAX), MySQL, and DataTables

Introduction

Efficient data management is a crucial aspect of modern web applications. This blog will guide you through creating a dynamic data table that supports fetching, editing, and deleting records using PHP, JavaScript, AJAX, MySQL, and DataTables. By the end of this guide, you’ll have a fully functional, responsive, and user-friendly CRUD system.

Prerequisites

  1. Basic knowledge of PHP, MySQL, and JavaScript.
  2. A web server with PHP and MySQL support.
  3. Installed libraries: DataTables, jQuery, and Bootstrap (optional for styling).

Setup

1. Create a MySQL Database and Table

Use the following SQL to create a database and a table:

CREATE DATABASE crud_demo;

USE crud_demo;

CREATE TABLE employees (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100),
    position VARCHAR(100),
    salary DECIMAL(10, 2)
);

INSERT INTO employees (name, position, salary) VALUES
('Alice Johnson', 'Manager', 75000),
('Bob Smith', 'Developer', 60000),
('Charlie Brown', 'Designer', 50000);

This script creates an employees table with sample data to use in our CRUD operations.

2. Setup PHP Backend

– Fetch records from the database.

<?php
header('Content-Type: application/json');

$conn = new mysqli('localhost', 'root', '', 'crud_demo');
if ($conn->connect_error) {
    die(json_encode(['error' => 'Database connection failed']));
}

$result = $conn->query("SELECT * FROM employees");
$data = [];
while ($row = $result->fetch_assoc()) {
    $data[] = $row;
}

echo json_encode($data);
?>

Explanation: This PHP script connects to the crud_demo database and retrieves all records from the employees table. The data is encoded into JSON format and returned to the frontend for rendering.

“ – Update a record.

<?php
header('Content-Type: application/json');

$conn = new mysqli('localhost', 'root', '', 'crud_demo');
if ($conn->connect_error) {
    die(json_encode(['error' => 'Database connection failed']));
}

$id = $_POST['id'];
$name = $_POST['name'];
$position = $_POST['position'];
$salary = $_POST['salary'];

$sql = "UPDATE employees SET name = ?, position = ?, salary = ? WHERE id = ?";
$stmt = $conn->prepare($sql);
$stmt->bind_param('ssdi', $name, $position, $salary, $id);
$stmt->execute();

echo json_encode(['success' => $stmt->affected_rows > 0]);
?>

Explanation: This script accepts the employee id, name, position, and salary from an AJAX POST request. It uses prepared statements to securely update the record in the database. The response indicates whether the update was successful.

“ – Delete a record.

<?php
header('Content-Type: application/json');

$conn = new mysqli('localhost', 'root', '', 'crud_demo');
if ($conn->connect_error) {
    die(json_encode(['error' => 'Database connection failed']));
}

$id = $_POST['id'];
$sql = "DELETE FROM employees WHERE id = ?";
$stmt = $conn->prepare($sql);
$stmt->bind_param('i', $id);
$stmt->execute();

echo json_encode(['success' => $stmt->affected_rows > 0]);
?>

Explanation: This script deletes an employee record based on the provided id. Prepared statements are used to prevent SQL injection. The response indicates the success of the deletion operation.

3. Frontend with DataTables and AJAX

HTML Structure

<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <title>CRUD with PHP and DataTables</title>
    <link rel="stylesheet" href="https://cdn.datatables.net/1.13.6/css/jquery.dataTables.min.css">
    <link rel="stylesheet" href="https://cdnjs.cloudflare.com/ajax/libs/bootstrap/5.3.3/css/bootstrap.min.css">
</head>
<body>
<div class="container mt-5">
    <h2>Employee Management</h2>
    <table id="employeeTable" class="display table table-bordered">
        <thead>
            <tr>
                <th>ID</th>
                <th>Name</th>
                <th>Position</th>
                <th>Salary</th>
                <th>Actions</th>
            </tr>
        </thead>
    </table>
</div>

<!-- Edit Modal -->
<div class="modal" id="editModal" tabindex="-1">
    <div class="modal-dialog">
        <div class="modal-content">
            <div class="modal-header">
                <h5 class="modal-title">Edit Employee</h5>
                <button type="button" class="btn-close" data-bs-dismiss="modal"></button>
            </div>
            <div class="modal-body">
                <form id="editForm">
                    <input type="hidden" id="editId">
                    <div class="mb-3">
                        <label for="editName" class="form-label">Name</label>
                        <input type="text" class="form-control" id="editName" required>
                    </div>
                    <div class="mb-3">
                        <label for="editPosition" class="form-label">Position</label>
                        <input type="text" class="form-control" id="editPosition" required>
                    </div>
                    <div class="mb-3">
                        <label for="editSalary" class="form-label">Salary</label>
                        <input type="number" class="form-control" id="editSalary" required>
                    </div>
                </form>
            </div>
            <div class="modal-footer">
                <button type="button" class="btn btn-secondary" data-bs-dismiss="modal">Close</button>
                <button type="button" class="btn btn-primary" id="saveChanges">Save changes</button>
            </div>
        </div>
    </div>
</div>

<script src="https://code.jquery.com/jquery-3.6.0.min.js"></script>
<script src="https://cdn.datatables.net/1.13.6/js/jquery.dataTables.min.js"></script>
<script src="https://cdnjs.cloudflare.com/ajax/libs/bootstrap/5.3.3/js/bootstrap.bundle.min.js"></script>
<script>
$(document).ready(function() {
    const table = $('#employeeTable').DataTable({
        ajax: 'fetch_data.php',
        columns: [
            { data: 'id' },
            { data: 'name' },
            { data: 'position' },
            { data: 'salary' },
            {
                data: 'id',
                render: function(id) {
                    return `
                        <button class="btn btn-sm btn-warning edit-btn" data-id="${id}">Edit</button>
                        <button class="btn btn-sm btn-danger delete-btn" data-id="${id}">Delete</button>
                    `;
                }
            }
        ]
    });

    // Edit functionality
    $('#employeeTable').on('click', '.edit-btn', function() {
        const id = $(this).data('id');
        const rowData = table.row($(this).parents('tr')).data();
        $('#editId').val(id);
        $('#editName').val(rowData.name);
        $('#editPosition').val(rowData.position);
        $('#editSalary').val(rowData.salary);
        $('#editModal').modal('show');
    });

    $('#saveChanges').click(function() {
        const id = $('#editId').val();
        const name = $('#editName').val();
        const position = $('#editPosition').val();
        const salary = $('#editSalary').val();

        $.post('update_data.php', { id, name, position, salary }, function(response) {
            if (response.success) {
                $('#editModal').modal('hide');
                table.ajax.reload();
            } else {
                alert('Update failed!');
            }
        });
    });

    // Delete functionality
    $('#employeeTable').on('click', '.delete-btn', function() {
        if (!confirm('Are you sure you want to delete this record?')) return;

        const id = $(this).data('id');
        $.post('delete_data.php', { id }, function(response) {
            if (response.success) {
                table.ajax.reload();
            } else {
                alert('Delete failed!');
            }
        });
    });
});
</script>
</body>
</html>

Explanation:

  1. DataTables Initialization: The DataTable fetches records using AJAX from fetch_data.php and dynamically populates the table.
  2. Edit Button: Opens a modal with prefilled form fields for the selected row’s data.
  3. Save Changes: Sends the updated data to update_data.php using an AJAX POST request. On success, the table is refreshed.
  4. Delete Button: Prompts the user for confirmation and sends a delete request to delete_data.php. On success, the table is refreshed.

Benefits of Using DataTables

  • Responsiveness: Adjusts automatically for smaller screens.
  • Pagination and Search: Improves usability for large datasets.
  • AJAX Integration: Real-time updates without refreshing the page.

Conclusion

This tutorial demonstrated how to create a fully functional CRUD system using PHP, MySQL, AJAX, and DataTables. With this foundation, you can further enhance your application with additional features like sorting, filtering, or advanced validations.

Rudra

Leave a Reply

Your email address will not be published. Required fields are marked *