如何使用PHP处理dataTable

Question

I want to have my table beautifully display with sorting options. I'm using PHP to retrieve records from a MySQL database. I learn of datatables and saw that they are pretty useful for such purpose.

Now, the problem is whenever I use PHP to generate data from the database and dynamically display them in a table it works perfectly with all the datatables styles applying to the table, but I can't get the sorting and pagination features of dataTables to work. Here is how my table displays:

How do I enable the sorting and pagination features that dataTables provides? Here are the scripts to dataTables and the php code I wrote:

<!-- DataTables CSS -->
<link href="vendor/datatables-plugins/dataTables.bootstrap.css" rel="stylesheet">

<!-- DataTables Responsive CSS -->
<link href="vendor/datatables-responsive/dataTables.responsive.css" rel="stylesheet">


<table class="table table-striped table-bordered table-hover" id="example">
    <thead>
        <tr>
            <th>First Name</th>
            <th>Surname</th>
            <th>Gender</th>
            <th>Birth Date</th>
            <th>Address</th>
            <th>Nationality</th>
            <th>County</th>
            <th>Student Type</th>
            <th>Class</th>
            <th colspan="3">Operations</th>
        </tr>
    </thead>
    <tbody>
    <?php 
       $query = "SELECT student_id, first_name, cell_number, middle_name,   surname, gender, date_of_birth, address, nationality, county, student_type, class_name 
            from students
            INNER JOIN classes
                ON students.class_id = classes.class_id";

       if($result = mysqli_query($connection, $query)){
          if(mysqli_num_rows($result) > 0){
            while ($row = mysqli_fetch_array($result)){
   ?>
    <tr>
        <td><?php echo htmlentities($row['first_name']) ?></td>
        <td><?php echo htmlentities($row['surname']) ?></td>
        <td><?php echo htmlentities($row['gender']) ?></td>
        <td><?php echo htmlentities($row['date_of_birth']) ?></td>
        <td><?php echo htmlentities($row['address']) ?></td>
        <td><?php echo htmlentities($row['nationality']) ?></td>
        <td><?php echo htmlentities($row['county'])?></td>
        <td><?php echo htmlentities($row['student_type'])?></td>
        <td><?php echo htmlentities($row['class_name'])?></td>

        <td align="center"><a class="page_anchor" href="edit_student.php?student=<?php echo urlencode($row['student_id']); ?>">Edit</a></td> 

        <td align="center"><a class="page_anchor" href="create_grades.php?student=<?php echo urlencode($row['student_id']); ?>">Grades</a></td> 

        <td align="center"><a class="page_anchor" href="student_details.php?student=<?php echo urlencode($row['student_id']); ?>">Details</a></td> 
    </tr>


            <!-- closing the while loop --> 
            <?php }?>
         </tbody>
        <!-- closing the if mysqli_num_rows if statement -->    
        <?php } else { echo "No record found"; }?>
    <!-- closing the if $result = mysqli_query($connection, sql) if statement -->   
    <?php } else {
        die("Database query failed. ". mysqli_error($connection));
    } ?>
</table>

<!-- jQuery -->
<script src="vendor/jquery/jquery.min.js"></script>

<!-- Bootstrap Core JavaScript -->
<script src="vendor/bootstrap/js/bootstrap.min.js"></script>

<script src="vendor/datatables/js/jquery.dataTables.min.js"></script>
 <script src="vendor/datatables-responsive/dataTables.responsive.js"></script>


<script>
$(document).ready(function() {
    $('#example').DataTable({
        responsive: true
    });
});
</script>

Here are the errors I'm receiving from the JS console:

Uncaught TypeError: Cannot read property 'mData' of undefined
 at HTMLTableCellElement.<anonymous> (jquery.dataTables.min.js:90)
 at Function.each (jquery.min.js:2)
 at r.fn.init.each (jquery.min.js:2)
 at HTMLTableElement.<anonymous> (jquery.dataTables.min.js:90)
 at Function.each (jquery.min.js:2)
 at r.fn.init.each (jquery.min.js:2)
 at r.fn.init.m [as dataTable] (jquery.dataTables.min.js:82)
 at r.fn.init.h.fn.DataTable (jquery.dataTables.min.js:166)
 at HTMLDocument.<anonymous> (index.php:429)
 at j (jquery.min.js:2)

Uncaught TypeError: Cannot read property 'defaults' of undefined
 at f (dataTables.bootstrap.min.js:5)
 at dataTables.bootstrap.min.js:8
 at dataTables.bootstrap.min.js:8

Here is a warning that I also saw in the JS console:

jQuery.Deferred exception: Cannot read property 'mData' of undefined TypeError: Cannot read property 'mData' of undefined
at HTMLTableCellElement.<anonymous> (http://localhost/SchoolMate/vendor/datatables/js/jquery.dataTables.min.js:90:236)
at Function.each (http://localhost/SchoolMate/vendor/jquery/jquery.min.js:2:2815)
at r.fn.init.each (http://localhost/SchoolMate/vendor/jquery/jquery.min.js:2:1003)
at HTMLTableElement.<anonymous> (http://localhost/SchoolMate/vendor/datatables/js/jquery.dataTables.min.js:90:192)
at Function.each (http://localhost/SchoolMate/vendor/jquery/jquery.min.js:2:2815)
at r.fn.init.each (http://localhost/SchoolMate/vendor/jquery/jquery.min.js:2:1003)
at r.fn.init.m [as dataTable] (http://localhost/SchoolMate/vendor/datatables/js/jquery.dataTables.min.js:82:388)
at r.fn.init.h.fn.DataTable (http://localhost/SchoolMate/vendor/datatables/js/jquery.dataTables.min.js:166:245)
at HTMLDocument.<anonymous> (http://localhost/SchoolMate/index.php:429:23)
at j (http://localhost/SchoolMate/vendor/jquery/jquery.min.js:2:29568) undefined

Answer 1

1. You're repeating tbody with each iteration. You should echo rows only not the tbody. Move it out of while loop.
2. You're showing more data columns in tbody than you have in thead i.e no of th != no of td

Edit:

Well, you can't achieve what you have shown since DataTables doesn't work like you want with colspan and rowspan. But you can do something like this:

<table class="jTable">
    <thead>
        <tr>
            <th>Name</th>
            <th>Operations</th>
        </tr>
    </thead>
    <tbody>
        <tr>
            <td>Waleed</td>
            <td>
                <table>
                    <tr>
                        <td>View</td>
                        <td>Edit</td>
                        <td>Delete</td>
                    </tr>
                </table>
            </td>
        </tr>
    </tbody>
</table>

Output:

However, rendering nested tables is not suggested due to slow performance. But this will do the job.

This may come handy as well.