Search and Pagination Functions not working in DataTable when using Dynamic Data

Question

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/bootstrap@5.0.2/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.

Answer 1

In order to use DataTables, you need to inizialize one

$(document).ready( function () {
   $('#Data_Table').DataTable();
});

This needs to be wrapped in a script tag

I don't see it anywhere in your code

Plus, you missed a td closing tag in Line 18

Here's a working example: jsfiddle.net

Source: datatables.net