I'm going back to an old project to apply things I've learned. But one thing I haven't figured out is the best way to do this obscenely complicated query with numerous variables.

I have a search page where the user selects multiple search variables, many of which are broken apart into different tables for organization. Once the user presses submit, I use ajax to get this script below, with several variables that either ='' if empty(nothing was selected), and so do nothing in the query, or equal whatever the variable selected was.

I'll post what I have here. This is a working script. but it is just so... ugly and primitive. How should I have done this? What's a better/ cleaner way?


Code:
 <?php
if (isset($_POST['submitted'])){

require ('../database_connection.php');

$item = $_POST['submitted'];
$leaf_shape = $_POST['s1'];
$leaf_color = $_POST['s2'];
$leaf_vein = $_POST['s3'];
$leaf_margin = $_POST['s4'];
$leaf_arrangement = $_POST['s5'];
$leaf_surface = $_POST['s6'];
$continent = $_POST['s7'];
$continent_region = $_POST['s8'];
$country = $_POST['s9'];
$state = $_POST['s10'];
$flower1 = $_POST['s11'];
$flower2 = $_POST['s12'];
$flower3 = $_POST['s13'];
$flower4 = $_POST['s14'];

$expand1 = "";
$expand2 = "";
$expand3 = "";
$expand4 = "";

$howmany = "2";

if ($leaf_color != ""){
	   $expand1a = "JOIN plant_color_connector ON termin_connect_to_plants.plant_id = plant_color_connector.plant_id";
	   $expand1b = "AND plant_color_connector.color_id IN (".$leaf_color.")";
	   
}
if ($leaf_margin != ""){
	   $expand2 = " ,$leaf_margin";
	   
}
if ($leaf_vein != ""){
	   $expand3 = " ,$leaf_vein";
	   $howmany = $howmany+1;
}

if ($continent != ""){
	   $expand4a = "JOIN plant_locations_link ON termin_connect_to_plants.plant_id = plant_locations_link.plant_id";
	   $expand4b = "AND plant_locations_link.continents IN ('$continent')";
	   
	   if ($continent_regions != ""){
	   $expand5 = "AND plant_locations_link.continent_regions IN ('$continent_regions')";
	   }
	   if ($country != ""){
	   $expand6 = "AND plant_locations_link.country IN ('$country')";
	   }
	   if ($state != ""){
	   $expand7 = "AND plant_locations_link.state IN ('$state')";
	   }

}


$query = "SELECT plantae.plant_name, plantae.taxonomic_genus, plantae.scientific_name, termin_connect_to_plants.plant_id, COUNT(*) AS howmany
FROM `termin_connect_to_plants`
JOIN plantae ON termin_connect_to_plants.plant_id = plantae.plant_name
$expand1a
$expand4a
WHERE term_id IN ( $leaf_shape $expand2 $expand3)
$expand1b
$expand4b
$expand5
$expand6
$expand7
GROUP BY plantae.plant_name, plantae.taxonomic_genus, plantae.scientific_name, termin_connect_to_plants.plant_id";

$result = mysql_query($query) or die(mysql_error());
if (mysql_num_rows($result) > 0){
$number = mysql_num_rows($result);
// echo $query;
echo "<div id='results' style='position:relative;top:4px; float:right; width:100%; z-index:200; right:10px;height:0;overflow:visible;'><p style='font-size:11px; line-height:17px;padding-left: 0px;'><b>".$number." results</b><br><br>";
while ($row = mysql_fetch_array($result)) { 
	   echo "<a href='view_plant.php?id=".$row['plant_name']."'>View plant</a>&nbsp; &nbsp;".$row['taxonomic_genus']."&nbsp;".$row['scientific_name']."&nbsp;".$row['infraspecific_epithet']."<br>";
}
echo "</p></div>";


} else { echo "<div id='results' style='position:relative;top:10px; float:right; width:100%;z-index:200;  padding-left:0px;right:10px; height:0;overflow:visible;'><p style='font-size:11px; line-height:17px;padding-left: 30px;'><b>No matches</b></p></div>"; }
}