How to Filter data in HTML Table with 3 DropDowns - Jquery

AminCoD
October 21, 2022
263 views
0 votes
2 Answers

I want to filter data in the html table based on 3 dropdown list values (standort, status and infrastructure). –> ddlStandort, ddlStatus, ddlInfrastruktur
My below code is not working, I don’t know why…..

JavaScript

<script type="text/javascript" src="https://ajax.googleapis.com/ajax/libs/jquery/1.8.3/jquery.min.js"></script>
    <script type="text/javascript">
    $(document).ready(function () {
        $("#ddlStandort,#ddlStatus,#ddlInfrastruktur").on("change", function () {
            var standort = $('#ddlStandort').find("option:selected").val();
            var status = $('#ddlStatus').find("option:selected").val();
            var infrastructure = $('#ddlInfrastruktur').find("option:selected").val();
            SearchData(standort, status, infrastructure)
        });
    });
    function SearchData(standort, status, infrastructure) {
        if (standort.toUpperCase() == 'All' && status.toUpperCase() == 'All' && infrastructure.toUpperCase() == 'All') {
            $('#table11 tbody tr').show();
        } else {
            $('#table11 tbody tr:has(td)').each(function () {
                var rowStandort = $.trim($(this).find('td:eq(9)').text());
                var rowStatus = $.trim($(this).find('td:eq(5)').text());
                var rowInfrastructure = $.trim($(this).find('td:eq(10)').text());
                if (standort.toUpperCase() != 'All' && status.toUpperCase() != 'All' && infrastructure.toUpperCase() != 'ALL') {
                    if (rowStandort.toUpperCase() == standort.toUpperCase() && rowStatus == status && rowInfrastructure == infrastructure) {
                        $(this).show();
                    } else {
                        $(this).hide();
                    }
                } else if ($(this).find('td:eq(9)').text() != '' || $(this).find('td:eq(5)').text() != '' || $(this).find('td:eq(10)').text() != '') {
                    if (standort != 'All') {
                        if (rowStandort.toUpperCase() == standort.toUpperCase()) {
                            $(this).show();
                        } else {
                            $(this).hide();
                        }
                    }
                    if (status != 'All') {
                        if (rowStatus == status) {
                            $(this).show();
                        }
                        else {
                            $(this).hide();
                        }
                    }
                    if (infrastructure != 'All') {
                        if (rowInfrastructure == infrastructure) {
                            $(this).show();
                        }
                        else {
                            $(this).hide();
                        }
                    }
                }
            });
        }
    }
</script>

HTML/PHP

<?php
        require("db_conn.php");
        $sql = "SELECT * FROM `dspia_ip_state` ORDER BY state_id";
        $all_states = mysqli_query($conn,$sql);
        ?>
            <select id="ddlStatus" name="ddlStatus"><br>
                <option value="All">Status</option>
                <?php
                while ($state = mysqli_fetch_array(
                        $all_states,MYSQLI_ASSOC)):;
            ?>
                <option value="<?php echo $state["state"];
                ?>">
                    <?php echo $state["state"];
                    ?>
                </option>
            <?php
                endwhile;
            ?>
            </select>
        <?php
        require("db_conn.php");
        $sql = "SELECT * FROM `dspia_location` ORDER BY location_id";
        $all_location = mysqli_query($conn,$sql);
        ?>
            <select id="ddlStandort" name="ddlStandort">
                <option value="All">Standort</option>
                <?php
                while ($location = mysqli_fetch_array(
                        $all_location,MYSQLI_ASSOC)):;
            ?>
                <option value="<?php echo $location["location"];
                ?>">
                    <?php echo $location["location"];
                    ?>
                </option>
            <?php
                endwhile;
            ?>
            </select>
        <?php
        require("db_conn.php");
        $sql = "SELECT * FROM `dspia_infrastructure` ORDER BY infrastructure_id";
        $all_infrastructure = mysqli_query($conn,$sql);
        ?>
            <select id="ddlInfrastruktur" name="ddlInfrastruktur">
                <option value="All">Infrastruktur</option>
                <?php
                while ($infrastructure = mysqli_fetch_array(
                        $all_infrastructure,MYSQLI_ASSOC)):;
            ?>
                <option value="<?php echo $infrastructure["infrastructure"];
                ?>">
                    <?php echo $infrastructure["infrastructure"];
                    ?>
                </option>
            <?php
                endwhile;
            ?>
            </select>

the table data is selected from a database

Whole Code

<body>
        <div class="row header">
                <a href="home.php"><img id="drv" src="img/neueip/logo_drv_berlin-brandenburg_web.jpg"></a>
                <div class= "header-text">
                    <h2>IP-Adressverwaltung</h2>
                </div>
                <div class= "header-line">
                    <h1>|</h1>
                </div>
            <div class="col-50 rechts">
            <?php
            if($_SESSION['is_admin'] =='1'){
                ?>
                <div class="verwaltung">
                    <button class="dropbtn"><b>Verwaltung</b></button>
                <div class="dropdown-content">
                    <a href="home.php">IP-Adresse</a>
                    <a href="benutzer.php">Benutzer</a>
                    <a href="status.php">Status</a>
                    <a href="standort.php">Standort</a>
                    <a href="betriebssystem.php">Betriebssystem</a>
                    <a href="infrastruktur.php">Infrastruktur</a>
                </div>
                </div>
            <?php
            }
            ?>
                <a href="logout.php"><img src="img/home/logout.png" class="logout"></a>
                <div class= "user">
                    <h3>| Benutzer: <?php print_r($_SESSION['login_name']); ?> |</h3>
                </div>
            </div>
            <!--
            <div class="gesamtPapierkorb">
            <a href="home.php"><img src="img/home/gesamtPapierkorb.png"></a>
            </div>
            -->
        </div>
        <div class="reload">
            <a href="home.php"><img src="img/home/refresh.png"></a>
        </div>
        <div class="filtern">
        <?php
        $sql = "SELECT * FROM `dspia_ip_state` ORDER BY state_id";
        $all_states = mysqli_query($conn,$sql);
        ?>
            <select id="ddlStatus" name="ddlStatus"><br>
                <option value="All">Status</option>
                <?php
                while ($state = mysqli_fetch_array(
                        $all_states,MYSQLI_ASSOC)):;
            ?>
                <option value="<?php echo $state["state"];
                ?>">
                    <?php echo $state["state"];
                    ?>
                </option>
            <?php
                endwhile;
            ?>
            </select>
        <?php
        $sql = "SELECT * FROM `dspia_location` ORDER BY location_id";
        $all_location = mysqli_query($conn,$sql);
        ?>
            <select id="ddlStandort" name="ddlStandort">
                <option value="All">Standort</option>
                <?php
                while ($location = mysqli_fetch_array(
                        $all_location,MYSQLI_ASSOC)):;
            ?>
                <option value="<?php echo $location["location"];
                ?>">
                    <?php echo $location["location"];
                    ?>
                </option>
            <?php
                endwhile;
            ?>
            </select>
        <?php
        $sql = "SELECT * FROM `dspia_infrastructure` ORDER BY infrastructure_id";
        $all_infrastructure = mysqli_query($conn,$sql);
        ?>
            <select id="ddlInfrastruktur" name="ddlInfrastruktur">
                <option value="All">Infrastruktur</option>
                <?php
                while ($infrastructure = mysqli_fetch_array(
                        $all_infrastructure,MYSQLI_ASSOC)):;
            ?>
                <option value="<?php echo $infrastructure["infrastructure"];
                ?>">
                    <?php echo $infrastructure["infrastructure"];
                    ?>
                </option>
            <?php
                endwhile;
            ?>
            </select>
        </div>
        
        <input type="text" id="suchleiste" onkeyup="myFunction()" placeholder="Suchen...">
        
        </div>
        <div class="IPbutton">
            <form action="newip.php"  method="post">
            <button class="IP" type="submit"><b>+  Neue IP-Adresse</b></button>
            </form>
        </div>
       
    <link rel="stylesheet" href="https://cdnjs.cloudflare.com/ajax/libs/font-awesome/4.7.0/css/font-awesome.min.css">
    
    
    
    <div class="tabelle">
    <div style="overflow-x:auto;">
    <table id=table11>
        <tr>
            <th></th>
            <th onclick="sortID()">ID</th>
            <th onclick="sortTable(1)">IP-Adresse ↕</th>
            <th onclick="sortTable(2)">Hostname ↕</th>
            <th onclick="sortTable(3)">MAC-Adresse ↕</th>
            <th onclick="sortTable(4)">PW_Neu ↕</th>
            <th onclick="sortTable(5)">Status ↕</th>
            <th onclick="sortTable(6)">Beschreibung ↕</th>
            <th onclick="sortTable(7)">Betriebssystem ↕</th>
            <th onclick="sortTable(8)">Admin ↕</th>
            <th onclick="sortTable(9)">Standort ↕</th>
            <th onclick="sortTable(10)">Infrastruktur ↕</th>
            <th onclick="sortTable(11)">Workorder ↕</th>
            <th onclick="sortTable(12)">Anmerkungen ↕</th>
        </tr>
        
<?php
if(isset($_GET["del"])){
    if(!empty($_GET["del"])){
        $main_id = $_GET["del"];
        $stmt = $conn->prepare("DELETE FROM dspia_main WHERE main_id = '".$main_id."'");
        
        if($stmt->execute()){
            ?>
            <div class="alert">
            <span class="closebtn" onclick="this.parentElement.style.display='none';">&times;</span> 
            <strong>
            <?php
            print "Die IP-Adresse wurde gelöscht.";
            ?>
            </strong> 
            </div>
            <?php
        }else{
            print $conn->error; 
        }
    }
}

$sql = "SELECT * FROM v_dspia_main";
$result = mysqli_query($conn, $sql);

while($dsatz = mysqli_fetch_assoc($result)){
    ?>
    <tbody id="myTable">
    <tr>
        <th><a href="editIP.php?id=<?php echo $dsatz["main_id"] ?>"><img src="img/home/stift.png" class="Stift" width="auto" height="30"></a><a href="home.php?del=<?php echo $dsatz["main_id"] ?>"onclick='return confirmSubmit()'><img src="img/home/mülleimer.png" class="Mülleimer" width="auto" height="33" class="Mülleimer"></th>
        <td><?php echo $dsatz["main_id"] ?></td>
        <td><?php echo $dsatz["ip_v4"] ?></td>
        <td><?php echo $dsatz["hostname"] ?></td>
        <td><?php echo $dsatz["mac_address"] ?></td>
        <td><?php if($dsatz["pw_new"] == "1") {
            echo "Ja";
        }else if($dsatz["pw_new"] == "0"){
            echo "Nein";
        } ?></td>
        <td><?php echo $dsatz["state"] ?></td>
        <td><?php echo $dsatz["description"] ?></td>
        <td><?php echo $dsatz["op_system"] ?></td>
        <td><?php echo $dsatz["operator"] ?></td>
        <td><?php echo $dsatz["location"] ?></td>
        <td><?php echo $dsatz["infrastructure"] ?></td>
        <td><?php echo $dsatz["workorder"] ?></td>
        <td><?php echo $dsatz["remarks"] ?></td>
    </tr>
    <?php
    }
    ?>
    <colgroup>
    <col width="5%">
    </colgroup>
    </tbody>
    </table>
    </div>
    </div>

Tables =
main_dspia (main table)
v_main_dspia (view to show the table on website)
dspia_ip_state (for the Dropdown state)
dspia_location (for the Dropdown location)
dspia_infrastructure (for the Dropdown infrastructure)

Table with DropDown Filter and Content

Answers

Your Javascript code works fine. There is something wrong with your data in php/html.
Check the following example:


<select id="ddlStandort">
    <option value="All">All</option>
    <option value="TestValue1">TestValue1</option>
    <option value="TestValue2">TestValue2</option>
</select>

<br>

<select id="ddlStatus">
    <option value="All">All</option>
    <option value="Pending">Pending</option>
    <option value="Sent">Sent</option>
</select>

<br>

<select id="ddlInfrastruktur">
    <option value="All">All</option>
    <option value="Cars">Cars</option>
    <option value="Motorcycles">Motorcycles</option>
</select>

<br>

<table id="table11">
    <thead>
        <tr>
            <th>Entry Code</th>
            <th>--------</th>
            <th>--------</th>
            <th>--------</th>
            <th>--------</th>
            <th>Status</th>
            <th>--------</th>
            <th>--------</th>
            <th>--------</th>
            <th>Standort</th>
            <th>Infrastruktur</th>
        </tr>
    </thead>

    <tbody>
        <tr>
            <td>1</td>
            <td></td>
            <td></td>
            <td></td>
            <td></td>
            <td>Pending</td>
            <td></td>
            <td></td>
            <td></td>
            <td>TestValue1</td>
            <td>Cars</td>
        </tr>
        <tr>
            <td>2</td>
            <td></td>
            <td></td>
            <td></td>
            <td></td>
            <td>Sent</td>
            <td></td>
            <td></td>
            <td></td>
            <td>TestValue2</td>
            <td>Motorcycles</td>
        </tr>
    </tbody>
</table>

<script type="text/javascript" src="https://ajax.googleapis.com/ajax/libs/jquery/1.8.3/jquery.min.js"></script>
    <script type="text/javascript">
    $(document).ready(function () {
        $("#ddlStandort,#ddlStatus,#ddlInfrastruktur").on("change", function () {
            var standort = $('#ddlStandort').find("option:selected").val();
            var status = $('#ddlStatus').find("option:selected").val();
            var infrastructure = $('#ddlInfrastruktur').find("option:selected").val();
            SearchData(standort, status, infrastructure)
        });
    });
    function SearchData(standort, status, infrastructure) {
        if (standort.toUpperCase() == 'All' && status.toUpperCase() == 'All' && infrastructure.toUpperCase() == 'All') {
            $('#table11 tbody tr').show();
        } else {
            $('#table11 tbody tr:has(td)').each(function () {
                var rowStandort = $.trim($(this).find('td:eq(9)').text());
                var rowStatus = $.trim($(this).find('td:eq(5)').text());
                var rowInfrastructure = $.trim($(this).find('td:eq(10)').text());
                if (standort.toUpperCase() != 'All' && status.toUpperCase() != 'All' && infrastructure.toUpperCase() != 'ALL') {
                    if (rowStandort.toUpperCase() == standort.toUpperCase() && rowStatus == status && rowInfrastructure == infrastructure) {
                        $(this).show();
                    } else {
                        $(this).hide();
                    }
                } else if ($(this).find('td:eq(9)').text() != '' || $(this).find('td:eq(5)').text() != '' || $(this).find('td:eq(10)').text() != '') {
                    if (standort != 'All') {
                        if (rowStandort.toUpperCase() == standort.toUpperCase()) {
                            $(this).show();
                        } else {
                            $(this).hide();
                        }
                    }
                    if (status != 'All') {
                        if (rowStatus == status) {
                            $(this).show();
                        }
                        else {
                            $(this).hide();
                        }
                    }
                    if (infrastructure != 'All') {
                        if (rowInfrastructure == infrastructure) {
                            $(this).show();
                        }
                        else {
                            $(this).hide();
                        }
                    }
                }
            });
        }
    }
</script>

First, is not needed to include the db.connection file each time you execute a query, it’s suficient to add it at the top file and to prevent duplicate db connection.
i recommend this syntax require_once("db_conn.php");

for your issue it’s possible to do it in 2 ways:

method 01
add a filter form with get method and use the $_GET request in your php code

Filter Form

<form method="get">
<select name="filter-1" onchange="this.form.submit()">
<option value="f1-v1">F1V1</option>
<option value="f1-v2">F1V2</option>
<option value="f1-v3">F1V3</option>
</select>
<select name="filter-2" onchange="this.form.submit()">
<option value="f2-v1">F2V1</option>
<option value="f2-v2">F2V2</option>
<option value="f2-v3">F2V3</option>
</select>
<select name="filter-3" onchange="this.form.submit()">
<option value="f3-v1">F3V1</option>
<option value="f3-v2">F3V2</option>
<option value="f3-v3">F3V3</option>
</select>

this mean when one of the selectbox changes the form will be submitted and the url will be changed
https://exemple.com/page.php?filer-1=value&filer-2=value&filter-3=value

PHP Code

<?php 
require_once("db_conn.php");
if(isset($_GET['filter-1']) && isset($_GET['filter-2']) && isset($_GET['filter-3']) ) {
$fetchData = $conn->query("SELECT * FROM table WHERE col1 = '".$_GET['filter-1']."' && col2 = '".$_GET['filter-2']."' && col3 = '".$_GET['filter-3']."' "); 
}
else {
$fetchData = $conn->query("SELECT * FROM table ");
}
echo '<table>';
foreach($fetchData as $row) {
echo '<tr>';
echo '<td>$row['col1']</td>'; //Filter 1 Value
echo '<td>$row['col2']</td>'; //Filter 2 Value
echo '<td>$row['col3']</td>'; //Filter 3 Value
echo '</tr>';
}
echo '</table>';
?>

This is a simple example

Methode 02
use jquery + ajax to update the table data on filter form select change

I hope that this will help you to solve your problem or at least give you an idea on how to solve it

Good luck

Please signup or login to give your own answer.

Click here to cancel reply.

How to Filter data in HTML Table with 3 DropDowns – Jquery

Answers