How can i add filter options for my table out of my database in php

Question

I want to filter the output of my table out of my database by for example the 'product_id'. So only products with the same id are shown. I added a dropdown in HTML but i don't know how i can connect it with my mysql request.

Here you can see what the headers of my table look like, and how my dropdown looks, it already takes the data of my database, so it already shows the right products

 <div class="form-group{P1}">
<label class="col-sm-2 control-label" for="selectp1" value="{P1}">P</label>
<div class="col-sm-10">
    <select id="selectp1" name="selectp1" required autofocus class="form-control selectpicker unloadmsg" onchange="showSelects();">
        {POPTIONS}
    </select>
</div>

     private function getDatensatzQuery() {
        $sql = "SELECT [....]


                . 'LEFT JOIN [....];

        if ($filterdata['filterfor'] == 'date') {
            $sql .= "AND DATE(a.datum) = '$filterdata[datum]' ";
        } else if ($filterdata['filterfor'] == 'daterange') {
            $sql .= "AND a.datum BETWEEN '" . date('Y-m-d H:i:s', 
  $filterdata['filterrangefrom']) . 
                    "' AND '" . date('Y-m-d H:i:s', 
  $filterdata['filterrangeto']) . "' ";
        }
        if($filterdata['filterfor']=='produkt'){
            $sql .= "AND p_id= '$filterdata[p]'";
        }

        $sql .= "ORDER BY $orderby " . (($orderdesc) ? 'DESC' : 'ASC');
        $sql .= ($paginate) ? " LIMIT $start, $schritt" : '';          
        return $sql;
}

Feel free to ask questions

lot of tutorial available try to do something see https://stackoverflow.com/questions/15415571/ajax-php-drop-down-list — Rahul, Apr 13 '18 at 09:27

Antoine Petitot · Answer 1 · 2018-04-13T10:49:56.597

EDIT : Deleted my last comment, here's what I coded with my understanding of your question, but I can't help with with SQL filters / sorting as I never used it

<table style="text-align: center;">
    <tr>
        <th>
            ID
        </th>
        <th>
            NAME
        </th> 
        <th>
            IP
        </th>
    </tr>
<?php
    $servername = "";
    $susername = "";
    $spassword = "";
    $dbname = "";
    $conn = mysqli_connect($servername, $susername, $spassword, $dbname); //Connect
    if(!empty($_POST['option_filter'])){
        $choosen_filter = $_POST['option_filter'];
        //You can now create the SQL request to filter the results to display to the user depending on one of the options
        if($choosen_filter == 'filtrer_ids'){
            //SQL filter per IDS
        }else if($choosen_filter == 'filtrer_names'){
            //SQL filter per NAMES
        }else if($choosen_filter == 'filtrer_ips'){
            //SQL filter per IPS
        }
        // ----------------------------------------------------------------------------------
        // This is just an example to get every row of the database with all 3 variables
        // You can print each row with all results just after sorting/filtering them
        $sql = "SELECT id,username,ip FROM users_database";
        $result = $conn->query($sql);
        if ($result->num_rows > 0) {
            while($row = $result->fetch_assoc()) {
                $MYSQL_username = $row['username'];
                $MYSQL_ip = $row['ip'];
                $MYSQL_id = $row['id'];
                $display=1; //Just to remember that you got results
                //Then print the entire row with each values
                print('<tr><th>'.$MYSQL_username.'/th><th>'.$MYSQL_ip.'</th><th>'.$MYSQL_id.'</th></tr>');
               }
        }
        // ----------------------------------------------------------------------------------
    }
?>
</table>
<form action="PAGE.php" method="POST" id="filter">
    <select name="option_filter">
        <option value="filtrer_ids">Filter IDS</option>
        <option value="filtrer_names">Filter NAMES</option>
        <option value="filtrer_ips">Filter IPS</option>
    </select>
    <input type="submit" name="submit" value="filter">
</from>

I want to add dropdown fields above the table where i can choose options which filter my table. So like i have to add below the sql request that if a dropdown field is chosen i have to add for example "AND product_id=1" but i dont know how fetch the input of the dropdown field into my php script. — RYU5, Apr 13 '18 at 10:04

Celebrombore · Answer 2 · 2018-04-13T10:34:28.720

That was my try a long time ago ^^ Worked for me but I only used 1 filter. Please note that I used JQuery.

Next to the dropdown Ive added a button:

<button id="suchen" onclick="Suchen()" value="Suche starten" class="btn btn-secondary">Suche starten</button>

It invokes my Search-Function named "Suchen()" (JS Function).

function Suchen() {
     var eingrenzung1 = $("#filter :selected").text();
     var eingrenzung2 = $("#filter2 :selected").text();
     var eingrenzung3 = $("#filter3 :selected").text();
     var eingrenzung4 = $("#filter4 :selected").text();
     //Just an idea, otherwise the if-Check would be false
     if(eingrenzung2 == ""){
           var eingrenzung2 = "%"
     }
     if(eingrenzung3 == ""){
           var eingrenzung3 = "%"
     }
     if(eingrenzung4 == ""){
           var eingrenzung4 = "%"
     }
     Ajax('#tabelle', 'http://localhost/.../ajax/Tabelle.php?eingrenzung1='+eingrenzung1+'&eingrenzung2='+eingrenzung2+'&eingrenzung3='+eingrenzung3+'&eingrenzung4='+eingrenzung4;

}

It sends the data to the table (Ive placed the table in an extra doc) and it activates an if-check in that doc

if(isset($_GET['eingrenzung1']) AND isset($_GET['eingrenzung2']) AND isset($_GET['eingrenzung3']) AND isset($_GET['eingrenzung4'])) {
    $kopf1->Sucher($_GET['eingrenzung1'], $_GET['eingrenzung2'], $_GET['eingrenzung3'], $_GET['eingrenzung4']);
    $result2 = $kopf1->alleDaten;
    $anzahl = $kopf1->anzahl;
}

$kopf1 is an object, dont know whether you are using OOP?, and the Sucher Class-Function:

public function Sucher($eingrenzung1,$eingrenzung2,$eingrenzung3,$eingrenzung4){
    $query = "SELECT * FROM table WHERE Column1 LIKE '$eingrenzung1%' AND Column2 LIKE '$eingrenzung2%' AND Column3 LIKE '$eingrenzung3%' AND Column4 LIKE '$eingrenzung4%'";
    $this->Ausfuehren($query);
    $this->alleDaten = $this->query->fetchAll(PDO::FETCH_BOTH);
    $this->anzahl = $this->query->rowCount();
    return $this->alleDaten;
}

And then I echo the results in the table (in Table.php)

<?php
for($i=0;$i < $anzahl;$i++){    ?>
    <tr id="<?php echo $result2[$i][0]; ?>" name="j">
        <td class="t1"><input type='checkbox' name='multiDelete' class="bd"></td>
        <td class="t1" value="<?php echo $result2[$i][0]; ?>"><?php echo $result2[$i][0]; ?></td>
        <td class="t1"><?php echo $result2[$i][1]; ?></td>
        <td class="t1"><?php echo $result2[$i][2]; ?></td>
        <td class="t1"><?php echo $result2[$i][3]; ?></td>
        <td class="t1"><?php echo $result2[$i][4]; ?></td>
        <td class="t1"><?php echo $result2[$i][5]; ?></td>
        <td class="t1"><?php echo $result2[$i][6]; ?></td>
        <td class="t1"><?php echo $result2[$i][7]; ?></td>

    </tr>
<?php
}

?>

( I know it doesnt look nice ^^)

Dont know whether this will help

Edit: Maybe it fits a bit better to ur question now

How can i add filter options for my table out of my database in php

2 Answers2