phpjquerymysqlfilteradvanced-search
Product Refine filter by categories, price and regions
I am still learning web development and I am working on a project in order to learn how to develop a complete website with all functionalities. I am currently trying to implement the product refine filter by categories, condition, region and price. I started doing the filter according to categories and regions only, to see how I could go about but my attempt to do it failed. Whenever I check a box, itfetches the categoryId in the url but gives me an error about sql syntax.
The error message received:
I have 4 tables ads table with adId, title, catId, regionId, price, condition, description category table with catId, catName region table with regionId, regionName images table with imageId,adId,path, preview
Can anyone help me to find a solution to my problem?
Here is my class file
public function getAllCat(){
$query = "SELECT * FROM category ORDER BY catId";
$result = $this->db->select($query);
return $result;
}
public function getAllRegion(){
$query = "SELECT * FROM region ORDER BY regionId";
$result = $this->db->select($query);
return $result;
}
public function getAllAds(){
$query = "SELECT * FROM ads ORDER BY adId DESC";
$result = $this->db->select($query);
return $result;
}
public function getPreviewImage($id){
$query = "SELECT * FROM images WHERE adId = $id AND preview = '1' ";
$image = $this->db->select($query);
return $image;
}
Here is my allads.php file
<?php
//declaration array varible
$filtercategory = array();
$filterregion = array();
//finding query string value
if(isset($_REQUEST['filtercategory'])){
//query string value to array and removing empty index of array
$filtercategory = array_filter(explode("-",$_REQUEST['filtercategory']));
}
if(isset($_REQUEST['filterregion'])){
$filterregion = array_filter(explode("-",$_REQUEST['filterregion']));
}
?>
<main class="cd-main-content">
<div class="Ads-Container">
<?php
//Pagination start
$query = "SELECT COUNT(adId) FROM ads";
$result = $db->select($query);
$row = mysqli_fetch_row($result);
// Here we have the total row count
$rows = $row[0];
// This is the number of results we want displayed per page
$page_rows = 20;
// This tells us the page number of our last page
$last = ceil($rows/$page_rows);
// This makes sure $last cannot be less than 1
if($last < 1){
$last = 1;
}
// Establish the $pagenum variable
$pagenum = 1;
// Get pagenum from URL vars if it is present, else it is = 1
if(isset($_GET['pn'])){
$pagenum = preg_replace('#[^0-9]#', '', $_GET['pn']);
}
// This makes sure the page number isn't below 1, or more than our $last page
if ($pagenum < 1) {
$pagenum = 1;
} else if ($pagenum > $last) {
$pagenum = $last;
}
// This sets the range of rows to query for the chosen $pagenum
$limit = 'LIMIT ' .($pagenum - 1) * $page_rows .',' .$page_rows;
?>
<?php
$query = "SELECT * FROM ads ORDER BY adId DESC $limit";
//filter query start
if(!empty($filtercategory)){
$categorydata =implode("','",$filtercategory);
$query .= " and catId in('$categorydata')";
}
if(!empty($filterregion)){
$regiondata =implode("','",$filterregion);
$query .= " and regionId in('$regiondata')";
}
//filter query end
$post = $db->select($query);
if($post){
while($result = $post->fetch_assoc()){
?>
<div class="ads-column_2">
<div class="ads-column-thumbnail">
<?php
$preview = $ad->getPreviewImage($result["adId"]);
if($preview){
while($rresult = $preview->fetch_assoc()){
?>
<img src="/afro-circle/<?php echo $rresult['path']?>" alt="" class="image-responsive">
<?php } } ?>
<div class="ads-preview-details">
<center>
<h4><a href="addetails.php?adid=<?php echo $result['adId']; ?>"><?php echo $result['title']; ?></a></h4>
<h4 class="">FCFA <?php echo number_format($result['price']); ?></h4>
</center>
</div>
<div class="space-ten"></div>
<div class="btn-ground text-center">
<a href="addetails.php?adid=<?php echo $result['adId']; ?>"><button type="button" class="btn btn-primary" data-toggle="modal" data-target="#product_view">Quick View</button></a>
</div>
<div class="space-ten"></div>
</div>
</div>
<?php } } ?>
</div>
<!-- /.row -->
<div class="row" style="text-align: center;">
<?php
// This shows the user what page they are on, and the total number of pages
$textline1 = "Testimonials (<b>$rows</b>)";
$textline2 = "Page <b>$pagenum</b> of <b>$last</b>";
// Establish the $paginationCtrls variable
$paginationCtrls = '';
// If there is more than 1 page worth of results
if($last != 1){
/* First we check if we are on page one. If we are then we don't need a link to
the previous page or the first page so we do nothing. If we aren't then we
generate links to the first page, and to the previous page. */
if ($pagenum > 1) {
$previous = $pagenum - 1;
$paginationCtrls .= '<a href="'.$_SERVER['PHP_SELF'].'?pn=1">First Page</a> ';
$paginationCtrls .= '<a href="'.$_SERVER['PHP_SELF'].'?pn='.$previous.'">Previous</a> ';
// Render clickable number links that should appear on the left of the target page number
for($i = $pagenum-4; $i < $pagenum; $i++){
if($i > 0){
$paginationCtrls .= '<a href="'.$_SERVER['PHP_SELF'].'?pn='.$i.'">'.$i.'</a> ';
}
}
}
// Render the target page number, but without it being a link
$paginationCtrls .= '<span>'.$pagenum.'</span> ';
// Render clickable number links that should appear on the right of the target page number
for($i = $pagenum+1; $i <= $last; $i++){
$paginationCtrls .= '<a href="'.$_SERVER['PHP_SELF'].'?pn='.$i.'">'.$i.'</a> ';
if($i >= $pagenum+4){
break;
}
}
// This does the same as above, only checking if we are on the last page, and then generating the "Next"
if ($pagenum != $last) {
$next = $pagenum + 1;
$paginationCtrls .= ' <a href="'.$_SERVER['PHP_SELF'].'?pn='.$next.'">Next</a> ';
$paginationCtrls .= ' <a href="'.$_SERVER['PHP_SELF'].'?pn='.$last.'">Last Page</a> ';
}
}
?>
<div class="pagination">
<div id="pagination_controls"><?php echo $paginationCtrls; ?></div>
</div>
</div>
<!-- /.row -->
<div class="cd-filter filter-is-visible">
<form>
<div class="cd-filter-block">
<h4>Categories <span class="spanbrandcls" style="float:right; visibility:hidden;"><a href="javascript:void(0);"><img src="refine/images/reset.png" alt="reset" title="reset"></a></span></h4>
<ul class="cd-filter-content cd-filters list">
<?php
$getCategory = $category->getAllCat();
if($getCategory){
while($result = $getCategory->fetch_assoc()){
?>
<li>
<input type="checkbox" class="filter filtercategory" value="<?php echo $result['catId']; ?>" <?php if(in_array($result['catName'],$filtercategory)){ echo"checked"; } ?> >
<label class="checkbox-label" id="Category" ><?php echo $result['catName']; ?></label>
</li>
<?php } } ?>
</ul> <!-- cd-filter-content -->
</div> <!-- cd-filter-block -->
<div class="cd-filter-block">
<h4>Size <span class="spansizecls" style="float:right; visibility:hidden;"><a href="javascript:;"><img src="refine/images/reset.png" alt="reset" title="reset"></a></span></h4>
<div class="cd-filter-content">
<div class="cd-select cd-filters">
<select class="filter scheck" name="subcatId">
<option data-type="sizes" value="">Item Condition</option>
<option data-type="sizes" value="1">New</option>
<option data-type="sizes" value="2">Used</option>
</select>
</div> <!-- cd-select -->
</div> <!-- cd-filter-content -->
</div> <!-- cd-filter-block -->
<div class="cd-filter-block">
<h4>Region <span class="spancolorcls" style="float:right; visibility:hidden;"><a href="javascript:;"><img src="refine/images/reset.png" alt="reset" title="reset"></a></span></h4>
<ul class="cd-filter-content cd-filters list">
<?php
$getRegion = $region->getAllRegion();
if($getRegion){
while($result = $getRegion->fetch_assoc()){
?>
<li>
<input type="radio" class="filter filterregion" value="<?php echo $result['regionId']; ?>" <?php if(in_array($result['regionName'],$filterregion)){ echo"checked"; } ?>>
<label class="radio-label" for="radio1"><?php echo $result['regionName']; ?></label>
</li>
<?php } } ?>
</ul> <!-- cd-filter-content -->
</div> <!-- cd-filter-block -->
</form>
<a href="#0" class="cd-close"><i class="fa fa-close"></i> Close</a>
</div>
<a href="#0" class="cd-filter-trigger"><i class="fa fa-filter"></i> Search by:</a>
<div class="clear"></div>
Here is my JS
<script>
$(function(){
$('.filter').click(function(){
var filtercategory = multiple_values('filtercategory');
var filterregion = multiple_values('filterregion');
var url ="allads.php?filtercategory="+filtercategory+"&filterregion="+filterregion;
window.location=url;
});
});
function multiple_values(inputclass){
var val = new Array();
$("."+inputclass+":checked").each(function() {
val.push($(this).val());
});
return val.join('-');
}
Solution
The problem lies in the following section of the code:
$query = "SELECT * FROM ads ORDER BY adId DESC $limit";
//filter query start
if(!empty($filtercategory)){
$categorydata =implode("','",$filtercategory);
$query .= " and catId in('$categorydata')";
}
if(!empty($filterregion)){
$regiondata =implode("','",$filterregion);
$query .= " and regionId in('$regiondata')";
}
The code insert the filter criteria at the en of the sql statement, which means after the limit. So, the actual sql statement looks something like as follows:
SELECT * FROM ads ORDER BY adId DESC LIMIT 10, 10 and catId in('4')
The correct sql code would look something like:
SELECT * FROM ads WHERE catId in('4') ORDER BY adId DESC LIMIT 10, 10
You must modify your php code to generate valid sql code. While debugging, add code that prints out the full sql statement to see the full sql ststement before it is executed.
Also, your code is wide open to sql injection attacks. Pls use prepared statements or other techniques to prevent such attacks.