Single and multiple data filters in php

Question

I need to create filter function for this 3 information: Category, Price and Discount for users in order to filter either 1 of the information or any of the 2 information together, or all 3 information together.

I used this method to filter single information and it successfully displayed the results, but when I tried to use this method to filter any of the 2 or 3 information together, it failed to filter everything.

<?php
//filter category only
if (isset($_GET['f_category']) && $_GET['f_category'] != ""){

    $f_category = $_GET['f_category'];
    $sql = "SELECT * FROM post_ads WHERE sup_category='$f_category'";    
}

//filter price only
if (isset($_GET['min_price']) && $_GET['min_price'] != "" && $_GET['max_price'] && $_GET['max_price'] != "") {

    $min_price = $_GET['min_price'];
    $max_price = $_GET['max_price'];
    $sql = "SELECT * FROM post_ads WHERE sup_price>='$min_price' AND sup_price<='$max_price'"; 
}

// filter discount only
if (isset($_GET['f_discount']) && $_GET['f_discount'] != ""){

    $f_discount = $_GET['f_discount'];
    $sql = "SELECT * FROM post_ads WHERE sup_discount='$f_discount'";    
}

//filter category and price
if (isset($_GET['f_category']) && $_GET['f_category'] != "" || $_GET['min_price'] && $_GET['min_price'] != "" && $_GET['max_price'] && $_GET['max_price'] != ""){

    $f_category = $_GET['f_category'];
    $min_price = $_GET['min_price'];
    $max_price = $_GET['max_price'];
    $sql = "SELECT * FROM post_ads WHERE sup_category='$f_category'
    AND sup_price>='$min_price' AND sup_price<='$max_price'";        
}

//filter category and discount
if (isset($_GET['f_category']) && $_GET['f_category'] != "" || $_GET['f_discount']
&& $_GET['f_discount'] != ""){

    $f_category = $_GET['f_category'];
    $f_discount = $_GET['f_discount'];
    $sql = "SELECT * FROM post_ads WHERE sup_category='$f_category'
    AND sup_discount='$f_discount'";   
}

if(isset($sql)){

$result = mysql_query($sql, $con1);
while($rows=mysql_fetch_array($result))
 {
   //display results
 }
}?>

Can I know what is the problem and how do I fix it?

Professor Abronsius · Accepted Answer · 2016-01-02T16:29:05.060

You could add each clause to an array, based upon the criteria in original and combine them at the end perhaps..

<?php
    $clauses=array();

    if( isset( $_GET['f_category'] ) && !empty( $_GET['f_category'] ) ){
        $clauses[] = "`sup_category` = '{$_GET['f_category']}'";   
    }
    if ( isset( $_GET['min_price'], $_GET['max_price'] ) && !empty( $_GET['min_price'] )  && !empty( $_GET['max_price'] ) ) {
        $clauses[]="`sup_price` >= '{$_GET['min_price']}'";
        $clauses[]="`sup_price` <= '{$_GET['max_price']}'";
    }
    if ( isset( $_GET['f_discount'] ) && !empty( $_GET['f_discount'] ) ){
        $clauses[]="`sup_discount` = '{$_GET['f_discount']}'";   
    }

    $where = !empty( $clauses ) ? ' where '.implode(' and ',$clauses ) : '';
    $sql = "SELECT * FROM `post_ads` " . $where; 

    echo $sql;

    if(isset($sql)){

         $result = mysql_query($sql, $con1);
         while($rows=mysql_fetch_array($result)){
           /*display results*/
         }
    }
?>

Running the above ( edited version ) with this url

https://locahost/stack/sql?f_category=bananas&min_price=200&max_price=500&f_discount=32

results in a query that looks like:

SELECT * FROM `post_ads` where `sup_category` = 'bananas' 
   and `sup_price` >= '200' and `sup_price` <= '500' and `sup_discount` = '32'

This all said - without care your code is very vulnerable to sql injection - the use of the mysql_* suite of functions are deprecated and their use is strongly discouraged. Before getting in too deep, change over to mysqli with prepared statements - avoid the heartache '-)

What did the / does the final sql query look like if you echo it out to screen? I just tried and can see duplicate entries in the query with the test data I used... so it needs refining — Professor Abronsius, Jan 02 '16 at 16:14
I just want to echo image, title and price from database that matched with the filter information. echo ''; echo $rows['ad_title']; echo $rows['sup_price']; — yyii, Jan 02 '16 at 16:35

score 0 · Answer 2 · answered Jan 02 '16 at 15:12

For simplicity's sake, rewrite your code so that it processes the input first and THEN build your query. This makes it a lot more readable for both of us and you will most likely resolve your issue along the way.

Additionally, please refrain of using mysql_, it is unsafe and has been replaced by mysqli_ methods.

Captain Red · Answer 3 · 2016-01-02T15:56:34.193

-1

I suppose your URL will be in the following format:

1> example.com?f_category=test // When only f_category filter is selected

2> example.com?f_category=test&min_price=50 // When f_category and min_price are filter are selected. And so on.....

So, you can simply code this as:

<?php


$querySubString = "1 = 1";

if (isset($_GET['f_category']) && $_GET['f_category'] != ""){

$f_category = $_GET['f_category'];
$querySubString .= " AND sup_category = '$f_category' ";
}

if (isset($_GET['min_price']) && $_GET['min_price'] != ""){

$min_price = $_GET['min_price'];
$querySubString .= "AND sup_price >= '$min_price' ";
}

if (isset($_GET['max_price']) && $_GET['max_price'] != ""){

$max_price = $_GET['max_price'];
$querySubString .= "AND sup_price <= '$max_price' ";
}

if (isset($_GET['f_discount']) && $_GET['f_discount'] != ""){

$f_discount = $_GET['f_discount'];
$querySubString .= " AND sup_discount= '$f_discount' ";
}
 $sql = "SELECT * FROM post_ads WHERE $querySubString ";

if(isset($sql)){

$result = mysql_query($sql, $con1);
while($rows=mysql_fetch_array($result))
 {
   //display results
 }


?>

edited Jan 02 '16 at 15:56

answered Jan 02 '16 at 15:07

Captain Red

1,171
2
16
27

I got this error, Warning: mysql_fetch_array() expects parameter 1 to be resource, boolean given in C:.. on line 160. Line 160 is while($rows=mysql_fetch_array($result)) – yyii Jan 02 '16 at 15:31
I missed AND at each $querySubstring .= //edited. Can u try now? – Captain Red Jan 02 '16 at 15:34
Do you have the code for connection on top of the code I suggested? The $con1 value?? – Captain Red Jan 02 '16 at 15:42
You mean this one? $con1 = mysql_connect("localhost", "root", "")or die(mysql_error()); $db = mysql_select_db("userlogin", $con1)or die(mysql_error()); – yyii Jan 02 '16 at 15:46
Yes, and there were extra curly braces. Can you try now? – Captain Red Jan 02 '16 at 15:57
Ya, I have removed the curly braces, yet still not able to solve. – yyii Jan 02 '16 at 16:07
You might want to switch over to msqli_* Refer to http://stackoverflow.com/questions/2973202/mysql-fetch-array-expects-parameter-1-to-be-resource-or-mysqli-result-boole – Captain Red Jan 02 '16 at 16:10
Okay, will try to switch over. Thanks for the info. – yyii Jan 02 '16 at 16:38

Single and multiple data filters in php

3 Answers3