{"id":111,"date":"2024-12-11T19:10:46","date_gmt":"2024-12-11T19:10:46","guid":{"rendered":"https:\/\/blog.vyomscode.com\/?p=111"},"modified":"2024-12-11T19:10:46","modified_gmt":"2024-12-11T19:10:46","slug":"fetching-editing-and-deleting-data-using-php-javascript-ajax-mysql-and-datatables","status":"publish","type":"post","link":"https:\/\/blog.vyomscode.com\/index.php\/2024\/12\/11\/fetching-editing-and-deleting-data-using-php-javascript-ajax-mysql-and-datatables\/","title":{"rendered":"Fetching, Editing, and Deleting Data Using PHP, JavaScript (AJAX), MySQL, and DataTables"},"content":{"rendered":"\n
\n

Introduction<\/h1>\n<\/blockquote>\n\n\n\n

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.<\/p>\n\n\n\n

\n

Prerequisites<\/h2>\n<\/blockquote>\n\n\n\n
    \n
  1. Basic knowledge of PHP, MySQL, and JavaScript.<\/li>\n\n\n\n
  2. A web server with PHP and MySQL support.<\/li>\n\n\n\n
  3. Installed libraries: DataTables, jQuery, and Bootstrap (optional for styling).<\/li>\n<\/ol>\n\n\n\n

    Setup<\/h2>\n\n\n\n

    1. Create a MySQL Database and Table<\/h3>\n\n\n\n
    \n

    Use the following SQL to create a database and a table:<\/p>\n<\/blockquote>\n\n\n\n

    CREATE DATABASE crud_demo;\n\nUSE crud_demo;\n\nCREATE TABLE employees (\n    id INT AUTO_INCREMENT PRIMARY KEY,\n    name VARCHAR(100),\n    position VARCHAR(100),\n    salary DECIMAL(10, 2)\n);\n\nINSERT INTO employees (name, position, salary) VALUES\n('Alice Johnson', 'Manager', 75000),\n('Bob Smith', 'Developer', 60000),\n('Charlie Brown', 'Designer', 50000);<\/code><\/pre>\n\n\n\n
    This script creates an employees<\/code> table with sample data to use in our CRUD operations.<\/p>\n\n\n\n
    2. Setup PHP Backend<\/h3>\n\n\n\n
    \n
     – Fetch records from the database.<\/strong><\/p>\n<\/blockquote>\n\n\n\n
    <?php\nheader('Content-Type: application\/json');\n\n$conn = new mysqli('localhost', 'root', '', 'crud_demo');\nif ($conn->connect_error) {\n    die(json_encode(['error' => 'Database connection failed']));\n}\n\n$result = $conn->query(\"SELECT * FROM employees\");\n$data = [];\nwhile ($row = $result->fetch_assoc()) {\n    $data[] = $row;\n}\n\necho json_encode($data);\n?><\/code><\/pre>\n\n\n\n
    Explanation:<\/strong> This PHP script connects to the crud_demo<\/code> database and retrieves all records from the employees<\/code> table. The data is encoded into JSON format and returned to the frontend for rendering.<\/p>\n\n\n\n
    “ – Update a record.<\/p>\n\n\n\n
    <?php\nheader('Content-Type: application\/json');\n\n$conn = new mysqli('localhost', 'root', '', 'crud_demo');\nif ($conn->connect_error) {\n    die(json_encode(['error' => 'Database connection failed']));\n}\n\n$id = $_POST['id'];\n$name = $_POST['name'];\n$position = $_POST['position'];\n$salary = $_POST['salary'];\n\n$sql = \"UPDATE employees SET name = ?, position = ?, salary = ? WHERE id = ?\";\n$stmt = $conn->prepare($sql);\n$stmt->bind_param('ssdi', $name, $position, $salary, $id);\n$stmt->execute();\n\necho json_encode(['success' => $stmt->affected_rows > 0]);\n?><\/code><\/pre>\n\n\n\n
    Explanation:<\/strong> This script accepts the employee id<\/code>, name<\/code>, position<\/code>, and salary<\/code> 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.<\/p>\n\n\n\n
    “ – Delete a record.<\/p>\n\n\n\n
    <?php\nheader('Content-Type: application\/json');\n\n$conn = new mysqli('localhost', 'root', '', 'crud_demo');\nif ($conn->connect_error) {\n    die(json_encode(['error' => 'Database connection failed']));\n}\n\n$id = $_POST['id'];\n$sql = \"DELETE FROM employees WHERE id = ?\";\n$stmt = $conn->prepare($sql);\n$stmt->bind_param('i', $id);\n$stmt->execute();\n\necho json_encode(['success' => $stmt->affected_rows > 0]);\n?><\/code><\/pre>\n\n\n\n
    Explanation:<\/strong> This script deletes an employee record based on the provided id<\/code>. Prepared statements are used to prevent SQL injection. The response indicates the success of the deletion operation.<\/p>\n\n\n\n
    3. Frontend with DataTables and AJAX<\/h3>\n\n\n\n
    HTML Structure<\/strong><\/p>\n\n\n\n
    <!DOCTYPE html>\n<html lang=\"en\">\n<head>\n    <meta charset=\"UTF-8\">\n    <meta name=\"viewport\" content=\"width=device-width, initial-scale=1.0\">\n    <title>CRUD with PHP and DataTables<\/title>\n    <link rel=\"stylesheet\" href=\"https:\/\/cdn.datatables.net\/1.13.6\/css\/jquery.dataTables.min.css\">\n    <link rel=\"stylesheet\" href=\"https:\/\/cdnjs.cloudflare.com\/ajax\/libs\/bootstrap\/5.3.3\/css\/bootstrap.min.css\">\n<\/head>\n<body>\n<div class=\"container mt-5\">\n    <h2>Employee Management<\/h2>\n    <table id=\"employeeTable\" class=\"display table table-bordered\">\n        <thead>\n            <tr>\n                <th>ID<\/th>\n                <th>Name<\/th>\n                <th>Position<\/th>\n                <th>Salary<\/th>\n                <th>Actions<\/th>\n            <\/tr>\n        <\/thead>\n    <\/table>\n<\/div>\n\n<!-- Edit Modal -->\n<div class=\"modal\" id=\"editModal\" tabindex=\"-1\">\n    <div class=\"modal-dialog\">\n        <div class=\"modal-content\">\n            <div class=\"modal-header\">\n                <h5 class=\"modal-title\">Edit Employee<\/h5>\n                <button type=\"button\" class=\"btn-close\" data-bs-dismiss=\"modal\"><\/button>\n            <\/div>\n            <div class=\"modal-body\">\n                <form id=\"editForm\">\n                    <input type=\"hidden\" id=\"editId\">\n                    <div class=\"mb-3\">\n                        <label for=\"editName\" class=\"form-label\">Name<\/label>\n                        <input type=\"text\" class=\"form-control\" id=\"editName\" required>\n                    <\/div>\n                    <div class=\"mb-3\">\n                        <label for=\"editPosition\" class=\"form-label\">Position<\/label>\n                        <input type=\"text\" class=\"form-control\" id=\"editPosition\" required>\n                    <\/div>\n                    <div class=\"mb-3\">\n                        <label for=\"editSalary\" class=\"form-label\">Salary<\/label>\n                        <input type=\"number\" class=\"form-control\" id=\"editSalary\" required>\n                    <\/div>\n                <\/form>\n            <\/div>\n            <div class=\"modal-footer\">\n                <button type=\"button\" class=\"btn btn-secondary\" data-bs-dismiss=\"modal\">Close<\/button>\n                <button type=\"button\" class=\"btn btn-primary\" id=\"saveChanges\">Save changes<\/button>\n            <\/div>\n        <\/div>\n    <\/div>\n<\/div>\n\n<script src=\"https:\/\/code.jquery.com\/jquery-3.6.0.min.js\"><\/script>\n<script src=\"https:\/\/cdn.datatables.net\/1.13.6\/js\/jquery.dataTables.min.js\"><\/script>\n<script src=\"https:\/\/cdnjs.cloudflare.com\/ajax\/libs\/bootstrap\/5.3.3\/js\/bootstrap.bundle.min.js\"><\/script>\n<script>\n$(document).ready(function() {\n    const table = $('#employeeTable').DataTable({\n        ajax: 'fetch_data.php',\n        columns: [\n            { data: 'id' },\n            { data: 'name' },\n            { data: 'position' },\n            { data: 'salary' },\n            {\n                data: 'id',\n                render: function(id) {\n                    return `\n                        <button class=\"btn btn-sm btn-warning edit-btn\" data-id=\"${id}\">Edit<\/button>\n                        <button class=\"btn btn-sm btn-danger delete-btn\" data-id=\"${id}\">Delete<\/button>\n                    `;\n                }\n            }\n        ]\n    });\n\n    \/\/ Edit functionality\n    $('#employeeTable').on('click', '.edit-btn', function() {\n        const id = $(this).data('id');\n        const rowData = table.row($(this).parents('tr')).data();\n        $('#editId').val(id);\n        $('#editName').val(rowData.name);\n        $('#editPosition').val(rowData.position);\n        $('#editSalary').val(rowData.salary);\n        $('#editModal').modal('show');\n    });\n\n    $('#saveChanges').click(function() {\n        const id = $('#editId').val();\n        const name = $('#editName').val();\n        const position = $('#editPosition').val();\n        const salary = $('#editSalary').val();\n\n        $.post('update_data.php', { id, name, position, salary }, function(response) {\n            if (response.success) {\n                $('#editModal').modal('hide');\n                table.ajax.reload();\n            } else {\n                alert('Update failed!');\n            }\n        });\n    });\n\n    \/\/ Delete functionality\n    $('#employeeTable').on('click', '.delete-btn', function() {\n        if (!confirm('Are you sure you want to delete this record?')) return;\n\n        const id = $(this).data('id');\n        $.post('delete_data.php', { id }, function(response) {\n            if (response.success) {\n                table.ajax.reload();\n            } else {\n                alert('Delete failed!');\n            }\n        });\n    });\n});\n<\/script>\n<\/body>\n<\/html><\/code><\/pre>\n\n\n\n
    Explanation:<\/strong><\/p>\n\n\n\n
      \n
    1. DataTables Initialization:<\/strong> The DataTable<\/code> fetches records using AJAX from fetch_data.php<\/code> and dynamically populates the table.<\/li>\n\n\n\n
    2. Edit Button:<\/strong> Opens a modal with prefilled form fields for the selected row’s data.<\/li>\n\n\n\n
    3. Save Changes:<\/strong> Sends the updated data to update_data.php<\/code> using an AJAX POST request. On success, the table is refreshed.<\/li>\n\n\n\n
    4. Delete Button:<\/strong> Prompts the user for confirmation and sends a delete request to delete_data.php<\/code>. On success, the table is refreshed.<\/li>\n<\/ol>\n\n\n\n
      Benefits of Using DataTables<\/h2>\n\n\n\n