How to get dataTable working with PHP

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

Not that this answers your question, but you should separate your logic from your presentation. Put all your logic at the top of the file and then just do a basic loop in your HTML. Trying to combine everything and your code is going to be a complete mess really fast. — Mike, Feb 07 '17 at 22:32
Have you checked your JS console for errors? This question really has nothing to do with PHP or MySQL. — Mike, Feb 07 '17 at 22:32
@Mike How would you have me separate the logic from the presentation? Can you please give me a little bit example on how to do that? I have updated my post to show the error I'm receiving for the JS console. — Nathan Siafa, Feb 08 '17 at 05:51
Basically move your mysql stuff to before the HTML and save the result as an array. Then in your HTML just do `foreach ($result_array as $result)`. — Mike, Feb 08 '17 at 07:10

score 1 · Accepted Answer · edited May 23 '17 at 11:53

1

You're repeating tbody with each iteration. You should echo rows only not the tbody. Move it out of while loop.
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.

edited May 23 '17 at 11:53

Community

1
1

answered Feb 08 '17 at 06:09

Waleed

1,097
18
45

I have placed outside the loop but that doesn't seem to be the problem. That was smart about th != to td. The problem was with the three operations column in the table pic I posted far right. Do you have ideas on how I can get everything working togerther? – Nathan Siafa Feb 08 '17 at 06:28
works like bingo!!!!!!! Thanks very much but is there a way that I can remove the sort on the operations column? – Nathan Siafa Feb 08 '17 at 10:01
Yes, have a look: http://stackoverflow.com/questions/16335928/how-to-remove-sorting-option-from-datatables – Waleed Feb 08 '17 at 10:24

score 0 · Answer 2 · answered Feb 07 '17 at 22:37

0

You are repeating your <tbody> tag with each iteration of your loop. Move this outside of your loop (along with the closing </tbody>) so there is only one instance of them in your table.

answered Feb 07 '17 at 22:37

Andy

698
12
22

@Ancy C thanks. That got the stripes to appear on the table, but didn't solve the problem. – Nathan Siafa Feb 08 '17 at 05:52

Zeeshan Ch · Answer 3 · 2018-12-10T23:11:40.490

Your Number of td and th Tags must be same for dataTables to work. So just add some empty th tags.. AND DONT USE COLSPAN while using datatables

In your case you need to add 2 extra th tags...

     `<!-- 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>Operations</th> // DONT USE COLSPAN WHILE USING DATATABLES
            <th></th>
            <th></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> `

Please provide example code to fix the example provided by the asker of the question rather than just text. — Loren, Dec 31 '17 at 15:16

How to get dataTable working with PHP

3 Answers3