How to Delete Record Data using PHP Ajax | Design CSS

Step 1: connect_db.php

<?php  
$host = 'localhost';  
$user = 'root';  
$password = '';  
$database = 'testing';  

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

Step 2: insert.php

<?php  

include 'connect_db.php';

if (!empty($_POST)) {  
    $output = '';  
    $message = '';  

    // Sanitize and validate inputs
    $name = mysqli_real_escape_string($conn, trim($_POST["name"]));  
    $address = mysqli_real_escape_string($conn, trim($_POST["address"]));  
    $gender = mysqli_real_escape_string($conn, trim($_POST["gender"]));  
    $designation = mysqli_real_escape_string($conn, trim($_POST["designation"]));  
    $age = (int)$_POST["age"]; // Ensure age is an integer

    if (isset($_POST["employee_id"]) && $_POST["employee_id"] != '') {  
        // Update the record
        $query = "  
        UPDATE tbl_employee   
        SET name = ?,   
        address = ?,   
        gender = ?,   
        designation = ?,   
        age = ?   
        WHERE id = ?";  

        $stmt = $conn->prepare($query);
        $stmt->bind_param('ssssii', $name, $address, $gender, $designation, $age, $_POST["employee_id"]);
        $message = 'Data update has been successfully!';  
    } else {  
        // Insert new record
        $query = "  
        INSERT INTO tbl_employee (name, address, gender, designation, age)  
        VALUES (?, ?, ?, ?, ?)";  

        $stmt = $conn->prepare($query);
        $stmt->bind_param('ssssi', $name, $address, $gender, $designation, $age);
        $message = 'Data insert has been successfully!';  
    }

    if ($stmt->execute()) {  
        $output .= '<label class="text-success">' . $message . '</label>';  
        
        // Fetch the updated list of employees
        $select_query = "SELECT * FROM tbl_employee ORDER BY id DESC";  
        $result = $conn->query($select_query);  
        $output .= '  
        <table class="table table-striped table-hover">  
            <tr>  
                <th>Name</th>  
                <th>Address</th>  
                <th>Gender</th>  
                <th>Designation</th>  
                <th>Age</th>  
                <th>Edit</th>  
                <th>Delete</th>  
            </tr>';  

        while ($row = $result->fetch_assoc()) {  
            $output .= '  
            <tr>  
                <td>' . htmlspecialchars($row["name"]) . '</td>  
                <td>' . htmlspecialchars($row["address"]) . '</td>  
                <td>' . htmlspecialchars($row["gender"]) . '</td>  
                <td>' . htmlspecialchars($row["designation"]) . '</td>  
                <td>' . htmlspecialchars($row["age"]) . '</td>  
                <td><input type="button" name="edit" value="Edit" id="' . $row["id"] . '" class="btn btn-info btn-xs edit_data" /></td>  
                <td><a href="#deleteEmployeeModal" class="delete" data-toggle="modal"><i class="material-icons" data-toggle="tooltip" title="Delete">&#xE872;</i></a></td>  
            </tr>';  
        }  
        $output .= '</table>';  
    } else {  
        $output .= '<label class="text-danger">Error: ' . $conn->error . '</label>';  
    }

    echo $output;  
}  
?>

Step 3: select.php

<?php  
if (isset($_POST["employee_id"])) {  
    $output = '';  
    include 'connect_db.php';  
    
    $query = "SELECT * FROM tbl_employee WHERE id = ?";  
    $stmt = $conn->prepare($query);
    $stmt->bind_param('i', $_POST["employee_id"]);
    $stmt->execute();
    
    $result = $stmt->get_result();  
    $output .= '  
    <div class="table-responsive">  
        <table class="table table-striped table-hover">';  
    
    while ($row = $result->fetch_assoc()) {  
        $output .= '  
            <tr>  
                <td><label>Name</label></td>  
                <td>' . htmlspecialchars($row["name"]) . '</td>  
            </tr>  
            <tr>  
                <td><label>Address</label></td>  
                <td>' . htmlspecialchars($row["address"]) . '</td>  
            </tr>  
            <tr>  
                <td><label>Gender</label></td>  
                <td>' . htmlspecialchars($row["gender"]) . '</td>  
            </tr>  
            <tr>  
                <td><label>Designation</label></td>  
                <td>' . htmlspecialchars($row["designation"]) . '</td>  
            </tr>  
            <tr>  
                <td><label>Age</label></td>  
                <td>' . htmlspecialchars($row["age"]) . ' Year</td>  
            </tr>';  
    }  
    $output .= '  
        </table>  
    </div>';  
    echo $output;  
}  
?>

Step 4: javascript.js

$(document).ready(function() {
    // Activate tooltip
    $('[data-toggle="tooltip"]').tooltip();

    // Select/Deselect checkboxes
    var checkbox = $('table tbody input[type="checkbox"]');
    $("#selectAll").click(function() {
        if (this.checked) {
            checkbox.each(function() {
                this.checked = true;                        
            });
        } else {
            checkbox.each(function() {
                this.checked = false;                        
            });
        } 
    });

    checkbox.click(function() {
        if (!this.checked) {
            $("#selectAll").prop("checked", false);
        }
    });
});

Step 5: styles.css

/* Styles remain unchanged */
body {
    color: #566787;
    background: #f5f5f5;
    font-family: 'Varela Round', sans-serif;
    font-size: 13px;
}

.table-wrapper {
    background: #fff;
    padding: 20px 25px;
    margin: 30px 0;
    border-radius: 1px;
    box-shadow: 0 1px 1px rgba(0, 0, 0, 0.247);
}

.table-title {
    padding-bottom: 15px;
    background: linear-gradient(40deg, #2096ff, #05ffa3) !important;
    color: #fff;
    padding: 16px 30px;
    margin: -20px -25px 10px;
    border-radius: 1px 1px 0 0;
    box-shadow: 0 1px 1px rgba(0, 0, 0, 0.247);
}

.table-title h2 {
    margin: 5px 0 0;
    font-size: 24px;
}

/* Additional styles omitted for brevity */

Summary of Improvements:

1. SQL Injection Prevention: Prepared statements ($stmt->bind_param) have been used to protect against SQL injection.

2. XSS Protection: The htmlspecialchars() function is applied when displaying user input to prevent cross-site scripting (XSS).

3. Error Handling: Error messages are displayed in case the database query fails.