How to write an advanced search?

[Most_Deadly]

I want to do an advanced search, something similar to the one in scryfall where I can use either syntax or buttons to search, using multiple filters and logical operators. I have difficulty knowing how to create the SQL query dynamically. I'd also like to know if this can be done only with a sql database or also with a no-sql one.

I'm looking for open source projects implementing this, or a guide on how to do something like this. Linking here other similar questions would also help.

 

[MeMate]

Can be done with no-sql too.

How does your current table(s) look like? What fields you want to check for?

Actually it's nothing more than check for formular values, push them to an array search, and convert this array to a query string. And then, just add the string.

In case of only one table with fields text,age,name it could be like this
PHP EXAMPLE

if(isset($_POST['search'])){
     $queryArray=array();
     $regquery="select * from yourtablename";
    if(isset($_POST['txt'])){
            array_push($queryArray,"txt like '%".trim($_POST['txt'])."%'");
    }
   
if(isset($_POST['age'])){
            array_push($queryArray,"age=".$_POST['age']);
    }
 if(isset($_POST['name'])){
            array_push($queryArray,"name='".$_POST['age']."'");
    }
if(count( $queryArray)){
        $regquery.=' where '.implode(' and ',$queryArray);
       //no do something with this query
}
}

 

[CodeMonkeyJ]

MeMate's got the gist of it, but be careful of creating SQL as strings like that, as it's very susceptible to SQL injection attacks. You'd have to very, very carefully validate your user input (and write tests... so many tests...) or, better yet, use SQL binding to pass in the arguments, which would be more complex but 100% worth it.

 

[Most_Deadly]

I found this helpful:
mtg.wtf repo
beets repo