Cascading Dropdowns that then lead to datatable

rob1st

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.

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() <?php > ?>

' onchange='this.form.submit()'> Choose Location"; <?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'].""; } else { echo ">".$row['LocationName'].""; } } } ?>
</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>

<?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' > "; if($row3[4] == 1) { echo " "; } elseif($row3[4] == 3) { echo " "; } else { echo " "; } echo " "; } } echo "

Design Tag	Spec Tag	Asset	Status
$row3[1]	$row3[2]	$row3[3]	None	Installed	Tested

";
}

<?php >

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

allan

Hi,

I'd say that looks fine to me. If you wanted to, you could use the DataTable ajax option to get the data and send the options selected using ajax.data. But I don't think there is anything wrong with the approach you've taken! It works well

Allan