How to implement multiple search filters in php

Question

I m trying to implement filters in php and mysql to search record in database....Filters should be like if among 6 filters only two is selected query will perform "AND" operation for these two and fetch data from database and if three then it will perform "AND" operation for these three filters..... one way to implement is to check for each filter like.

if (isset($_GET['name'] && isset($_GET['city'])) { 
    // perform AND for these two
} elseif(isset($_GET['name'] && (isset($_GET['age'])) {
    // perform AND for these three
}

// and so on ...

But the problem is if i have 6 filters then i have to create 64 combinations for it... I m thinking about that is there any alternative solution exists for it ?

pouyan · Accepted Answer · 2023-05-02 20:38:34Z

8

This could work, assuming that your $_GET keys match columns in your table:

$query = "SELECT * FROM table";

$filtered_get = array_filter($_GET); // removes empty values from $_GET

if (count($filtered_get)) { // not empty
    $query .= " WHERE";

    $keynames = array_keys($filtered_get); // make array of key names from $filtered_get

    foreach($filtered_get as $key => $value)
    {
       $query .= " $keynames[$key] = '$value'";  // $filtered_get keyname = $filtered_get['keyname'] value
       if (count($filtered_get) > 1 && (count($filtered_get)-1 > $key)) { // more than one search filter, and not the last
          $query .= " AND";
       }
    }
}
$query .= ";"

$query = "SELECT * FROM table WHERE name = 'name' AND city = 'city' AND age = 'age';"

I think this may work, although this code sample does not include sanitization against SQL injection, so you should probably add something to scrub potentially dangerous input

Updated: added $filtered_get = array_filter($_GET); to filter out any empty fields from $_GET array

Updated: I edited the if code condition to remove extra "AND" at the end of query "(count($filtered_get)-1 > $key"

edited May 2, 2023 at 20:38

pouyan

34 bronze badges

answered Feb 9, 2019 at 13:56

tshimkus

1,1692 gold badges18 silver badges25 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Inam Over a year ago

what if an empty input text value is passed here than what will happen ."AND" won't probably work then

tshimkus Over a year ago

I added a array_filter() function to the $_GET array to scrub out any empty fields, which should build the query to include only filters that have been set

Bleron Mexhuani Over a year ago

Very useful code ,great job !

Ankesh Anand Over a year ago

I am using the exact same code. However, it types one last AND in the ending of SQL. Any solutions?

Ankesh Anand Over a year ago

For anyone who have the same problem of AND in the SQL statement in last, do the following: ``` $count = count($parameters); if (($count > 1) && ($count > $key)) { $count = $count - 1; $query .= " AND"; } ``` This resolved my problem. However, the theory of that if statement is correct but why it happens is out of my mind. I am using PHP7.4

Nikunj Hapani · Accepted Answer · 2019-02-09 13:35:41Z

0

single if condition to check value is empty or not.for non-empty then mysql query is two between 'AND' operation.
$query = 'select * from info';
$where = ' Where';
$and = 'id = 1';
if(!empty($_GET['name'])){
  $and .= ' AND name="test"';
}
if(!empty($_GET['city'])){
  $and .= ' AND city="test"';
}
$q = $query.''.$where.''.$and;
make like this query is 'select & from info where id=1 AND name="test" AND city="test"';

answered Feb 9, 2019 at 13:35

Nikunj Hapani

13 bronze badges

Comments

Lajos Arpad · Accepted Answer · 2019-02-09 16:27:15Z

0

You can use a prefix for the form elements, like filter-, so items will look like filter-age, filter-name and so on. Now, let's suppose you have a function like

function buildFilters($input, $delimiter) {
    foreach ($input as $key => $value) {
        $filters = array();
        $filters[]="1=1";//default filter
        if (strpos($key, $prefix) === 0) {
            //add the filter to $filters via $filters[]=yourvalue
        }
    }
    return implode($delimiter, $filters);
}

and then you can call it like

$myFilters = buildFilters($_POST, " AND ");

answered Feb 9, 2019 at 16:27

Lajos Arpad

80.4k42 gold badges123 silver badges234 bronze badges

3 Comments

Lajos Arpad Over a year ago

@Inam I converted your logic into a cycle and separated it into a function. To give you more information I would need to know more about the form you have.

Inam Over a year ago

my form is something like this ... <form action="filterpage.php" method = "GET"> <input type="text" name="title"/> <select name="category[ ]" multiple> <option value="cat1">cat1</option> <option value="cat2">cat2</option> </select> </form>

Lajos Arpad Over a year ago

@Inam you could change your form so name = "filter-title" and so on to make it compatible with the code I have given you.

CShore · Accepted Answer · 2019-03-13 21:04:46Z

0

I tried using the answer from @tshimkus. Maybe it's the way I have the arrays set up or something but there were a couple problems I ran into before getting it to work.

this line:

$query .= " $keynames[$key] = '$value'";

for me needed to be:

$query .= " $key = '$value'";

I also didn't get the check (shown below) for last item to work.

if (count($filtered_get) > 1 && (count($filtered_get) > $key)) { // more than one search filter, and not the last

ended up using a count along with this instead:

if($i!==$len){

answered Mar 13, 2019 at 21:04

CShore

651 silver badge9 bronze badges

Comments

Siddhartha Mukherjee · Accepted Answer · 2022-10-11 08:56:13Z

$whereClause = '  ';
  
    if ( !empty($_POST['gender']) || !empty($_POST['hobby']) || !empty($_POST['search_filter']) ) {
    
    $whereClause .= ' WHERE ';
    if ( !empty($_POST['gender']) && !empty($_POST['hobby']) && !empty($_POST['search_filter']) ) {
        $whereClause .= ' gender = "'.$_POST['gender'].'" AND gender = "'.$_POST['gender'].'" AND search_filter LIKE "%'.$_POST['search_filter'].'%" ';

    } else {

        if ( !empty($_POST['gender']) && $_POST['gender'] != "" && empty($_POST['hobby']) && empty($_POST['search_filter']) ) {
            $whereClause .= ' gender = "'.$_POST['gender'].'"  ';          
        }
        else if ( (!empty($_POST['gender']) && $_POST['gender'] != "") &&  (!empty($_POST['hobby']) || !empty($_POST['search_filter']))) {
            $whereClause .= ' gender = "'.$_POST['gender'].'"  '; 
            $whereClause .= ' AND ';
        }
        
        if ( !empty($_POST['hobby']) && $_POST['hobby'] != "" && empty($_POST['gender']) && empty($_POST['name'])){
            $whereClause .= ' hobby = "'.$_POST['hobby'].'"  ';           
        }
        else if ( (!empty($_POST['hobby']) && $_POST['hobby'] != "") &&  (!empty($_POST['gender']) || !empty($_POST['search_filter']))) {
            $whereClause .= ' hobby = "'.$_POST['hobby'].'"  '; 
            $whereClause .= ' AND ';
        } 
        
        if ( !empty($_POST['search_filter'])) {
            $whereClause .= ' search_filter LIKE "%'.$_POST['search_filter'].'%"  ';       
        }    
    }

}

Collectives™ on Stack Overflow

How to implement multiple search filters in php

5 Answers 5

5 Comments

Comments

3 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

5 Comments

Comments

3 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related