Multiple variable search, need help optimizing

[tpl41803]

Hi all,

 

I am working on a building a search page which will allow me to search and browse through a database by any of its columns. There are 10 columns total and I want to be able to search by any combination of them.

 

I started with just three variables to see if I could figure it out. And after completing that script, I realize writing the script for 10 variables will be enormous.

 

if (isset($bmake, $btype, $bprice)) {
     if ($bmake == all && $bprice == all && $btype == all) {
           $where = "";
      } elseif ($bmake == all && $bprice == all && $btype != all) {
           $where = "WHERE style='$btype'";
      } elseif ($bmake == all && $bprice != all && $btype == all) {
           $where = "WHERE price BETWEEN '$priceLow' and '$priceHigh'";
      } elseif ($bmake != all && $bprice == all && $btype == all) {
           $where = "WHERE make='$bmake'";
      } elseif ($bmake == all && $bprice != all && $btype != all) {
           $where = "WHERE style='$btype' AND price BETWEEN '$priceLow' and '$priceHigh'";
      } elseif ($bmake != all && $bprice == all && $btype != all) {
           $where = "WHERE make='$bmake' AND style='$btype'";
      } elseif ($bmake != all && $bprice != all && $btype == all) {
           $where = "WHERE make='$bmake' AND price BETWEEN '$priceLow' and '$priceHigh'";
      } elseif ($bmake != all && $bprice != all && $btype != all) {
           $where = "WHERE make='$bmake' AND style='$btype' AND price BETWEEN '$priceLow' and '$priceHigh'";
      }
} else {
    $bmake = 'all';
    $btype = 'all';
    $bprice = 'all';
    $where = '';
}

   $result = mysql_query("SELECT * FROM $table $where ORDER BY year");
     if (!$result) {
       die("Query to show fields from table failed");
       }

 

So, my problem now is that if I add another variable, I have to write a whole lot more elseif statements, for the total 10 variables the number of combinations is well into the thousands. I'm not math expert, that's just a guess.

 

I know this is possible because there are hundreds of sites in existence that function in the way that I want mine too.

 

Anyone have any ideas or steps in the right direction?

 

I'm using PHP5.2

 

 

thanks

[Goat]

there is a simple trick to accomplish this. Array implode function merges several strings and puts some other string in between. You can use it to simply 'glue' several strings with AND keyword.

 

try something like this

$search_array = array();

if($btype !=all)
  search_array[] =  " style='$btype' ";

if($bprice !=all)
  search_array[] =  " price BETWEEN '$priceLow' and '$priceHigh' ";

if($bmake!=all)
  search_array[] =  " make='$bmake' ";

$search_string = implode($search_array," AND ");

$query = "SELECT * FROM $table WHERE $search_string ORDER BY year"

///.......

 

 

Edit: I messed up code a little, now it's better, you only need one WHERE in main query

 

Also, don't forget to use myslq_real_escape_string to sanitize input!

 

regards,

Goat

[tpl41803]

thanks so much, this is exactly what i needed :-D