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

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

Table with DropDown Filter and Content

CodePudding user response：

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>

CodePudding user response：

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