cascading-dropdowns-that-then-lead-to-datatable

Shivani Vyas

example is here: https://test.assettrack.cx/install.php

I have the page working through form reloading on submit, but I am sure there is probably a better way to do it with datatables/ajax and wonder if you could advise?

So there are 3 drop downs, you choose location, then it reveals room drop down, you choose room and then it reveals the datatable filtered to those two dropdowns, and then has an optional drop down to filter more if required.

My current code is using SQL to pull the data then transferring it to the datatable, I have an editor licence, but not sure that helps here.

There is an install function that essentially changes the status when they click it, I know I can get Datatables to do that and will get that working when I get the load improved.

Here below code is working fine but when you choose location - the whole page reloads / refereshed and display second dropdown for room .. again when you choose room - the whole page reloads/ refereshed and reveals the datatable filtered to those two dropdowns. My question is .. Is it possible that instead of refresh whole page only data/ content reflect ? Is there anything that I can add or remove from below code?

my code

<?php
//SESSION START
if(!isset($_SESSION)) {
  session_start();
}
$actual_link = (isset($_SERVER['HTTPS']) && $_SERVER['HTTPS'] === 'on' ? "https" : "http") . "://$_SERVER[HTTP_HOST]$_SERVER[REQUEST_URI]";
if (strpos($actual_link, 'test.assettrack')) {
  $path ="https://test.assettrack.cx";
} else {
  $path = "https://assettrack.cx";
}

//PERMISSIONS (REQUIRED IN INCLUDE)
$public = 1; // 0 = Have to be logged in, 1 = Anyone can view.
$permission = 15; //10 = User, 20 = Tester, 30 = Admin, 40 = SuperAdmin
$clientAccess = 0; //0 = Client canot view, 1 = client can view
$_SESSION['redirect'] = basename($_SERVER['SCRIPT_FILENAME']);
$rootfile = 1;

// INCLUDES
require('includes/includes.php'); // Database functions

// STANDARD PAGE SPECIFIC VARIABLE SET
$title = "Install Check"; //Page Title - Must be before filestart
require('includes/filestart.php');


//GET POST INFO FROM FORM RESUBMIT
if($_SERVER['REQUEST_METHOD'] == "POST") {

  //GET LOCATION INFO FROM FORM RESUBMIT
  if(isset($_POST['loc'])) {
    $loc = $_POST['loc'];
  } else {
    $loc = null;
  }

  //GET ROOM INFO FROM FORM RESUBMIT
  if(isset($_POST['Room'])) {
    $Room = $_POST['Room'];
  } else {
    $Room = null;
  }

  //GET TYPE INFO FROM FORM RESUBMIT (OPTIONAL)
  if($_POST['type'] > 0 ) {
    $type = $_POST['type'];
    $typeSQL = " AND A.assetType = $type ";
  } else {
    $type = null;
    $typeSQL = null;
  }
}

//FUNCTION FOR INSTALLED BUTTON
install()
?>

<div class="d-md-none">
  <div style="margin-top: 100px"></div>
</div>


<div class='container1'>
  <form name="installform" method="post" role="form" >
    <div class="col-md-11 container-flex mx-auto">
      <div class="col-11 form-location mx-auto">
        <select class='form-select' name='loc' value='<?php echo $loc ?>' onchange='this.form.submit()'></option>
          <option value=''>Choose Location</option>";
          <?php 

            //LOCATION DROP DOWN
            $SQL = "SELECT L.id, L.LocationName FROM asset A JOIN loc L ON A.loc = L.id GROUP BY L.LocationName ORDER BY L.id";
            $list = query($SQL);
            if(is_array($list) || is_object($list)) {
            foreach($list as $row) {
                echo "<option value='".$row['id']."'";
                IF($row['id'] == $loc) {
                echo " selected>".$row['LocationName']."</option>";
                } else {
                echo ">".$row['LocationName']."</option>";
                }
            }
            }
          ?>
        </select>
      </div>
    </div>

    <?php
      //IF LOCATION IS SELECTED SHOW ROOM DROP DOWN
      if($loc > 0 ) {
    ?>

    <div class="col-md-11 container-flex mx-auto mt-2">
      <div class="col-11 form-location mx-auto">
        <select class='form-select' name='Room' value='' onchange='this.form.submit()'></option>
          <option value=''>Choose Room</option>
          <?php 

            //ROOM DROP DOWN
            $SQL1 = "SELECT A.Room, R.room FROM asset A LEFT JOIN room R ON R.roomNo = SUBSTRING(A.Room,1,3) WHERE A.Room != '' AND A.loc = $loc GROUP BY A.Room ORDER BY A.Room";
            $list1 = query($SQL1);
            if(is_array($list1) || is_object($list1)) {
              foreach($list1 as $row) {
                echo "<option value='".$row['Room']."'";
                IF($row['Room'] == $Room) {
                  echo " selected>".$row['Room']." ".$row['room']."</option>";
                } else {
                  echo ">".$row['Room']." ".$row['room']."</option>";
                }
              }
            }
          ?>
        </select>
      </div>
    </div>

    <?php
      }

      //IF LOCATION AND ROOM IS SELECTED SHOW TYPE DROP DOWN
      if($loc > 0 && $Room > 0) {
    ?>

    <div class="col-md-11 container-flex mx-auto mt-2">
      <div class="col-11 form-location mx-auto">
        <select class='form-select mb-2' name='type' value='' onchange='this.form.submit()'></option>
          <option value=''>Choose Type (Optional)</option>
          <?php 

            //TYPE DROP DOWN
            $SQL2 = "SELECT T.assetTypeID, T.assetType FROM asset A LEFT JOIN assettype T ON T.assetTypeID = A.assetType WHERE A.ROOM = '$Room' AND A.loc = $loc GROUP BY T.assetType ORDER BY T.assetType";
            $list2 = query($SQL2);
            if(is_array($list2) || is_object($list2)) {
              foreach($list2 as $row) {
                  echo "<option value='".$row['assetTypeID']."'";
                  IF($row['assetTypeID'] == $type) {
                  echo " selected>".$row['assetType']."</option>";
                  } else {
                  echo ">".$row['assetType']."</option>";
                  }
              }
            }
          ?>
        </select>
      </div>
    </div>

  </form>

  <div class="container-fluid ms-7 w-auto">
  <div class="card">
    <div class="card-body">
      <div id='' class='content'>
        <table id='assyntCx_Table' class='stripe row-border order-column' style="width:100%">
          <thead>
            <tr>
              <th style='width: 36%;'>Design Tag</th>
              <th style='width: 36%;'>Spec Tag</th>
              <th style='width: 36%;'>Asset</th>
              <th style='width: 28%; text-align: right'>Status</th>
            </tr>
      </thead>
      <tbody>

  <?php
    $SQL3 = "SELECT A.id, A.dwgTag, Z.assetTag, T.assetType, A.assetStatus, S.assetStatus FROM asset A LEFT JOIN assetstatus S ON S.id = A.assetStatus LEFT JOIN assetTag Z ON Z.assetID = A.id LEFT JOIN assettype T ON T.assetTypeID = A.assetType WHERE A.loc = $loc AND A.Room = '$Room' $typeSQL ORDER BY A.dwgTag";
    if($result3 = query($SQL3)) {
      while ($row3 = mysqli_fetch_array($result3)) {
        $assetID = $row3[0];
        echo "
      <form name='installform' method='post' role='form' >
        <input type='hidden' name='loc' id='loc' class='form-control' value='$loc'>
        <input type='hidden' name='Room' id='Room' class='form-control' value='$Room'>
        <input type='hidden' name='assetID' id='assetID' class='form-control' value='$assetID'>
        <tr>
          <td style='width: 36%'><small>$row3[1]</small></td>
          <td style='width: 36%'><small>$row3[2]</small></td>
          <td style='width: 36%'><small>$row3[3]</small></td>";
      if($row3[4] == 1) {
        echo "
          <td class='text-center'>
            <button type='submit' class='btn btn-danger btn-sm' name='install' value='3'><small>None</small></button>
          </td>";
      } elseif($row3[4] == 3) {
        echo "
          <td class='text-center'>
            <button type='submit' class='btn btn-warning btn-sm' name='uninstall' value='1'><small>Installed</small></button>
          </td>";
      } else {
        echo "
          <td class='text-center'>
            <button type='button' class='btn btn-success btn-sm' disabled><small>Tested</small></button>
          </td>";
      }
        echo "
        </tr>
      </form>";
    }
  }

  echo "
      <tbody>
    </table>
    </div>
    </div>
  </div>
</div>


  ";
}
?>
</div>
<script>
$(document).ready(function() {
    $('#assyntCx_Table').DataTable( {
        dom: 'fit',
        scrollY:        '65vh',
        scrollCollapse: true,
        paging:         false
    } );
} );
</script>
<?php require('includes/fileend.php') ?>

allan

I answered an almost identical question here. Is @rob1st a colleague of yours?

Allan

Shivani Vyas

@allan Thank you so much allan for the link. In that post you mentioned about DataTable ajax option to get the data and send the options selected using ajax.data. So it it not possible what I want to achieve in above code correct ? I have to use ajax options. Please check below question again. This is what I want to achieve.

My code is working fine but when you choose location - the whole page reloads / refereshed and display second dropdown for room .. again when you choose room - the whole page reloads/ refereshed and reveals the datatable filtered to those two dropdowns. My question is .. Is it possible that instead of refresh whole page only data/ content reflect ? Is there anything that I can add or remove from below code?

kthorngren

From a Datatables perspective there are a couple things you can do:

1. Use rows().invalidate() after you reload the table to have Datatables refresh its data cache.
2. Use destroy() before reloading the table then reinitialize Datatables after the table is reloaded.

Basically you need to tell Datatables there is new table data in the DOM. The process you use to refresh the table versus reloading the page is outside of Datatables. Stack Overflow or other source might have ideas for how to handle the page the way you want.

Kevin

rob1st

Hi @allan, yes, I handed the task over to Shivani because I had to attend to other business, apologies for not passing her to the original thread.