I'll create a well-structured codebase with:
-
A secure database schema
-
Proper database connection handling
-
Sanitized SQL queries (using prepared statements)
-
Modern HTML and JavaScript enhancements
1. database.sql (Improved Table Structure)
CREATE TABLE `tbl_employee` (
`id` INT AUTO_INCREMENT PRIMARY KEY,
`name` VARCHAR(50) NOT NULL,
`address` TEXT NOT NULL,
`gender` ENUM('Male', 'Female') NOT NULL,
`designation` VARCHAR(100) NOT NULL,
`age` INT NOT NULL,
`image` VARCHAR(255) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
2. config.php (Database Connection)
<?php
$servername = 'localhost';
$username = 'root';
$password = '';
$dbname = 'employee';
$conn = new mysqli($servername, $username, $password, $dbname);
if ($conn->connect_error) {
die("Connection failed: " . $conn->connect_error);
}
?>
3. fetch.php (Fetching Employee Details)
<?php
include 'config.php';
if (isset($_POST["employee_id"])) {
$stmt = $conn->prepare("SELECT * FROM tbl_employee WHERE id = ?");
$stmt->bind_param("i", $_POST["employee_id"]);
$stmt->execute();
$result = $stmt->get_result();
$row = $result->fetch_assoc();
echo json_encode($row);
}
?>
4. index.php (Main Employee Listing with Bootstrap)
<?php
include 'config.php';
$query = "SELECT * FROM tbl_employee ORDER BY id DESC";
$result = $conn->query($query);
?>
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8">
<title>Employee Management</title>
<link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/4.5.2/css/bootstrap.min.css">
<script src="https://code.jquery.com/jquery-3.6.0.min.js"></script>
</head>
<body>
<div class="container mt-4">
<h2 class="text-center">Manage Employees</h2>
<button class="btn btn-success mb-3" data-toggle="modal" data-target="#addEmployeeModal">Add Employee</button>
<table class="table table-bordered">
<thead>
<tr>
<th>Name</th>
<th>Address</th>
<th>Gender</th>
<th>Designation</th>
<th>Age</th>
<th>Actions</th>
</tr>
</thead>
<tbody>
<?php while ($row = $result->fetch_assoc()) { ?>
<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>
<button class="btn btn-info edit_data" data-id="<?= $row["id"] ?>">Edit</button>
<button class="btn btn-danger delete_data" data-id="<?= $row["id"] ?>">Delete</button>
</td>
</tr>
<?php } ?>
</tbody>
</table>
</div>
<!-- Add Employee Modal -->
<div class="modal fade" id="addEmployeeModal">
<div class="modal-dialog">
<div class="modal-content">
<form id="employeeForm">
<div class="modal-header">
<h4 class="modal-title">Add/Edit Employee</h4>
<button type="button" class="close" data-dismiss="modal">×</button>
</div>
<div class="modal-body">
<input type="hidden" name="employee_id" id="employee_id">
<div class="form-group">
<label>Name</label>
<input type="text" name="name" id="name" class="form-control" required>
</div>
<div class="form-group">
<label>Address</label>
<textarea name="address" id="address" class="form-control" required></textarea>
</div>
<div class="form-group">
<label>Gender</label>
<select name="gender" id="gender" class="form-control">
<option value="Male">Male</option>
<option value="Female">Female</option>
</select>
</div>
<div class="form-group">
<label>Designation</label>
<input type="text" name="designation" id="designation" class="form-control" required>
</div>
<div class="form-group">
<label>Age</label>
<input type="number" name="age" id="age" class="form-control" required>
</div>
</div>
<div class="modal-footer">
<button type="submit" class="btn btn-success">Save</button>
<button type="button" class="btn btn-default" data-dismiss="modal">Close</button>
</div>
</form>
</div>
</div>
</div>
<script src="scripts.js"></script>
</body>
</html>
5. insert.php (Insert or Update Employee)
<?php
include 'config.php';
if ($_SERVER["REQUEST_METHOD"] === "POST") {
$id = $_POST["employee_id"];
$name = $_POST["name"];
$address = $_POST["address"];
$gender = $_POST["gender"];
$designation = $_POST["designation"];
$age = $_POST["age"];
if ($id) {
$stmt = $conn->prepare("UPDATE tbl_employee SET name=?, address=?, gender=?, designation=?, age=? WHERE id=?");
$stmt->bind_param("ssssii", $name, $address, $gender, $designation, $age, $id);
} else {
$stmt = $conn->prepare("INSERT INTO tbl_employee (name, address, gender, designation, age) VALUES (?, ?, ?, ?, ?)");
$stmt->bind_param("ssssi", $name, $address, $gender, $designation, $age);
}
if ($stmt->execute()) {
echo "Success";
} else {
echo "Error";
}
}
?>
6. delete.php (Delete Employee)
<?php
include 'config.php';
if (isset($_POST["employee_id"])) {
$stmt = $conn->prepare("DELETE FROM tbl_employee WHERE id = ?");
$stmt->bind_param("i", $_POST["employee_id"]);
if ($stmt->execute()) {
echo "Deleted";
} else {
echo "Error";
}
}
?>
7. scripts.js (AJAX Handling)
$(document).ready(function () {
$(".edit_data").click(function () {
var id = $(this).data("id");
$.post("fetch.php", { employee_id: id }, function (data) {
var employee = JSON.parse(data);
$("#employee_id").val(employee.id);
$("#name").val(employee.name);
$("#address").val(employee.address);
$("#gender").val(employee.gender);
$("#designation").val(employee.designation);
$("#age").val(employee.age);
$("#addEmployeeModal").modal("show");
});
});
$("#employeeForm").submit(function (event) {
event.preventDefault();
$.post("insert.php", $(this).serialize(), function (response) {
alert(response);
location.reload();
});
});
$(".delete_data").click(function () {
if (confirm("Are you sure?")) {
var id = $(this).data("id");
$.post("delete.php", { employee_id: id }, function (response) {
alert(response);
location.reload();
});
}
});
});
Final Thoughts
This improved version ensures:
-
Security (SQL Injection Prevention)
-
Better Structure
-
Efficient Queries
-
Bootstrap 4 for Modern UI
