Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

MySQL Select Where statement, where an argument is anything

Posted on 2011-05-11
14
Medium Priority
?
342 Views
Last Modified: 2012-05-11
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

0
Comment
Question by:Letsgetcoding
  • 5
  • 5
  • 3
  • +1
14 Comments
 
LVL 39

Expert Comment

by:Pratima Pharande
ID: 35744101
-did you required somthing like this

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

Expert Comment

by:stephano12
ID: 35744394
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
0
 

Author Comment

by:Letsgetcoding
ID: 35744436
Can you please explain the method fully.
I don't quite understand how it works?
0
Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

 
LVL 7

Expert Comment

by:gsiric
ID: 35744466
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" ?
0
 
LVL 1

Expert Comment

by:stephano12
ID: 35744470
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)
0
 

Author Comment

by:Letsgetcoding
ID: 35744519
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.
0
 

Author Comment

by:Letsgetcoding
ID: 35744523
And by All, I mean the same as Any.
0
 
LVL 1

Expert Comment

by:stephano12
ID: 35744536
Then what is the difference between ANY and ALL ?
0
 
LVL 7

Expert Comment

by:gsiric
ID: 35744644
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
 
0
 
LVL 1

Expert Comment

by:stephano12
ID: 35744798
You have 2 different columns or just one?
0
 

Author Comment

by:Letsgetcoding
ID: 35744859
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.
0
 

Author Comment

by:Letsgetcoding
ID: 35744864
Oh and the columns are 'colBedrooms' and 'colPrice'.
0
 
LVL 7

Accepted Solution

by:
gsiric earned 2000 total points
ID: 35744913
So something like this (in PSEUDO CODE):




$sql = "SELECT * FROM tablename WHERE "

if bedfrom='Any' and $bedto='Any' then
 $sql += " colBedroms >=1 and colBedroms <= 6"
elseif $bedfrom='Any then
 $sql += " colBedroms <= "  + $bedto
elseif $bedto='Any then
 $sql += " colBedroms >= " + $bedfrom
else
 $sql += "colBedroms >=" + $bedfrom + " and  colBedroms <= " + $bedto

if $pricefrom="Any" and $priceto="Any"
 $sql += " and colPrice >= 150 and colPrice <= 700)
else if $pricefrom="Any" then
 $sql += " and colPrice <= " + $priceTo
elseif $bedto='Any then
 $sql += " and colPrice >= " + $pricefrom
else
 $sql += " and colPrice >= " + $pricefrom + " and colPrice <= " + $priceto

Open in new window

0
 
LVL 1

Expert Comment

by:stephano12
ID: 35744943
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;
                  }
            }
      }
}

?>
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

MSSQL DB-maintenance also needs implementation of multiple activities. However, unprecedented errors can hamper the database management. In that case, deploying Stellar SQL Database Toolkit ensures fast and accurate database and backup repair as wel…
Exchange database can often fail to mount thereby halting the work of all users connected to it. Finding out why database isn’t mounting is crucial and getting the server back online. Stellar Phoenix Mailbox Exchange Recovery is a champion product t…
In this video, Percona Director of Solution Engineering Jon Tobin discusses the function and features of Percona Server for MongoDB. How Percona can help Percona can help you determine if Percona Server for MongoDB is the right solution for …
The Relationships Diagram is a good way to get an overall view of what a database is keeping track of. It is also where relationships are defined. A relationship specifies how two tables connect to each other. As you build tables in Microsoft Ac…

578 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question