Search with multiple drop down boxes

[aneesh29]

Hi
I have a search pege in my website.I am developing in PHP/MYSQL.
search page contains three drop down boxes
1:Country
2:Education
3:age

Country contains:All,US,Canada etc
Education Contains:All,Highschool etc
Age Contains:All,20,21,etc

How can i write sql statement to satisfy this criteria to get values from database.

Thank you so much for your help

[WolfShade]

This is really a question for the SQL forum, but..

"SELECT col1, col2, col3, col4, .... col20, col21, col22
FROM `tableA`
WHERE col5 = '".$_POST['country']."'
   AND col7 = '".$_POST['education']."'
   AND col9 = ".$_POST['age']." "

This may not be quite correct - it's been a while since I worked with MySQL.

[Belrick]

Just a tip, age should also not be hard stored. Should store their birthday and use PHP to calculate their age.

Oh and I use a search engine but my process is different to yours. I collect all the SQL rows and store in array.

I then use strpos() to search and display.

Oh and you shoudl still sanitize POSTs.

Oh and is the coutnry name education going to be stored within a string?

If so you will need to use % with your WHERE clause.

[aneesh29]

Hi
Thanks for your reply,but this query is only if they select all the drop down boxes,what happens if they do not want to select county option,we will get empty rows

any other ideas

[Belrick]

$search_clause ="";
$first_clause = TRUE;
if(isset($_POST['country'])){

if($first_clause){
$x = "WHERE";
}
else{
$x = "AND";
}
$first_clause = FALSE;
$search_clause .= " $x country ='".$_POST['country']."' ";
}

//etc etc for all your search possibilities

$query = "SELECT * FROM `yourtable` $search_clause"; 

Note if you are submitting a search one drop down select at a time you will need to store past POSTs into a SESSION.

[aneesh29]

Hi
Thank you so much for your reply
actually i have "ANY" option in all drop down boxes,so if they choose "ANY" they should get all the results from database,suppose they choose "ANY" option for country not for remaning onece,how can i write query in this situcation

[Belrick]

Instead of checking to see if you receive a country POST with ISSET do a check and see if country POST == "any" else add a country search clause.

ie:

if($_POST['country'] != "ANY"){ 

if($first_clause){ 
$x = "WHERE"; 
} 
else{ 
$x = "AND"; 
} 
$first_clause = FALSE; 
$search_clause .= " $x country ='".$_POST['country']."' "; 
} 

This simply assumes that youll always recieve a country POST and that if its not containing ANY then its a country that restricts your SQL select statement.

[Belrick]

Hmmm

Altogether

$search_array = array("Country","Education","Age");
$search_clause =""; 
$first_clause = TRUE; 


foreach($search_array as $search){
if($_POST[$search] != "ANY"){ 

if($first_clause){ 
$x = "WHERE"; 
} 
else{ 
$x = "AND"; 
} 
$first_clause = FALSE; 
$search_clause .= " $x $search ='".$_POST[$search]."' "; 
} 
} 

Echo / print out $search_clause to see if it creates a proper SQL syntax. If so your good to use it! (of course there is no sanitization being done here though and nor does it handle the smarts around storing birthdays rather than arbitary ages)

[aneesh29]

Hi
Thank you so much, i solved my problem