Using datatable for existing html table (using database connection) in wordpress

Question

I currently have a WordPress site with a section that has a main HTML table which has a basic header of 7 columns and the rows are all filled from database variable. It works perfectly now, where if I add 8 more records to the database, it pulls those and displays them in the table with no problem.

I've just been asked to make this full table sortable, filterable and searchable. I figured datatables would be the best and simplest way, but I've never used it in a WordPress site so I don't know where I've misstepped. After adding the code for the datatable, the page loads without errors but it's the same HTML table and none of the datatable functions are taking place.

Here's the full code:

$server = "localhost";
$user = "//omitted";
$pw = "//omitted";
$db = "//omitted";

$connect = mysqli_connect($server, $user, $pw, $db);

if ($connect->connect_error) {
  die("Connection failed: " . $conn->connect_error);
} else {
  //echo'success!';
}

$query1 = "SELECT * FROM staging;";
$result1 = mysqli_query($connect,$query1);
?>

<html>
<head>
  <title>Dashboard</title>
  <link rel="stylesheet" type="text/css" href="https://cdn.datatables.net/1.10.15/css/jquery.dataTables.min.css" />

</head>
<body>

<div class="dashboardTable" style="overflow-x:auto; width:900px;">
  <table id="mytable" style="border: 1px solid #468BBD; border-collapse: collapse; width:100%; margin:0 auto;">
    <thead>
    <tr style="border: 1px solid #468BBD;">
      <th style="padding-left: 10px; padding-right:10px; border: 1px solid #468BBD;">Service Preformed</th>
      <th style="padding-left: 10px; padding-right:10px; border: 1px solid #468BBD;">Work Order Number</th>
      <th style="padding-left: 10px; padding-right:10px; border: 1px solid #468BBD;">Date</th>
      <th style="padding-left: 10px; padding-right:10px; border: 1px solid #468BBD;">Utility</th>
      <th style="padding-left: 10px; padding-right:10px; border: 1px solid #468BBD;">Service Name</th>
      <th style="padding-left: 10px; padding-right:10px; border: 1px solid #468BBD;">Address</th>
      <th style="padding-left: 10px; padding-right:10px; border: 1px solid #468BBD;">Serial No.</th>
    </tr>
    </thead>

    <tbody>
    <?php
    while($row = mysqli_fetch_array($result1)){
      ?>

      <tr>
        <td style="border-collapse: collapse; padding-left: 10px; padding-right:10px;"><? echo $row['workOrderType2'];?>&nbsp;</td>
        <td style="border-collapse: collapse; padding-left: 10px; padding-right:10px;"><? echo $row['workOrderNum'];?>&nbsp;</td>
        <td style="border-collapse: collapse; padding-left: 10px; padding-right:10px;"><? echo $row['date'];?>&nbsp;</td>
        <td style="border-collapse: collapse; padding-left: 10px; padding-right:10px;"><? echo $row['utility'];?>&nbsp;</td>
        <td style="border-collapse: collapse; padding-left: 10px; padding-right:10px;"><? echo $row['serviceName'];?>&nbsp;</td>
        <td style="border-collapse: collapse; padding-left: 10px; padding-right:10px;"><? echo $row['address'];?>&nbsp;</td>
        <td style="border-collapse: collapse; padding-left: 10px; padding-right:10px;"><?php echo '<a href="/dashboard-display?id='.$row['serialNumber'].'">'.$row['serialNumber'].'</a>'; ?>   </td>
      </tr>
    <?}?>
    </tbody>
  </table>
</div>

<script type="text/javascript"   src="https://cdn.datatables.net/1.10.15/js/jquery.dataTables.min.js">
</script>
<script type="text/javascript">
  $(document).ready( function () {
    $('#mytable').DataTable();
  } );
</script>

I'm not very familiar with the datatables plugin but I feel like the code is correct there, I'm just wondering if the wordpress factor is the issue. Is there a reason the code isn't breaking my page but it's not displaying the datatable?

UPDATE:

Photo of sidescroll issue with more columns

You are repeating <tbody> which causes malformed markup. the lioop should only repeat <tr>...</tr> ... — davidkonrad
– davidkonrad, Commented May 18, 2017 at 15:53
Wait, just to make sure I understand: I should start <tbody> above the while loop? — Geoff_S
– Geoff_S, Commented May 18, 2017 at 16:17
Ok I made that change and updated my code above, but it still just shows the plain html table — Geoff_S
– Geoff_S, Commented May 18, 2017 at 16:21

StudioTime · Accepted Answer · 2017-05-18 16:28:14Z

1

You need to define jquery to use $ when using wordpress, try this:

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

There are multiple ways you can do this, another is:

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

answered May 18, 2017 at 16:28

StudioTime

24.4k40 gold badges129 silver badges217 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Geoff_S Over a year ago

Oh man, I should have known better! I didn't even think about this. Thank you so much, it's working perfectly now!

Geoff_S Over a year ago

Hey @Darren, Quick question. Is there a way to contain this in more of an Iframe style? It worked but someone requested me to try this with about 20 columns, instead of the original 7, so now the sidescroll for the columns also scrolls away from the search box, pagination and number of entries to show. I updated with a photo of the issue when I start side scrolling.

StudioTime Over a year ago

You mean like this? $('#mytable').DataTable({ "scrollX": true });

Geoff_S Over a year ago

Yes sir, Just like that. I tried wrapping it in multiple DIVs and everything but nothing would work. Thanks again!

StudioTime Over a year ago

Yeah it can be, do a search for datatables redraw

|

Collectives™ on Stack Overflow

Using datatable for existing html table (using database connection) in wordpress

1 Answer 1

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related