I want to fill my table with data from my localhost database, which i managed to do. when i delete an employee, i would like to retrieve their first and last name based on their id. unfortunately, i am not sure where my code is going wrong as the same code works for other queries.
I am a student and practising so apologies if my code is not up to code. I’m learning by myself and exploring php at the moment.
PHP scipt:
<?php
ini_set('display_errors', 'On');
error_reporting(E_ALL);
$executionStartTime = microtime(true);
include("config.php");
header('Content-Type: application/json; charset=UTF-8');
$conn = new mysqli($cd_host, $cd_user, $cd_password, $cd_dbname, $cd_port, $cd_socket);
if (mysqli_connect_errno()) {
$output['status']['code'] = "300";
$output['status']['name'] = "failure";
$output['status']['description'] = "database unavailable";
$output['status']['returnedIn'] = (microtime(true) - $executionStartTime) / 1000 . " ms";
$output['data'] = [];
mysqli_close($conn);
echo json_encode($output);
exit;
}
// First query to retrieve personnel data based on ID
$query = $conn->prepare('SELECT `id`, `firstName`, `lastName`, `email`, `departmentID` FROM personnel WHERE `id` = ?');
$query->bind_param("i", $_POST['id']);
$query->execute();
if ($query === false) {
$output['status']['code'] = "400";
$output['status']['name'] = "executed";
$output['status']['description'] = "query failed";
$output['data'] = [];
mysqli_close($conn);
echo json_encode($output);
exit;
}
$result = $query->get_result();
$personnel = [];
while ($row = mysqli_fetch_assoc($result)) {
array_push($personnel, $row);
}
if (empty($personnel)) {
$output['status']['code'] = "404";
$output['status']['name'] = "not found";
$output['status']['description'] = "no personnel found with the provided ID";
$output['data'] = [];
mysqli_close($conn);
echo json_encode($output);
exit;
}
// Second query to retrieve department data
$query = 'SELECT id, name from department ORDER BY name';
$result = $conn->query($query);
if (!$result) {
$output['status']['code'] = "400";
$output['status']['name'] = "executed";
$output['status']['description'] = "query failed";
$output['data'] = [];
mysqli_close($conn);
echo json_encode($output);
exit;
}
$department = [];
while ($row = mysqli_fetch_assoc($result)) {
array_push($department, $row);
}
$output['status']['code'] = "200";
$output['status']['name'] = "ok";
$output['status']['description'] = "success";
$output['status']['returnedIn'] = (microtime(true) - $executionStartTime) / 1000 . " ms";
$output['data']['personnel'] = $personnel;
$output['data']['department'] = $department;
mysqli_close($conn);
echo json_encode($output);
?>
JS Script:
let addPersonnel = function (selectVal, departments) {
let perLocObj = $.grep(departments, function (department, i) {
return department.department.toLowerCase() === selectVal.toLowerCase();
});
perLocInput = perLocObj[0].location;
$("#locationInput").val(perLocInput);
};
let fillTable = (data) => {
$("#personnelTableBody").empty();
for (let i = 0; i < data.length; i++) {
let row = $(`<tr id=employeeRow'${[i]}'>`);
// console.log(data[0]);
//Name
let nameTableCell = $("<td>")
.addClass("align-middle text-nowrap")
.html(`${data[i].firstName}, ${data[i].lastName}`);
row.append(nameTableCell);
//Job Title
let jobTableCell = $("<td>")
.addClass("align-middle text-nowrap d-none d-sm-table-cell")
.html(data[i].jobTitle)
.addClass("text-start mb-0");
row.append(jobTableCell);
//Department
let departmentCell = $("<td>")
.addClass("align-middle text-nowrap d-none d-md-table-cell")
.html(data[i].department);
row.append(departmentCell);
//Location
let locationsCell = $("<td>")
.addClass("align-middle text-nowrap d-none d-lg-table-cell")
.html(data[i].location);
row.append(locationsCell);
//Email
let emails = $("<td>")
.addClass("align-middle text-nowrap d-none d-xl-table-cell")
.html(data[i].email);
row.append(emails);
// -- BUTTONS -- //
let buttonCells = $("<td>").addClass("align-middle text-end text-nowrap");
let editButtons = $("<button>")
.addClass("btn btn-primary btn-sm m-1")
.attr("data-bs-target", "#editEmpModal")
.attr("data-id", data[i].id)
.attr("data-bs-toggle", "modal");
let editIcons = $("<i>").addClass("fa-solid fa-pencil fa-fw");
let deleteButtons = $("<button>")
.addClass("btn btn-primary btn-sm")
.attr("data-bs-target", "#deleteEmpModal")
.attr("data-id", data[i].id)
.attr("data-bs-toggle", "modal");
let deleteIcons = $("<i>").addClass("fa-solid fa-trash fa-fw");
editButtons.append(editIcons);
deleteButtons.append(deleteIcons);
buttonCells.append(editButtons);
buttonCells.append(deleteButtons);
row.append(buttonCells);
$("#personnelTableBody").append(row);
}
$("#empNumber").html(`${data.length} Employees`);
};
// Personnal Delete Tab
// -- Delete Employee Modal Shows -- //
$("#deleteEmpModal").on("show.bs.modal", function (e) {
$.ajax({
url: "libs/php/getPersonnelByID.php",
type: "POST",
dataType: "json",
data: {
id: $(e.relatedTarget).attr("data-id"), // Retrieves the data-id attribute from the calling button
},
success: function (result) {
if (result.status.code == 200) {
console.log(result);
personnelId = result.data.personnel[0].id;
$("#deleteEmpMessage").html(
`Are you sure you want to delete <b>${result.data.personnel[0].firstName} ${result.data.personnel[0].lastName}<b>?`
);
} else {
$("#deleteEmpModal .modal-title").replaceWith("Error retrieving data");
}
},
error: function (jqXHR, textStatus, errorThrown) {
$("#deleteEmpModal .modal-title").replaceWith(
"Error cannot retrieving data"
);
},
});
});
// -- Delete Employee Confirm Routine -- //
$("#confirmdelEmp").on("click", function (e) {
$.ajax({
url: "libs/php/deleteEmployee.php",
type: "POST",
dataType: "json",
data: {
id: personnelId,
},
success: function (result) {
if (result.status.code == 200) {
fillTable(result.data);
$("#deleteEmpModal").modal("hide");
delEmpToast.show();
} else {
console.log("failed");
}
},
error: function (jqXHR, textStatus, errorThrown) {},
});
});
$("#cancelEditEmpBtn").on("click", function () {
$("#editEmpTitle").html("");
$("#editFirstName").val("");
$("#editLastName").val("");
$("#editEmail").val("");
$("#editJob").val("");
});
