Fetch database results by choosing dropdown option

I have a small problem with my filtering system I’m creating. I have a dropdown with database categories and one category is “Alle” (english: All) and is chosen by default. This option is not in the database but a separate html option.

This is the filter

When I open the filter first I see all my database results (because i have a PHP code to show all results if nothing happens at first) but when I choose a random category and then choose “Alle” again there is just an error.

Here is the error

Someone know a script (ajax) and php code to show all results by clicking on “all”.

Here are my codes:

index.php

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

<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 -->
 <form action="" method="post">
  <select id="category" name="category">
    <option value="all">Alle</option>

    <?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>

  <!-- Subcategory dropdown -->
  <select id="subcategory" name="subcategory" disabled>
    <option value="">Kategorie auswählen!</option>
  </select>

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



  <!-- 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>
</body>
</html>

JavaScript
​x
 
<!doctype html><html lang="de"><head>​<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 --> <form action="" method="post">  <select id="category" name="category">    <option value="all">Alle</option>​    <?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>​  <!-- Subcategory dropdown -->  <select id="subcategory" name="subcategory" disabled>    <option value="">Kategorie auswählen!</option>  </select>​  <!-- 2nd subcategory dropdown -->  <select id="subcategory2" name="subcategory2" disabled>    <option value="">Kategorie auswählen!</option>  </select> </form>​​​  <!-- 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></body></html>​

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>';
  }
}
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>';  }}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!';}}​?>​

Thanks

Answer

You need to write an if statement in your PHP to check for this special case – if the category ID is “all” then you must not include the category_id = ... part of the query at all in the SQL, because in that case you actually don’t want any restrictions on that field.

e.g.

$query = "SELECT * FROM partner WHERE"; 
if ($_POST['category_id'] != "all") { $query .= " category_id = ? AND"; }  
$query .= " status = 1 ORDER BY  partner_name ASC";

$stmt = $connect->prepare($query);
if ($_POST['category_id'] != "all") { $stmt->bind_param("i", $_POST["category_id"]); }
$stmt->execute();
$result = $stmt->get_result();

JavaScript
 
$query = "SELECT * FROM partner WHERE"; if ($_POST['category_id'] != "all") { $query .= " category_id = ? AND"; }  $query .= " status = 1 ORDER BY  partner_name ASC";​$stmt = $connect->prepare($query);if ($_POST['category_id'] != "all") { $stmt->bind_param("i", $_POST["category_id"]); }$stmt->execute();$result = $stmt->get_result();​

N.B. I have used a prepared statement and parameterisation here, instead of concatenating $_POST["category_id"] directly into the SQL string. This will protect your code from both SQL injection attacks and certain kinds of unexpected SQL syntax errors which can arise from the content of the variable data. You can find more examples of writing secure queries here

Advertisement

Answer