Better way to condition variables that just looping through possibilites?-CodePudding

Looking for some knowledge from the group, done some research but not really found anything, so looking to see if I can get any pointers.

I have code that has the potential inclusion zero to three different options. Example: MySQL query would be:

$sql = "SELECT * FROM users";

I have three dropdowns to add to this that give a where for location (loc), system(ss) and entity(entity), or a combination of them.

I currently manage this, which works, by conditioning the query through php if statements, which work like a truth table to give the right query.

The _where variable below is used when the item is present and first, and the _and if there are subsequent additions. fairly simple stuff.

if($loc > 0 && $ss > 0 && $entity > 0)  // 1 1 1
{
    $sql.= " $loc_where $ss_and $entity_and";
}
else if($loc > 0 && $ss > 0 && $entity == "") // 1 1 0
{
    $sql.= " $loc_where $ss_and";
}
else if($loc == "" && $ss > 0 && $entity > 0) // 0 1 1
{
    $sql.= " $ss_where $entity_and";
}
else if($loc > 0 && $ss == "" && $entity > 0) // 1 0 1
{
    $sql.= " $loc_where $entity_and";
}
else if($loc > 0 && $ss == "" && $entity == "") // 1 0 0
{
    $sql.= " $loc_where";
}
else if($loc == "" && $ss > 0 && $entity == "") // 0 1 0
{
    $sql.= " $ss_where";
}
else if($loc == "" && $ss == "" && $entity > 0) // 0 0 1
{
    $sql.= " $entity_where";
}
else // 0 0 0
{
    $sql;
}

Is there a better way to look at the three options and create the required query without looping through each iteration? Something that maybe looks at which variables are set and can then create the $sql more intelligently? and perhaps can also incorporate more options should they be needed in the future without me having the create a very long truth table of combinations.

Appreciate the assistance.

CodePudding user response：

Can you try this :

$sql = "SELECT * FROM users ";

if ($loc > 0 || $ss > 0 || $entity > 0) {
    $sql.= " where id > 0 ";
    
    $sql.= $loc > 0 ? $loc_and : '';
    $sql.= $ss  > 0 ? $ss_and : '';
    $sql.= $entity > 0 ? $entity_and : '';
}

Where id > 0 will always be true, so you will have to add the and parts only to your query without the need to worry about the where condition.