What could i make this variable so it selects all records. Should be an easy one

[Lukey]

I have a database table with various houses in it and fields relating to the price, name, features, country etc.

 

I am creating a html form to search through the records and display the appropriate results using php.

The issue is that when it comes to a listbox on the form i have an option for "any country"

however i am unsure how to implement this in my sql statement.

form code:

<select name="country">
    	<option value="any">Any Country</option>
     <option value="UK"> United Kingdom </option> 
     <option value="USA">United States of America</option>
     <option value="Argentina">Argentina</option>
     <option value="Australia">Australia</option>
     ...
    

 

SQL statement:

$sql = "SELECT * FROM house
			WHERE htown = '$town' || harea = '$area' || hcountry = '$country'
			 || beds = '$beds'
			&& pool = '$pool' && garage = '$garage' && aircon = '$aircon' ";

 

The issue is that if country is selected to option "any", "any" cant be found in the records so no results are displayed.

 

This is probably a newbie question a realise but help would be appreciated!

[Lukey]

Ok as a temporay fix i added an if statement to all variables taken and if they were blank (ie search all records of that field) i made them "%".

 

Then i changed the SQL statement to all ANDS, it seems to display the correct results however the first record is always ignored which is odd.

[bubblegum.anarchy]

Leave off the WHERE clause if < Any Country > is selected, something like:

 

$sql = "
     SELECT * FROM house 
     WHERE TRUE "
          .( $_REQUEST['country'] != "any" ? " AND hcountry = ".to_string($_REQUEST['country']) : null )
          .( $_REQUEST['area'] != "any" ? " AND harea = ".to_string($_REQUEST['area']) : null )."
     ORDER BY value";

 

to_string() is a user defined function that should return a safe sql quoted string

[Illusion]

May be what I suggesting is little bit geeky but anyway I am writing it.

Write if loop for checking country is Any for true case you remove country in where conditon and for false case go ahead with your previous query. I hope you have index on all the fields you are using in the query. Removing country in your condition make a diffrence performence wise I mean it slows down.

[fenway]

You can do similar tricks with 1=1, etc.