Filter Query Using Multiple Drop Down Menu

[iGuest 3]

Hi Im working on using 2 drop down menu to filter mysql query. Here is the drop down menu<form action="showJobs.php" method="post"><select name="Locations"><option value="All" selected="selected">All states</option><option value="arizona">Arizona</option><option value="alaska">Alaska</option></select><select name="Jobs"><option value="All" selected="selected">All jobs</option><option value="arizona">Carpenters</option><option value="alaska">Plumbers</option></select><input type="submit" value="search jobs" /></form>i can use this query <?php $dState = $_POST["Locations"]; $dJob = $_POST["Jobs"]; $sqlQuery = "SELECT * FROM mytable WHERE state=\"$dState\" AND job=\"$dJob\""; ?>However, instead of requiring both a 'location' and a 'job,' the user has the option of selecting just one or the other, so that if the user selects "Arizona" and leaves the 'jobs' menu as is (for example, at the default option of 'All jobs'), we would be returned all of the carpenters, plumbers, and auto mechanics in Arizona. Or if the state was left at the default, and 'carpenters' was selected, then all the carpenters in Arizona, Alaska, and Alabama would be returned. What would be a good way to code this? maybe something like if conditionsif($Locations !== 'all'){if($Jobs !== 'all'){thanks

[Iniyila 2]

you can check the content of $dState and $dJob before selecting from database, as you said an "if" will do the job great, so have you faced any problem using "if" ? for example i use your code to show how you can do it :

 

1.if user didn't set anything and location and job both are in their default value :

 

 

if(&dState == &All& & &dJob == &All&){

&sqlQuery = "SELECT * FROM mytable";

}

 

2. if user sets the job but not specifying any location so :

 

elseif(&dState == &All&){

&sqlQuery = "SELECT * FROM mytable WHERE job="&dJob"";

}

 

3. if users sets the location but not specifying any job :

 

elseif(&dJob == &All&){

&sqlQuery = "SELECT * FROM mytable WHERE state="&dState"";

}

 

4. and in the end if user sets all fields :

 

else{

&sqlQuery = "SELECT * FROM mytable WHERE state="&dState" AND job="&dJob"";

}

 

so here you can do what you have mentioned, but also there is another way of doing such things and that is switch case method in php, maybe it needs less lines of codes but about the efficiency you need to check php version and then search around to see if you can find the resource and time consumption of each function.

Edited May 11, 2012 by velma (see edit history)

[jlhaslip 4]

Be sure to use the correct case when checking the conditionals.The opening post above and the reply given to it are different.'All' and 'all' will not compare favourably. Be sure to use either upper case or lower case. Don't expect the comparison to work properly if you mix cases. Just a note to remind you that the if statement will not work by comparing All, all, or ALL.