Seeking better way for changeable query

[turpentyne]

I'm going back to an old project to apply things I've learned. As a beginner, I was able to figure out how to change the query, based on what was chosen, but it seems like a backwards way to do it, and I'm not sure of a better way. I'm open to suggestions.

I have a search page where the user selects multiple search variables, many of which are broken apart into several tables for organization(one to many, many to many, and all that) . 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>"; }
}

[pritaeas]

Instead of all those expand variables, you could use an array and at the end use the implode function. Something like this:

$where = array ();
if ($continent_regions != ""){
    $where[] = "plant_locations_link.continent_regions IN ('$continent_regions')";
}
if ($country != ""){
    $where[] = "plant_locations_link.country IN ('$country')";
}
if ($state != ""){
    $where[] = "plant_locations_link.state IN ('$state')";
}
$whereClause = implode(' AND ', $where);