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>
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!';
}
}
?>
Thanks
Advertisement
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();
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