Using a dropdown to show a particular datatable

Question

I have a page with a perfectly working datatable and I've just created a second table to display data from another database table, but they don't both need to be displayed on the same page at the same time. I've been tasked with creating a dropdown that shows values such as "Table 1" and "Table 2" so that when you visit the page it defaults to show table 1 but then if you select table 2 from the dropdown then it will only display table 2.

The 2 datatables are very similar in format, they just pull from different tables in the database. Currently I have them both in the code so it just shows one on top of the other. Here is the code for my tables including HTML, php and JS:

<div class="dashboardTable" style="width:965px; overflow:auto;">
<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>
    <th style="padding-left: 10px; padding-right:10px; border: 1px solid #468BBD;">lowSideMIUNumArriv</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>
    <td style="border-collapse: collapse; padding-left: 10px; padding-right:10px;"><? echo $row['lowSideMIUNumArriv'];?>&nbsp;</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">
(function($) {
$(document).ready(function(){
$('#mytable').DataTable();
   $('.dataTable').wrap('<div class="dataTables_scroll" />');
});
}(jQuery));
</script>

<div class="dashboardTableSurvey" style="width:965px; overflow:auto;">
<table id="mytableSurvey" 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($result2)){
?>

<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">
(function($) {
 $(document).ready(function(){
 $('#mytableSurvey').DataTable();
   $('.dataTable').wrap('<div class="dataTables_scroll" />');
 });
}(jQuery));
</script>

Again, nothing changes much in the format or code of these tables, but is there a way to use a dropdown for selecting which of these tables to show?

*****UPDATE*****

New code, not quite working:

<select name='tables' id='select-tables'>
  <option value="mytable">Survey Test Table</option>
  <option value="mytableSurvey">Survey Only</option>
</select>

<script>
(function($) {
   $('#select-tables').on('change', function(){
   var table = $(this).find('option:selected');
   $('#' + table).show();
   $('table').not('#' + table).hide();
});
}(jQuery));
</script>

My table IDs are mytable and mytableSurvey, and I've put display:none in my css for table but because I"m using datatables it still shows the page number and search options for the datatables themselves. THe dropdown is not showing each selection, however.

You have to load datatable on change event of dropdown. But before load from one datatable to another datatable, you have to destroy previous datatable. For e.g. table.destroy();. Here, table is variable which you have declared to create datatable. — Virb
– Virb, Commented May 22, 2017 at 12:06

capcj · Accepted Answer · 2017-05-22 12:32:54Z

    //In your css:

    table {
       display: none;
    }

        <div class="dashboardTable" style="width:965px; overflow:auto;">
    <select name='tables' id='select-tables'>
      <option value="table1">First Table</option>
      <option value="teble2">Second Table</option>
    </select>
        <table id="table1" 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>
            <th style="padding-left: 10px; padding-right:10px; border: 1px solid #468BBD;">lowSideMIUNumArriv</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>
            <td style="border-collapse: collapse; padding-left: 10px; padding-right:10px;"><? echo $row['lowSideMIUNumArriv'];?>&nbsp;</td>
            </tr>
            <?}?>
           </tbody>
          </table>
<table id="table2" 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>
            <th style="padding-left: 10px; padding-right:10px; border: 1px solid #468BBD;">lowSideMIUNumArriv</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>
            <td style="border-collapse: collapse; padding-left: 10px; padding-right:10px;"><? echo $row['lowSideMIUNumArriv'];?>&nbsp;</td>
            </tr>
            <?}?>
           </tbody>
          </table>
        </div>


        <script type="text/javascript"   src="https://cdn.datatables.net/1.10.15/js/jquery.dataTables.min.js">
    <script>
    $('#select-tables').on('change', function(){
       var table = $(this).find('option:selected');
       $('#' + table).show();
       $('table').not('#' + table).hide();
    });
    </script>

Not so elegant, but quick solution. If you want to load all tables at once. The best performance would be get them by ajax:

In your frontend/view page:

$("#select-tables").change(function(){
  $.get("yourcodetogettables.php?table=" + $(this).find("option:selected").val(), function(data, status){
    //RETRIEVE YOUR HTML BY THAT YOURCODETOGETTABLES
    $(table).remove();
    $('body').append(data);
  });
});

In the PHP COde: ECHO THAT STRUCTURE:

    <div class="dashboardTable" style="width:965px; overflow:auto;">
<select name='tables' id='select-tables'>
  <option value="table1">First Table</option>
  <option value="teble2">Second Table</option>
</select>
    <table id="table1" 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>
        <th style="padding-left: 10px; padding-right:10px; border: 1px solid #468BBD;">lowSideMIUNumArriv</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>
        <td style="border-collapse: collapse; padding-left: 10px; padding-right:10px;"><? echo $row['lowSideMIUNumArriv'];?>&nbsp;</td>
        </tr>
        <?}?>
       </tbody>
      </table>
        </div>

Thank you, I will try this now! For the first solution, will I just add the onChange JS under my JS code for the datatables? Since my existing code creates the actual datatables and the new JS code will just say that it needs to load the selected one?
In the first code, you will put css to hide all your tables and show them by js onchange of your dropdown, the second one you will bring the table that user want and append (put the code) inside your body tag, remove before that all tables in the frontend.
The first one has concerns towards scalability and performance, but it's easier to implement considering what you already made. The second one will lead in more work, but it's more scalable.
I see, thank you. I've tried this and I updated my code with the newer change but The dropdown is not displaying each table, so my selections are just static within the dropdown. I am using wordpress so I had to slightly reformat the JS code but do you mind looking at my edit and see if there's a glaring issue there?
Sry, i'm at work and didn't see your comment. You can put your dropdown responsible by: - Detecting all tables in the frontend with JS and filling the dropdown; - Fill them with php getting from DB or an array;

Collectives™ on Stack Overflow

Using a dropdown to show a particular datatable

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related