I made a table and I have been using Datatable CDN to have some functions such as search and the ability to select table pages. After following the steps on DataTables my code load my table with the search bar and pages, however all the functions are not working. Can someone tell me where I did a mistake please:
Using static data makes the dataTable work as it is intended with the search option and pagination working and showing the results that are in the table. However when using dynamic data with MySQL database the search and pagination functions stops working. Below is my code with fetch the data from table employees from my DB:
<?php
global $db;
$sql = "SELECT id, name, role, competency FROM employees";
$result = mysqli_query($db, $sql);
?>
This query is found just before my table in my html code, all the data are displayed correctly and styling of dataTable are displayed also, however the functions for search and pagination are not working. The search returns No Match result however the record is present in the table. Can someone please tell me the error please.
<table id="Data_Table" width="100%">
<thead>
<tr>
<td>ID</td>
<td>Employee Name</td>
<td>Job Role</td>
<td>Competency Level</td>
<td>Action</td>
</tr>
</thead>
<?php
if (mysqli_num_rows($result) > 0) {
foreach($result as $row) {
?>
<tbody>
<tr>
<form method="post">
<td><?php echo $row['id']; ?></td>
<td><?php echo $row['name']; ?></td>
<td><?php echo $row['role']; ?></td>
<td><?php echo $row['competency']; ?></td>
<td>
<button type="button" class="view_emp" title="View Record">
<span class="la la-eye"></span> View
</button>
<button type="button" class="edit_emp" title="Update Record">
<span class="la la-edit"></span>Update
</button>
<input type="checkbox" name="keyToDelete" value="<?php echo $row['id']; ?>" required>
<button type="submit" name="delete_employee" class="delete_employee" title="Delete Record"><span class="la la-trash"></span>Delete
</button>
</td>
</form>
</tr>
</tbody>
<?php
}
}
else {
echo "No Employee Record could be found";
}
$db-> close();
?>
</table>
In my code I have also included the script that would use DataTables as follows:
<script src="https://code.jquery.com/jquery-3.6.0.slim.js" integrity="sha256-HwWONEZrpuoh951cQD1ov2HUK5zA5DwJ1DNUXaM6FsY=" crossorigin="anonymous"></script>
<script src="https://cdnjs.cloudflare.com/ajax/libs/popper.js/2.9.2/umd/popper.min.js"></script>
<script src="https://cdn.jsdelivr.net/npm/[email protected]/dist/js/bootstrap.bundle.min.js" integrity="sha384-MrcW6ZMFYlzcLA8Nl+NtUVF0sA7MsXsP1UyJoMp4YLEuNSfAP+JcXn/tWtIaxVXM" crossorigin="anonymous"></script>
<script src="https://cdn.datatables.net/1.10.25/js/jquery.dataTables.min.js"></script>
<script src="https://cdn.datatables.net/1.10.25/js/dataTables.bootstrap5.min.js"></script>
and the style link for the datatable
<link rel="stylesheet" href="https://cdn.datatables.net/1.10.25/css/dataTables.bootstrap5.min.css">
All the style are working as intended but the function such as search and pagination are not working can someone tell me what i made wrong here
I defined the DataTables in my script as follows:
<script>
$(document).ready(function(){
$('#Data_Table').DataTable();
});
</script>
There are no error in the console log of the browser the function search and pagination simply wont work, search option will search and return results as No match with records when there is a record and $results are the data fetched from my database employees eg: id: 1 ,name: Bob ,role: Web Designer ,Competency: Beginner.
<script>...</script>tag where your DataTable is defined; (b) some sample data from$result; (c) any error messages from your browser's console.