Database Search Form

cba

I'm building a flight search page to display flights from the MySQL database onto a results page for a virtual airline I'm trying to start up for MS Flight Sim. My search page has 4 list menus: Origin City, Destination City, Aircraft, and Carrier. Each menu has an "any" option, so, i.e., if the user does not select an aircraft type, flights will be displayed with all aircraft types, only filtering based on the other parameters. I can build a simple MySQL query to filter using only one of the parameters, however if the SQL query filters using all of them and one of the fields has "any" selected the search will not work. The only solution I see is to write 16 if then statements (all of the possible combinations of menu selections) and 16 different SQL statements. Is there a better way to do this?

weevil

If you use variables in your query, you should be able to do it all in one statement. If the user selects "any", you just do not include that in your WHERE clause. What does your code look like so far?

cba

I'm using variables in my where clause, and it all works fine if an option is chosen from every menu. It fails to work, however, when one or more of the fields are left as the default "any".

$Origin = $HTTP_POST_VARS['Origin'];
$Dest = $HTTP_POST_VARS['Destination'];
$Plane = $HTTP_POST_VARS['Aircraft'];
$Op = $HTTP_POST_VARS['Operator'];

$ShowFlights = "SELECT * FROM Flights WHERE Origin = $Origin AND Destination = $Dest AND Aircraft = $Plane AND Operator = $Op";

weevil

You could do this in a function, but here it is

if ($_POST['Origin'] != "any") {
  $whereclause = "Origin = $_POST['Origin']";
  $and = " AND";
}
if ($_POST['Destination'] != "any") {
  $whereclause = "$and Destination = $_POST['Destination']";
  $and = " AND";
}
if ($_POST['Aircraft'] != "any") {
  $whereclause = "$and Aircraft = $_POST['Aircraft']";
  $and = " AND";
}
if ($_POST['Aircraft'] != "any") {
  $whereclause = "$and Operator = $_POST['Operator']";
}
if ($whereclause) {
  $where = "WHERE";
}
$ShowFlights = "SELECT * FROM Flights $where $whereclause";