Jump to content

dynamic sql select statement


fluid

Recommended Posts

/*
$sql = S ELECT  FROM holidays WHERE  Pprice <= $Pprice 
(if Pduration not equal to "Any" print)  AND Pduration = $Pduration
(if Pboard not equal to "Any" print)  AND Pduration = $Pboard
(if Pdeploc not equal to "Any" print)  AND Pdeploc = $Pboard
(if Pcomfort not equal to "Any" print)  AND Pcomfort = $Pboard
(if Prating not equal to "Any" print)  AND Prating = $Pduration 
ORDER BY Pprice ASC
*/

 

 

Basically the WHERE conditions need to show when the related variable is not set to ANY

 

Any suggestions would be appreciated (my php /mysql is fairly limited). Many Thanks.

Link to comment
https://forums.phpfreaks.com/topic/198486-dynamic-sql-select-statement/
Share on other sites

Hi, thanks for responding...

 

I have several drop down boxes followed by a search button.

 

I want to do a search that shows results where the fields match the drop down boxes - However the first option in each drop down is 'Any' so I want the search to ignore that drop down if it is set to 'Any'.

 

eg:

SELECT  FROM holidays WHERE  Pprice  Pduration = $Pduration AND Pprice = $Pprice

 

But if $Pprice = 'Any' it would only do this:

SELECT  FROM holidays WHERE  Pprice  Pduration = $Pduration

 

and leave out the Pprice condition

 

Hope that makes a bit more sense....

Found what I think may be the right direction: http://www.phpfreaks.com/forums/index.php/topic,167302.msg737240.html#msg737240

 

I just don't now how to apply that idea to the following code.  I have changed the dropdown <Select> boxes so that if 'Any' is selected, the <option> value = "".  I'm assuming the ISSET in the code from the above link will then ignore the variable if it equals nothing.

 

$sql=("SELECT * FROM holidays WHERE Pduration = '".$Pduration."' AND 
Pdeploc LIKE '%".$Pdeploc."%' AND Pboard LIKE '".$Pboard."' 
AND Pcountry LIKE '".$Pcountry."' AND Pprice <= '".$Pprice."' ORDER BY Pprice ASC")
or die(mysql_error()); 

Think I've sorted it, I realise my code is very messy and this prob is not the best way to do it, but think it works:

$Sdeploc = '';
$Sboard = '';
$Scountry = '';
$Sprice = '';
if (isset($Pdeploc))
{
    $Sdeploc = "AND Pdeploc LIKE '%".$Pdeploc."%'";
}

if (isset($Pboard))
{
    $Sboard = "AND Pboard LIKE '%".$Pboard."%'";
}

if (isset($Pcountry))
{
    $Scountry = "AND Pcountry LIKE '%".$Pcountry."%'";
}
if (isset($Pprice))
{
    $Sprice = "AND Pprice <= '".$Pprice."'";
}
$holidaysearch = "".$Sdeploc." ".$Sboard." ".$Scountry." ".$Sprice."";

$sql = "SELECT * FROM holidays WHERE Pduration = '".$Pduration."' ".$holidaysearch."";
$result = mysql_query($sql);

 

Archived

This topic is now archived and is closed to further replies.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.