asked on

MySQL Select Where statement, where an argument is anything

Hey there,

I am needing to create a MySQL statement that can support values that mean anything.
For example, I have a form that has multiple options for 'Any' and 'All'. How can I add support in my statement for them?

The code attached is an example I just typed out so there may be mistakes.

Any ideas?

Jessee

<?PHP
$val1 = $_GET['value1'] //May contain a specific value, or 'Any'
$val2 = $_GET['value2'] //Mat contain a specific value, or 'All'

$SQL = 'SELECT * FROM table WHERE column=' . $val1 . ' AND ' . $val2 . '
?>

Open in new window

Pratima

-did you required somthing like this

$SQL = 'SELECT * FROM table WHERE column in (' . $val1 . ' , ' . $val2 . ')'

stephano12

you should use this:
$val1 = ($_GET['value1'] =='Any') ? '' : "column='".$_GET['value1']."'"
$val2 = ($_GET['value2'] =='All') ? '' : "column='".$_GET['value2']."'"

$SQL = 'SELECT * FROM table WHERE ' ($val1=='') ? '' : (($val2=='') ? $val1 : $val1.' AND '.$val2

Jessee

ASKER

Can you please explain the method fully.
I don't quite understand how it works?

gsiric

I dont understand what you need to achive.
Can you explain what do you need to select when user choose "Any" and what you need to select when user choose "All".
Any can mean any of supplied values, but "All" ?

stephano12

it checks if you have a value for value1. If you have a specific value1 val1 would be column='value1' otherwise it will be empty (''). The same check is for value2.
When you build the sql you check if you have value for val1. If you have value for val1 it checks if you have value for val2.
the posible results are:
val1
val2
val1 AND val2

You have to make a correction in my statement:
$SQL = 'SELECT * FROM table WHERE ' ($val1=='') ? (($val2=='') ? '' : '.$val2) : (($val2=='') ? $val1 : $val1.' AND '.$val2)

Jessee

ASKER

Alright, I will try to explain it more clearly.

I have a combo box:
Any
1
2
3
4

When the user selects Any, it doesn't matter whether that specific column contains 1, 2, 3 or 4, it still meets the select conditions.

Any clearer? I can't think of how to properly explain it.

Jessee

ASKER

And by All, I mean the same as Any.

stephano12

Then what is the difference between ANY and ALL ?

gsiric

So you must check what user selected and then dynamically cretate SQL

Pseudo code:

sSQL = "SELECT * FROM table WHERE "

if val1='Any' then
$sSQL = $sSQL + " column1 in ('1','2','3','4')
else
$sSQL = $sSQL + " column1 = " + $val1

stephano12

You have 2 different columns or just one?

Jessee

ASKER

Multiple columns.

Alright I'll explain the situation fully.
I have 4 combo boxes.
The first is 'bedfrom'
The second is 'bedto'
The third is 'pricefrom'
The fourth is 'priceto'

bedfrom, and bedto both contain 'Any' and the values 1 to 6.
pricefrom and priceto both contain 'Any' and the values $150 to $700 increasing in $50 increments.

I am needing to select the values that match the criteria the user selects.

Jessee

ASKER

Oh and the columns are 'colBedrooms' and 'colPrice'.

ASKER CERTIFIED SOLUTION

gsiric

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

stephano12

This is the code for you:
<?PHP
$val1 = ($_GET['bedfrom']=='Any') ? '' : $_GET['bedfrom'];
$val2 = ($_GET['bedto']=='Any') ? '' : $_GET['bedto'];
$val3 = ($_GET['pricefrom']=='All') ? '' : $_GET['pricefrom'];
$val4 = ($_GET['priceto']=='All') ? '' : $_GET['priceto'];
$SQL = 'SELECT * FROM table ';

if ($val1==''){
      if ($val2==''){
            if ($val3==''){
                  if ($val4==''){
                        $SQL = $SQL;
                  }else{
                        $SQL = $SQL . 'WHERE colPrice<=' .$val4;
                  }
            }else{
                  if ($val4==''){
                        $SQL = $SQL . 'WHERE colPrice>=' .$val3;
                  }else{
                        $SQL = $SQL . 'WHERE colPrice>=' .$val3. 'AND colPrice<=' .$val4;
                  }
            }
      }else{
            if ($val3==''){
                  if ($val4==''){
                        $SQL = $SQL .WHERE colBedrooms<=' .$val2;
                  }else{
                        $SQL = $SQL .WHERE colBedrooms<=' .$val2. ' AND colPrice<=' .$val4;
                  }
            }else{
                  if ($val4==''){
                        $SQL = $SQL .WHERE colBedrooms<=' .$val2. 'AND colPrice>=' .$val3 ;
                  }else{
                        $SQL = $SQL .WHERE colBedrooms<=' .$val2. 'AND colPrice>=' .$val3. ' AND colPrice<=' .$val4;
                  }
            }
      }
}else{
      if ($val2==''){
            if ($val3==''){
                  if ($val4==''){
                        $SQL = $SQL;
                  }else{
                        $SQL = $SQL . 'WHERE colPrice<=' .$val4;
                  }
            }else{
                  if ($val4==''){
                        $SQL = $SQL . 'WHERE colPrice>=' .$val3;
                  }else{
                        $SQL = $SQL . 'WHERE colPrice>=' .$val3. 'AND colPrice<=' .$val4;
                  }
            }
      }else{
            if ($val3==''){
                  if ($val4==''){
                        $SQL = $SQL .WHERE colBedrooms<=' .$val2. ' AND colBedrooms>=' .$val1';
                  }else{
                        $SQL = $SQL .WHERE colBedrooms<=' .$val2. ' AND colBedrooms>=' .$val1'. ' AND colPrice<=' .$val4;
                  }
            }else{
                  if ($val4==''){
                        $SQL = $SQL .WHERE colBedrooms<=' .$val2. ' AND colBedrooms>=' .$val1'. 'AND colPrice>=' .$val3 ;
                  }else{
                        $SQL = $SQL .WHERE colBedrooms<=' .$val2. ' AND colBedrooms>=' .$val1'. 'AND colPrice>=' .$val3. ' AND colPrice<=' .$val4;
                  }
            }
      }
}

?>