Get a nice datatable with records but features don't work- Record limit, sorting, search

CJWatson · August 2016

<head>
<!--    Nav bar -->
<link href="css/navbar-fixed-top.css" rel="stylesheet" type="text/css" >
    <!-- Bootstrap core CSS -->
<link href="css/bootstrap.min.css" rel="stylesheet">
<!-- Datatables -->
<link rel="stylesheet" type="text/css" href="dataTables.css"/>
<link rel="stylesheet" type="text/css" href="DataTables-1.10.12/css/jquery.dataTables.css"/>
<link rel="stylesheet" type="text/css" href="Buttons-1.2.1/css/buttons.dataTables.css"/>
<link rel="stylesheet" type="text/css" href="FixedHeader-3.1.2/css/fixedHeader.dataTables.css"/>
<link rel="stylesheet" type="text/css" href="Responsive-2.1.0/css/responsive.dataTables.css"/>
<link rel="stylesheet" type="text/css" href="Select-1.2.0/css/select.dataTables.css"/>
        
</head> 
    
<body>      
<script type="text/javascript" src="jQuery-2.2.3/jquery-2.2.3.js"></script>
<script>window.jQuery || document.write('<script src="jQuery-2.2.3/jquery-2.2.3.min.js"><\/script>')</script>
<!-- Dropdown -->
<script type="text/javascript" src="js/dropdown.js"></script>
<script type="text/javascript" src="js/bootstrap-Dropdown.js"></script>
<script>
$(document).ready(function() {
    $(".dropdown-toggle").dropdown('toggle');
}); 
</script>
<!-- DataTables -->
<script type="text/javascript" src="datatables.js"></script>
<!-- <script type="text/javascript" src="datatables.min.js"></script> -->
<!-- <script type="text/javascript" src="js/datatables.bootstrap.min.js"></script> -->


<!--  Data Tables Extras -->
<!--
<script type="text/javascript" src="pdfmake-0.1.18/build/pdfmake.js"></script>
<script type="text/javascript" src="pdfmake-0.1.18/build/vfs_fonts.js"></script>
<script type="text/javascript" src="DataTables-1.10.12/js/jquery.dataTables.js"></script>
<script type="text/javascript" src="Buttons-1.2.1/js/dataTables.buttons.js"></script>
<script type="text/javascript" src="Buttons-1.2.1/js/buttons.colVis.js"></script>
<script type="text/javascript" src="Buttons-1.2.1/js/buttons.html5.js"></script>
<script type="text/javascript" src="Buttons-1.2.1/js/buttons.print.js"></script>
<script type="text/javascript" src="FixedHeader-3.1.2/js/dataTables.fixedHeader.js"></script>
<script type="text/javascript" src="Responsive-2.1.0/js/dataTables.responsive.js"></script>
<script type="text/javascript" src="Select-1.2.0/js/dataTables.select.js"></script>
-->

<!-- Datatables -->
<script>
$(document).ready(function() {
    var table = $('#example').DataTable();
 });
 </script> 
 <script>
 $('#example').dataTable( {
  "lengthMenu": [ [10, 25, 50, -1], [10, 25, 50, "All"] ]
} );
</script>
<script>
 $('#example').dataTable( {
  "pageLength": 10
} );
</script>
<script>
    $('#example').dataTable( {
  "ordering": false
} );
</script>

Below here is the connect to mysql with a search that puts the records in a table.
Get a nice datatables list but sorting, record limiting and search does not work

allan · August 2016

Thanks for your question - however, per the forum rules can you link to a test case showing the issue please. This will allow the issue to be debugged.

Information on how to create a test page, if you can't provide a link to your own page can be found here.

Thanks,
Allan

F12Magic · August 2016

Hi CJWatson,

Load all the javascript files in the head tag of the page OR at the bottom of the body tag.
For the datatables initialization, don't separate all the options. Do it once like this:

$(document).ready(function() {
    $('#example').dataTable( {
        "lengthMenu": [ [10, 25, 50, -1], [10, 25, 50, "All"] ],
        "pageLength": 10,
        "ordering": false
    });
});

CJWatson · August 2016

As per suggestions..
1. Datatables live is offline - so I couldn't post a test page
2. Put java script at bottom of body - No difference. Still get Nice layout but search, record limit and sort still don't work.
3. Simplified code with just basic initialization

<!DOCTYPE html>
<html lang="en">

<head>
<!--    Nav bar -->
<link href="css/navbar-fixed-top.css" rel="stylesheet" type="text/css" >
    <!-- Bootstrap core CSS -->
<link href="css/bootstrap.min.css" rel="stylesheet">
<!-- Datatables -->
<link rel="stylesheet" type="text/css" href="DataTables-1.10.12/css/jquery.dataTables.css"/>

<script type="text/javascript" src="jQuery-2.2.3/jquery-2.2.3.js"></script>
<script>window.jQuery || document.write('<script src="jQuery-2.2.3/jquery-2.2.3.min.js"><\/script>')</script>
<!-- Dropdown -->
<script type="text/javascript" src="js/dropdown.js"></script>
<script type="text/javascript" src="js/bootstrap-Dropdown.js"></script>

<!-- DataTables -->
<script type="text/javascript" src="DataTables-1.10.12/js/jquery.dataTables.js"></script>


</head> 
    
<body>  

     <!-- Fixed navbar -->
<nav class="navbar navbar-default navbar-fixed-top">
      <div class="container"><!--/.nav-containter -->
        <div class="navbar-header">
          <button type="button" class="navbar-toggle collapsed" data-toggle="collapse" data-target="#navbar" aria-                                                  expanded="false" aria-controls="navbar">
            <span class="sr-only">Toggle navigation</span>
            <span class="icon-bar"></span>
            <span class="icon-bar"></span>
            <span class="icon-bar"></span>
          </button>
          
        <a class="navbar-brand" href="#">Golf Hui</a>
        </div><!--/.nav-header -->
        
        <div id="navbar" class="navbar-collapse collapse">

         <ul class="nav navbar-nav">

            <li class="active"><a href="Opening_Page.php">Home</a></li>
           <li><a href="#about">About</a></li>
           <li><a href="#contact">Contact</a></li>
 
<!--            The dropdown menu -->

            <li class="dropdown">
      <a href="#" class="dropdown-toggle" data-toggle="dropdown" role="button" aria-        haspopup="true" 
          aria-expanded="true">Actions<span class="caret"></span></a>

              <ul class="dropdown-menu">
                <li><a href="#">Action</a></li>
                <li><a href="#">Another action</a></li>
                <li><a href="#">Something else here</a></li>
                <li role="separator" class="divider"></li>
<!--
                <li class="dropdown-header"></li>
                <li><a href="#">Separated link</a></li>
                <li><a href="#">One more separated link</a></li>
-->
              </ul>
              
            </li>
            
          </ul>

    </div><!--/.nav-collapse -->
  </div><!--/.nav-containter -->
</nav>

<?php

$servername = "localhost:3306";
$username = "root";
$password = "root";
$DbName = "Gof_Hui_data";

// Create connection
$conn =   new mysqli($servername, $username, $password);

// Check connection
if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
} 

$sql = "SELECT Members_Key_p , Name_first, Name_Last , Index_Club FROM Golf_Hui_Data.Members WHERE Active_Member = 1 ORDER BY Name_Last desc";
$result = mysqli_query($conn,$sql)  ;                 //$conn->query($sql);
if(mysqli_num_rows($result)) {
        
$Numofrecords = mysqli_num_rows($result);

echo '<table id="example" 
class="table table-bordered table-hover table-condensed " 
 >';

        echo'<thead>';
           echo'<tr>';
                echo'<th>View</th>';
                echo'<th>First Name</th>';
                echo'<th>Last Name</th>';
                echo'<th>Club Index</th>';
           echo' </tr>';
       echo' </thead>';
//while($row = $result->fetch_assoc()) {
while($row = mysqli_fetch_row($result)){
        $id = $row[0];
        echo'<tbody>';
           echo' <tr>';
               echo' <td><a href="Members_Input.php?$id='.$row[0].'">View</a></td>';
               echo '<td>'.$row[1].'</td>';    
               echo '<td>'.$row[2].'</td>';   
               echo '<td>'.$row[3].'</td>';

           echo'</tr>';
           
      echo'</tbody>';
      }
    echo'</table>';
    }

<?php
>
?>


<script>
$(document).ready(function() {
    $(".dropdown-toggle").dropdown('toggle');
}); 
</script>
<script>
$(document).ready(function() {
    $('#example').DataTable();
} );
</script>   

</body>
</html>

Edited by Allan - Syntax highlighting. Details on how to highlight code using markdown can be found in this guide.

allan · August 2016

The live site is running just now. Or you could use JSFiddle, JSBin or CodePen.

The one thing that stands out for me in the code above is that your tbody is in the while loop. So you have a tbody for every row. That isn't something DataTables currently supports - just a single tbody. Move it outside the while.

Allan

CJWatson · August 2016

Thanks All for your comments.! Allan the tbody tag was the issue. Moved it outside the while statement and all is well.
Mahalo for looking at the code figuring it out.
CJ Watson

Get a nice datatable with records but features don't work- Record limit, sorting, search

Get a nice datatable with records but features don't work- Record limit, sorting, search

Answers

Howdy, Stranger!

Categories

DataTables

Get a nice datatable with records but features don't work- Record limit, sorting, search

Get a nice datatable with records but features don't work- Record limit, sorting, search

Answers

Howdy, Stranger!

Quick Links

Categories

DataTables