Prevent duplication in a PHP filter

So I have an another problem with my filtering system. I am getting my filtered results when I choose an option from the first dropdown I have. But I also get below the filtered results a block where I see my options from the second dropdown (duplicated) and below that I have again ALL of my results I have in the database. How can I prevent this?

Here is my index.php

<!doctype html>
<html lang="de">
<head>

<!-- Required meta tags -->
<meta charset="utf-8">
<meta name="viewport" content="width=device-width, initial-scale=1, shrink-to-fit=no">

<!-- Stylesheets -->
<link rel="stylesheet" href="css/style.css">
<link rel="stylesheet" 
href="https://stackpath.bootstrapcdn.com/bootstrap/4.5.3/css/bootstrap.min.css" 
integrity="sha384-TX8t27EcRE3e/ihU7zmQxVncDAy5uIKz4rEkgIXeMed4M0jlfIDPvg6uqKI2xXr2" 
crossorigin="anonymous">

<!-- jQuery -->
<script src="https://ajax.googleapis.com/ajax/libs/jquery/3.4.1/jquery.min.js"></script>
<script src="js/jquery.min.js"></script>
<script>


  
  $(document).ready(function(){

    // Live anzeigen der 2. Ebene + 1 Ebene Ergebnisse
    $('#category').on('change', function(){
      var categoryID = $(this).val();
      if(categoryID){
        $.ajax({
          type:'POST',
          url:'ajaxdata.php',
          data:'category_id='+categoryID,
          success:function(html){
            $('#subcategory').html(html);
            $('#partner').html(html);
            $('#subcategory').prop('disabled', false);
          }
        });
      } else {
        $('#subcategory').html('<option value="">Zuerst Überkategorie auswählen!</option>');
      }
    }); 

    // Live Anzeigen der 3. Ebene + 2. Ebene Ergebnisse
    $('#subcategory').on('change', function(){
      var subcategoryID = $(this).val();
      if(subcategoryID){
        $.ajax({
          type:'POST',
          url:'ajaxdata.php',
          data:'subcategory_id='+subcategoryID,
          success:function(html){
            $('#subcategory2').html(html);
            $('#partner').html(html);
            $('#subcategory2').prop('disabled', false);
          }
        });
      } else {
        $('#subcategory2').html('<option value="">Zuerst Unterkategorie auswählen! 
    </option>');
      }
    });

    // Live anzeigen der 3. Ebene Ergebnisse
    $('#subcategory2').on('change', function(){
      var subcategory2ID = $(this).val();
      if(subcategory2ID){
        $.ajax({
          type:'POST',
          url:'ajaxdata.php',
          data:'subcategory2_id='+subcategory2ID,
          success:function(html){
            $('#partner').html(html);
          }
        });
      } else {
        $('#partner').html('<h1>Zuerst Unterkategorie auswählen!</h1>');
      }
    });
  });

</script>

</head>
<body>
<?php

  // Include connection.php
  include_once 'dbconfig.php';

  // Fetch all the category data
  $query = "SELECT * FROM categories WHERE status = 1 ORDER BY category_name ASC";
  $result = $connect->query($query);

?>

<!-- Category dropdown -->
<div class="container">
  <div class="row">
    

      <form action="" method="post">
        <div class="col">
          <select id="category" name="category" class="dropdown">

            <?php

              if($result->num_rows > 0){
                while($row = $result->FETCH_ASSOC()){
                  echo '<option value="'.$row['category_id'].'">'.$row['category_name'].'</option>';

                }
              } else {
                echo '<option value="">Nicht verfügbar!</option>';
              }
            ?>
          </select>
        </div>
        
        <div class="col">
          <!-- Subcategory dropdown -->
          <select id="subcategory" name="subcategory" class="dropdown" disabled>
            <option value="">Kategorie auswählen!</option>
          </select>
        </div>

        <div class="col">
          <!-- 2nd subcategory dropdown -->
          <select id="subcategory2" name="subcategory2" class="dropdown" disabled>
            <option value="">Kategorie auswählen!</option>
          </select>
        </div>
        
      </form>

    
  </div>
</div>

  <!-- Show all results -->
  <div id="all_results">
      <? /*
      $query = "SELECT * FROM partner WHERE status = 1 ORDER BY partner_name ASC";
      $result = $connect->query($query);
      if ($result->num_rows > 0){
      } */
      ?>
  </div>



<!-- Partner results -->
<div id="partner">
  <?php

    if($result->num_rows > 0){
      while($row = $result->FETCH_ASSOC()){
        ?>
        <div>
          <h1><? echo $row['partner_name'] ?></h1><br>
          <img src="<? echo $row['partner_logo'] ?>"><br>
          <a href="<? echo $row['partner_link'] ?>">Zum Unternehmen!</a>
        </div>
        <?php 
      }
    } else {
      echo '<p>Nichts gefunden!<p>';
    }
   ?>
</div>


<!-- jQuery library -->
<script src="js/jquery.min.js"></script>
<script src="https://ajax.googleapis.com/ajax/libs/jquery/3.4.1/jquery.min.js"></script>

JavaScript
​x
 
<!doctype html><html lang="de"><head>​<!-- Required meta tags --><meta charset="utf-8"><meta name="viewport" content="width=device-width, initial-scale=1, shrink-to-fit=no">​<!-- Stylesheets --><link rel="stylesheet" href="css/style.css"><link rel="stylesheet" href="https://stackpath.bootstrapcdn.com/bootstrap/4.5.3/css/bootstrap.min.css" integrity="sha384-TX8t27EcRE3e/ihU7zmQxVncDAy5uIKz4rEkgIXeMed4M0jlfIDPvg6uqKI2xXr2" crossorigin="anonymous">​<!-- jQuery --><script src="https://ajax.googleapis.com/ajax/libs/jquery/3.4.1/jquery.min.js"></script><script src="js/jquery.min.js"></script><script>​​    $(document).ready(function(){​    // Live anzeigen der 2. Ebene + 1 Ebene Ergebnisse    $('#category').on('change', function(){      var categoryID = $(this).val();      if(categoryID){        $.ajax({          type:'POST',          url:'ajaxdata.php',          data:'category_id='+categoryID,          success:function(html){            $('#subcategory').html(html);            $('#partner').html(html);            $('#subcategory').prop('disabled', false);          }        });      } else {        $('#subcategory').html('<option value="">Zuerst Überkategorie auswählen!</option>');      }    }); ​    // Live Anzeigen der 3. Ebene + 2. Ebene Ergebnisse    $('#subcategory').on('change', function(){      var subcategoryID = $(this).val();      if(subcategoryID){        $.ajax({          type:'POST',          url:'ajaxdata.php',          data:'subcategory_id='+subcategoryID,          success:function(html){            $('#subcategory2').html(html);            $('#partner').html(html);            $('#subcategory2').prop('disabled', false);          }        });      } else {        $('#subcategory2').html('<option value="">Zuerst Unterkategorie auswählen!     </option>');      }    });​    // Live anzeigen der 3. Ebene Ergebnisse    $('#subcategory2').on('change', function(){      var subcategory2ID = $(this).val();      if(subcategory2ID){        $.ajax({          type:'POST',          url:'ajaxdata.php',          data:'subcategory2_id='+subcategory2ID,          success:function(html){            $('#partner').html(html);          }        });      } else {        $('#partner').html('<h1>Zuerst Unterkategorie auswählen!</h1>');      }    });  });​</script>​</head><body><?php​  // Include connection.php  include_once 'dbconfig.php';​  // Fetch all the category data  $query = "SELECT * FROM categories WHERE status = 1 ORDER BY category_name ASC";  $result = $connect->query($query);​?>​<!-- Category dropdown --><div class="container">  <div class="row">    ​      <form action="" method="post">        <div class="col">          <select id="category" name="category" class="dropdown">​            <?php​              if($result->num_rows > 0){                while($row = $result->FETCH_ASSOC()){                  echo '<option value="'.$row['category_id'].'">'.$row['category_name'].'</option>';​                }              } else {                echo '<option value="">Nicht verfügbar!</option>';              }            ?>          </select>        </div>                <div class="col">          <!-- Subcategory dropdown -->          <select id="subcategory" name="subcategory" class="dropdown" disabled>            <option value="">Kategorie auswählen!</option>          </select>        </div>​        <div class="col">          <!-- 2nd subcategory dropdown -->          <select id="subcategory2" name="subcategory2" class="dropdown" disabled>            <option value="">Kategorie auswählen!</option>          </select>        </div>              </form>​      </div></div>​  <!-- Show all results -->  <div id="all_results">      <? /*      $query = "SELECT * FROM partner WHERE status = 1 ORDER BY partner_name ASC";      $result = $connect->query($query);      if ($result->num_rows > 0){      } */      ?>  </div>​​​<!-- Partner results --><div id="partner">  <?php​    if($result->num_rows > 0){      while($row = $result->FETCH_ASSOC()){        ?>        <div>          <h1><? echo $row['partner_name'] ?></h1><br>          <img src="<? echo $row['partner_logo'] ?>"><br>          <a href="<? echo $row['partner_link'] ?>">Zum Unternehmen!</a>        </div>        <?php       }    } else {      echo '<p>Nichts gefunden!<p>';    }   ?></div>​​<!-- jQuery library --><script src="js/jquery.min.js"></script><script src="https://ajax.googleapis.com/ajax/libs/jquery/3.4.1/jquery.min.js"></script>​

And this is my ajaxdata.php:

<?php

include_once 'dbconfig.php';

//Dropdown No. 1
if(!empty($_POST['category_id']))
{

  // Anzeigen von Ergebnisse - 1. Ebene
  $query = "SELECT * FROM partner WHERE category_id = ".$_POST['category_id']." AND status = 1 
ORDER BY partner_name ASC";
  $result = $connect->query($query);

  if($result->num_rows > 0)
  {
    while($row = $result->FETCH_ASSOC())
    {
    ?>        
      <div class="div_results">
        <h1><? echo $row['partner_name'] ?></h1><br>
        <img src="<? echo $row['partner_logo'] ?>"><br>
        <a href="<? echo $row['partner_link'] ?>">Zum Unternehmen!</a>
      </div>
    <?php
    }
  }

  // Anzeigen der Kategorien der 2. Ebene
  $query = "SELECT * FROM subcategories WHERE category_id = ".$_POST['category_id']." AND status = 1 ORDER BY subcategory_name ASC";
  $result = $connect->query($query);

  if($result->num_rows > 0)
  {
    echo '<option value="">Kategorie auswählen!</option>';

    while($row = $result->FETCH_ASSOC())
    {
      echo '<option value="'.$row['subcategory_id'].'">'.$row['subcategory_name'].'</option>';
    }
  } 

  // Anzeigen aller Ergebnisse
  $query = "SELECT DISTINCT * FROM partner WHERE status = 1 ORDER BY partner_name ASC";
  $result = $connect->query($query);

  if($_POST["category_id"] != "Alle")
  {
    while($row = $result->FETCH_ASSOC())
    {
    ?>        
      <div class="div_results">
        <h1><? echo $row['partner_name'] ?></h1><br>
        <img src="<? echo $row['partner_logo'] ?>"><br>
        <a href="<? echo $row['partner_link'] ?>">Zum Unternehmen!</a>
      </div>
    <?php
    }
  }   
  else {
    echo '<option value="">Nothing found!</option>';
  }
}

// Dropdown No. 2
elseif(!empty($_POST['subcategory_id']))
{

  // Anzeigen von Ergebnisse - 2. Ebene
  $query = "SELECT * FROM partner WHERE subcategory_id = ".$_POST['subcategory_id']." AND 
status = 1 ORDER BY  partner_name ASC";
  $result = $connect->query($query);

  if($result->num_rows > 0)
  {
    while($row = $result->FETCH_ASSOC())
    {
    ?>
    
      <div>
        <h1><? echo $row['partner_name'] ?></h1><br>
        <img src="<? echo $row['partner_logo'] ?>"><br>
        <a href="<? echo $row['partner_link'] ?>">Zum Unternehmen!</a>
      </div>
    <?php
    }
  }

  // Anzeigen der Kategorien der 3. Ebene
  $query = "SELECT * FROM subcategories2 WHERE subcategory_id = ".$_POST['subcategory_id']." 
AND status = 1 ORDER BY subcategory2_name ASC";
  $result = $connect->query($query);

  if($result->num_rows > 0)
  {
    echo '<option value="">Untekategorie auswählen!</option>';

    while($row = $result->FETCH_ASSOC())
    {
      echo '<option 
value="'.$row['subcategory2_id'].'">'.$row['subcategory2_name'].'</option>';
    }
  }
  else {
    echo '<option value="">Nichts gefunden!</option>';
  }
}


// Dropdown No. 3
elseif(!empty($_POST['subcategory2_id'])){

// Anzeigen von Ergebnisse - 3. Ebene (Letzte Ebene)
$query  = "SELECT * FROM partner WHERE subcategory2_id = ".$_POST['subcategory2_id']." AND status = 1 ORDER BY partner_name ASC";
$result = $connect->query($query);

if($result->num_rows > 0){

  while($row = $result->FETCH_ASSOC()){
    ?>
    <div>
      <h1><? echo $row['partner_name'] ?></h1><br>
      <img src="<? echo $row['partner_logo'] ?>"><br>
      <a href="<? echo $row['partner_link'] ?>">Zum Unternehmen!</a>
    </div>
    <?php
  }
} else {
  echo 'Nothing found!';
}
}
?>

JavaScript
 
<?php​include_once 'dbconfig.php';​//Dropdown No. 1if(!empty($_POST['category_id'])){​  // Anzeigen von Ergebnisse - 1. Ebene  $query = "SELECT * FROM partner WHERE category_id = ".$_POST['category_id']." AND status = 1 ORDER BY partner_name ASC";  $result = $connect->query($query);​  if($result->num_rows > 0)  {    while($row = $result->FETCH_ASSOC())    {    ?>              <div class="div_results">        <h1><? echo $row['partner_name'] ?></h1><br>        <img src="<? echo $row['partner_logo'] ?>"><br>        <a href="<? echo $row['partner_link'] ?>">Zum Unternehmen!</a>      </div>    <?php    }  }​  // Anzeigen der Kategorien der 2. Ebene  $query = "SELECT * FROM subcategories WHERE category_id = ".$_POST['category_id']." AND status = 1 ORDER BY subcategory_name ASC";  $result = $connect->query($query);​  if($result->num_rows > 0)  {    echo '<option value="">Kategorie auswählen!</option>';​    while($row = $result->FETCH_ASSOC())    {      echo '<option value="'.$row['subcategory_id'].'">'.$row['subcategory_name'].'</option>';    }  } ​  // Anzeigen aller Ergebnisse  $query = "SELECT DISTINCT * FROM partner WHERE status = 1 ORDER BY partner_name ASC";  $result = $connect->query($query);​  if($_POST["category_id"] != "Alle")  {    while($row = $result->FETCH_ASSOC())    {    ?>              <div class="div_results">        <h1><? echo $row['partner_name'] ?></h1><br>        <img src="<? echo $row['partner_logo'] ?>"><br>        <a href="<? echo $row['partner_link'] ?>">Zum Unternehmen!</a>      </div>    <?php    }  }     else {    echo '<option value="">Nothing found!</option>';  }}​// Dropdown No. 2elseif(!empty($_POST['subcategory_id'])){​  // Anzeigen von Ergebnisse - 2. Ebene  $query = "SELECT * FROM partner WHERE subcategory_id = ".$_POST['subcategory_id']." AND status = 1 ORDER BY  partner_name ASC";  $result = $connect->query($query);​  if($result->num_rows > 0)  {    while($row = $result->FETCH_ASSOC())    {    ?>          <div>        <h1><? echo $row['partner_name'] ?></h1><br>        <img src="<? echo $row['partner_logo'] ?>"><br>        <a href="<? echo $row['partner_link'] ?>">Zum Unternehmen!</a>      </div>    <?php    }  }​  // Anzeigen der Kategorien der 3. Ebene  $query = "SELECT * FROM subcategories2 WHERE subcategory_id = ".$_POST['subcategory_id']." AND status = 1 ORDER BY subcategory2_name ASC";  $result = $connect->query($query);​  if($result->num_rows > 0)  {    echo '<option value="">Untekategorie auswählen!</option>';​    while($row = $result->FETCH_ASSOC())    {      echo '<option value="'.$row['subcategory2_id'].'">'.$row['subcategory2_name'].'</option>';    }  }  else {    echo '<option value="">Nichts gefunden!</option>';  }}​​// Dropdown No. 3elseif(!empty($_POST['subcategory2_id'])){​// Anzeigen von Ergebnisse - 3. Ebene (Letzte Ebene)$query  = "SELECT * FROM partner WHERE subcategory2_id = ".$_POST['subcategory2_id']." AND status = 1 ORDER BY partner_name ASC";$result = $connect->query($query);​if($result->num_rows > 0){​  while($row = $result->FETCH_ASSOC()){    ?>    <div>      <h1><? echo $row['partner_name'] ?></h1><br>      <img src="<? echo $row['partner_logo'] ?>"><br>      <a href="<? echo $row['partner_link'] ?>">Zum Unternehmen!</a>    </div>    <?php  }} else {  echo 'Nothing found!';}}?>​

What am I doing wrong so this shows everything when I am filtering

Answer

For the first problem (returning an unfiltered set of results as well as the filtered set), it’s simply that you are running two versions of the SELECT * FROM partner... query, and outputting the results from both. The first version is filtered, but the second version isn’t. You just need to remove the second version – it’s not clear why that’s there to begin with.

For the second problem (outputting the dropdown options after the results), the issue is that you’re just outputting one big stream of HTML. Then you’ve written $('#subcategory').html(html); $('#partner').html(html); which attempts to put the same single stream of HTML into both parts of your page! The first command likely doesn’t entirely work correctly because you’re trying to put divs inside a select. And the second one prints everything that was output by the PHP – it has no way of telling which bit you wanted to print there, and which bit was intended for the dropdown. You gave it no way to differentiate.

It would make more sense to return a JSON object from the PHP, with two properties – one property containing the HTML for the search result, and one containing the HTML for the dropdown. Then the Javascript can easily read the correct bit into each part of your page separately.

Something like this should work I think (although obviously it’s not easy for me to test it):

JavaScript:

    $('#category').on('change', function(){
      var categoryID = $(this).val();
      if(categoryID){
        $.ajax({
          type:'POST',
          url:'ajaxdata.php',
          data:'category_id='+categoryID,
          dataType: "json",
          success:function(response){
            $('#subcategory').html(response.subcategories);
            $('#partner').html(response.results);
            $('#subcategory').prop('disabled', false);
          }
        });
      } else {
        $('#subcategory').html('<option value="">Zuerst Überkategorie auswählen!</option>');
      }
    });

JavaScript
 
    $('#category').on('change', function(){      var categoryID = $(this).val();      if(categoryID){        $.ajax({          type:'POST',          url:'ajaxdata.php',          data:'category_id='+categoryID,          dataType: "json",          success:function(response){            $('#subcategory').html(response.subcategories);            $('#partner').html(response.results);            $('#subcategory').prop('disabled', false);          }        });      } else {        $('#subcategory').html('<option value="">Zuerst Überkategorie auswählen!</option>');      }    }); ​

PHP:

//Dropdown No. 1
if(!empty($_POST['category_id']))
{

  // Anzeigen von Ergebnisse - 1. Ebene
  $query = "SELECT * FROM partner WHERE category_id = ".$_POST['category_id']." AND status = 1 
ORDER BY partner_name ASC";
  $result = $connect->query($query);
  $resultHTML = "";

  if($result->num_rows > 0)
  {
    while($row = $result->FETCH_ASSOC())
    {
      $resultHTML .=        
      '<div class="div_results">
        <h1>'.$row['partner_name'].'</h1><br>
        <img src="'.$row['partner_logo'].'"><br>
        <a href="'.$row['partner_link'].'">Zum Unternehmen!</a>
      </div>';
    }
  }

  // Anzeigen der Kategorien der 2. Ebene
  $query = "SELECT * FROM subcategories WHERE category_id = ".$_POST['category_id']." AND status = 1 ORDER BY subcategory_name ASC";
  $result = $connect->query($query);
  $subCategoryHTML = "";

  if($result->num_rows > 0)
  {
    $subCategoryHTML .= '<option value="">Kategorie auswählen!</option>';

    while($row = $result->FETCH_ASSOC())
    {
      $subCategoryHTML .= '<option value="'.$row['subcategory_id'].'">'.$row['subcategory_name'].'</option>';
    }
  }

  $returnData = array("results" => $resultHTML, "subcategories" => $subCategoryHTML);
  echo json_encode($returnData);
}

JavaScript
 
//Dropdown No. 1if(!empty($_POST['category_id'])){​  // Anzeigen von Ergebnisse - 1. Ebene  $query = "SELECT * FROM partner WHERE category_id = ".$_POST['category_id']." AND status = 1 ORDER BY partner_name ASC";  $result = $connect->query($query);  $resultHTML = "";​  if($result->num_rows > 0)  {    while($row = $result->FETCH_ASSOC())    {      $resultHTML .=              '<div class="div_results">        <h1>'.$row['partner_name'].'</h1><br>        <img src="'.$row['partner_logo'].'"><br>        <a href="'.$row['partner_link'].'">Zum Unternehmen!</a>      </div>';    }  }​  // Anzeigen der Kategorien der 2. Ebene  $query = "SELECT * FROM subcategories WHERE category_id = ".$_POST['category_id']." AND status = 1 ORDER BY subcategory_name ASC";  $result = $connect->query($query);  $subCategoryHTML = "";​  if($result->num_rows > 0)  {    $subCategoryHTML .= '<option value="">Kategorie auswählen!</option>';​    while($row = $result->FETCH_ASSOC())    {      $subCategoryHTML .= '<option value="'.$row['subcategory_id'].'">'.$row['subcategory_name'].'</option>';    }  }​  $returnData = array("results" => $resultHTML, "subcategories" => $subCategoryHTML);  echo json_encode($returnData);}​

Advertisement

Answer