Cascading Dropdowns that then lead to datatable
Cascading Dropdowns that then lead to datatable
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()
?>
<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') ?>
Answers
Hi,
I'd say that looks fine to me. If you wanted to, you could use the DataTable
ajaxoption to get the data and send the options selected usingajax.data. But I don't think there is anything wrong with the approach you've taken! It works wellAllan