yahoo5000 yahoo5000 - 6 months ago 18
SQL Question

Filter system not working

Hello so i made my filter system its a simple one only location and price range are set for now everything looks like this : Screen shot
so the problem i have is that min and max price range filter doesn't work location filter are working as it should be the only problem i face is min and max price got no error or warning nothing but eater nothing happens to.

php code above :

$cat1 = '';

if(isset($_GET["catid"])){

$p1 = '';
$p2 = '';
$catid = $_GET["catid"];
$l1 = substr($catid,0,1);
$l2 = substr($catid,1,1);
$p1 = "CAT".$l1;
if(!empty($l2)){
$p2 = "CAT".$l1."-".$l2;
$p3 = $p2;
}
$cat1 = @$lang[$p1];
$cat2 = @$lang[$p2];
}

$postid = '';
$userid = '';
$pricemin = '';
$pricemax = '';
$location = '';

if(isset($_POST["filter"])){

$pricemin = $_POST["min"];
$pricemax = $_POST["max"];
$location = $_POST["location"];
}


main page code :

<div class="fp">
<div class="filter">
<b style="padding-left: 10px;">Filters:</b>
<form class="filterform" action="" method="post"><br>
Location: <br>
<input name="location" ><br>
Price Range:<br>
Min:<input type="text" name="min" size="5"> Max:<input type="text" name="max" size="5"><br><br>
<input class="submit-button" type="submit" name="filter" value="Filter">
</form>
</div>
<div class="posts">
<div id="adcat"><?php
if(!empty($cat2)){
?>
<a href="cat.php?catid=<?php echo $l1; ?>" ><?php echo $cat1." ยป "; ?></a><span><?php echo $cat2; ?></span>
<?php
} else {
echo "<font color='grey'>".$cat1."</font>";
}
?>
</div><br><br>
<div id="detailformscat">
<?php
if(empty($p1) && empty($p2)){
$sql = "SELECT * FROM posts p JOIN images i ON p.id = i.postid ";
if(!empty($location)){
$sql .= "AND location='$location'";
}
if(!empty($min)){
$sql.= "AND price>='$min' ";
}
if(!empty($max)){
$sql.= "AND price<='$max' ";
}
} else if(!empty($p2)){
$sql = "SELECT * FROM posts p JOIN images i ON p.id = i.postid WHERE catid='$p2' ";
if(!empty($location)){
$sql .= "AND location='$location'";
}
if(!empty($min)){
$sql.= "AND price>='$min' ";
}
if(!empty($max)){
$sql.= "AND price<='$max' ";
}
} else {
$sql = "SELECT * FROM posts p JOIN images i ON p.id = i.postid WHERE p.catid LIKE '$p1%' ";
if(!empty($location)){
$sql .= "AND location='$location'";
}
if(!empty($min)){
$sql.= "AND price>='$min' ";
}
if(!empty($max)){
$sql.= "AND price<='$max' ";
}
}
$res = mysqli_query($connect,$sql);
while ($row = mysqli_fetch_assoc($res)) {
$postid = $row["postid"];
?>
<div id="ads">
<div id="adfavcat">
<?php if(!isset($_SESSION["userid"])) { ?>
<a href="post.php?login=false" ><img src="image/+fav.png" width="40px" height="40px"></a>
<?php } else {

$userid = $_SESSION["userid"];
$sql2 = "SELECT * FROM fav WHERE userid='$userid' AND postid='$postid' ";
$res2 = mysqli_query($connect,$sql2);
$rowcount = mysqli_num_rows($res2);

if ($rowcount > 0){ ?>
<a href="cat.php?catid=<?php echo $catid; ?>&delete&id=<?php echo $postid?>" ><img src="image/xfav.png" width="40px" height="40px"></a>
<?php
} else { ?>
<a href="cat.php?catid=<?php echo $catid; ?>&add&id=<?php echo $postid; ?>" ><img src="image/+fav.png" width="40px" height="40px"></a>
<?php }
} ?>

</div>
<div id="titlepic">
<a href="review.php?id=<?php echo $postid; ?>"><?php echo $row["title"]; ?></a><br>
<a href="review.php?id=<?php echo $postid; ?>"><img src="<?php if(!empty($row["path1"])) { echo $row["path1"]; } else echo "image/noimage.png"; ?>" height="100px" width="150px"></a>
</div>
<div id="datescat">
<b>Date Added:</b> <?php echo date('m/d/Y H:i', $row["dateadded"]); ?><br>
<b>Renew Date:</b> <?php if($row["renewdate"] > 0){ echo date('m/d/Y H:i', $row["renewdate"]); } ?><br>
<b>Location:</b> <?php echo $row["location"]; ?><br>
<b>Price:</b> <?php echo $row["price"]."&pound"; ?><br>

</div>
</div>
<hr width="100%">
<?php
}

?>
</div>
</div>
</div>

Answer

I think it's because you are treating the price as a string, in the sql query you wrote

$sql.= "AND price>='$min' ";

Try with cast/sanitize/filter input variables $min & $max to integers and removing the quotes.

--- by the way, I personally would also change some things:

  1. use atom instead of brackets
  2. use an ORM and remove the query from the html page (the view)
  3. if 2 is not possible, try to move all php logic to the php file instead of the html part
  4. remove all that IFs and try to write a code without lots of repetitions

You are also joining the tables instead of filtering, try changing

$sql = "SELECT * FROM posts p JOIN images i ON p.id = i.postid ";

with

$sql = "SELECT * FROM posts p JOIN images i ON p.id = i.postid WHERE p.id > 0 ";

change

$pricemin = '';
$pricemax = '';

with

$min = '';
$max = '';