0

I'm relatively new to PHP and I need some help on a search query.

I have a few drop down 'select' and a 'checkbox group' which will filter the search from database using (...WHERE somethingA = 'somethingA' && somethingB = 'somethingB' etc)

That's all working great but the problem comes when I want to make it so that some search fields DONT have to be used, so if 'SomethingA' is either disabled or value='none' then it will only return WHERE somethingB = 'SomethingB'.

I have tried using OR instead of AND but that returns both values if they are true and not really filtering it properly.

my initial solution was to have if..else statements to define the query, for example:

$query = "SELECT * FROM table";        
$results = $con->query("$query $where $QueryA $QueryB $QueryC");

if($_GET['SomethingA'] == "none" && $_GET['SomethingB'] == "none" && $_GET['SomethingC'] == "none"){
$where = ""
$QueryA = ""
$QueryB = ""
$QueryC = "ORDER by ID" //if all search field is 'none' then get all results
}elseif($_GET['SomethingB'] == "none" && $_GET['SomethingC'] == "none"){
$where = "WHERE"
$QueryA = "SomethingA = '{SomethingA}'" //only use A filter one field
$QueryB = ""
$QueryC = "" 
}elseif($_GET['SomethingA'] == "none" && $_GET['SomethingC'] == "none"){
$where = "WHERE"
$QueryA = ""
$QueryB = "SomethingB = '{SomethingB}'" //only use B filter one field
$QueryC = "" 
.....

it works but you can already see the problem as if i wanted to cross matrix all conditions it becomes very lengthy and confusing. So my question is whether there is a much better way of doing this, for instance, make value='none' return all results?

been looking around and attacking it from many angles but cant find a solution.. maybe javascript could help but im not the best with it.

thanks in advance

Drew
  • 24,851
  • 10
  • 43
  • 78
Eric
  • 35
  • 1
  • 6
  • it is called a Query Builder (or something similar). You implode out the $sql string via arrays, only including those chunks of the where clause that are relevant. I will tweak your title I think. – Drew Sep 20 '15 at 10:49
  • yeah thanks, I was wondering how best to describe this. maybe I will find an answer if I search query builder – Eric Sep 20 '15 at 10:54
  • here ya go [stack question](http://stackoverflow.com/q/19025648) for the concept at least – Drew Sep 20 '15 at 10:56

1 Answers1

1

The question is not too clear but look into this. It should help.

$query="SELECT * FROM table WHERE";
$query_link = " AND ";
$isASet=false;
$isBSet=false;
$isCSet=false;

if(strcmp($_GET['SomethingA'],"none") != 0){
    $query.=" column = {$_GET['SomethingA']}";

    //set this to true for later if statements
    $isASet=true;
}


if(strcmp($_GET['SomethingB'],"none") != 0){
    //check if A has been set, if yes include an AND
    if($isASet){
        $query.=$query_link;
    }
    //include this one as usual
    $query.=" column = {$_GET['SomethingB']}";
    $isBSet=true;
}

if(strcmp($_GET['SomethingC'],"none") != 0){
    //check if A or B has been set, if yes include an AND
    if($isASet || $isBSet){
        $query.=$query_link;
    }
    //include this as usual
    $query.=" column = {$_GET['SomethingC']}";
}

//run query and collect result
$result = $connection->query($query);
Visionwriter
  • 649
  • 6
  • 12