Best way to set a search up

lincolnway

I am not sure exactly how to explain this so bare with me. I have a conditional query search and want some advice on the best way to approach this. I don't think the way I am doing it is the best way so your help would be appreciated. Thanks again for your help.

Here is what I am attempting. I want to pull some conditional results based on the number that is pulled. Here is a basic example...

$query = "SELECT * FROM $tbname WHERE option1='$option1';
$result = mysql_query($query, $connect);
$num = mysql_num_rows($result);
$highNum = 6;

if($num > $highNum){
$query = "SELECT * FROM $tbname WHERE option1='$option1' AND option2='$opiont2'";
$result = mysql_query($query, $connect);
$num = mysql_num_rows($result);
} else if($num > $highNum) {
$query = "SELECT * FROM $tbname WHERE option1='$option1' AND option2='$opiont2' AND option3='$option3'";
$result = mysql_query($query, $connect);
$num = mysql_num_rows($result);
} else if($num > $highNum) {
// and so on until the search query comes below the number 6
}

The trick for me is that I want to add more options down the road and for the true application it can result in a lot of if/else statements. Not only can this become time consuming, it's a lot of code to go back and edit later. I don't mind doing the work but I would also like to try and keep the code as efficient and quick as possible.

Any suggestions on how to improve this. I hope my explanation is clear and understandable. Thanks again for the help.

Weedpacket

Another of these nudists wanting me to join them....

// May be constructed dynamically
$options = array(
'option1'=>$option1,
'option2'=>$option2,
'option3'=>$option3,
//...add further entries to taste
);
$query = "Select [insert desired fields here] from $tbname";
if(count($options)
{
    $query = array();
    foreach($options as $name=>$value)
    {
        $query[] = "$name='$value'";
    }
   $query .= ' where '.join(' and ', $query);
}

But if these options are what they look like you should look at normalising your database structure; then there won't be any additional work even if there were fifty options.

lincolnway

Thanks for your response. I probably wasn't real clear on what I am trying to do. Let me know if this makes more sense.

The user has some drop down options to choose from. Lets use a vehicle database as an example. So the user goes and chooses these options to find the a list of cars that best match there criteria. I Only want a limited number to show up depending on what they choose. Options might be... make, model, year, color, etc. The importance of the match will be in order, so the "Make" is the most important and the "color" is the least important. Lets say I want to pull no more than 6 and no less than 3.

If the search pulls all the options it might narrow the results to 1 or 2. I don't want that. I want at least three to pull up. So maybe the search stops searching after it matches the "make, model & year" but the color is left out because it found the suggested number of mathces need.

If it only searches "make" and "model" and the results pull more than six it will continue to search until it hits the suggested number of matches which is 3,4,5 or 6.

I hope that makes sense.

What would the best way to get a search to react to a minimum and maximum. This is a basic example but it hopefully illustrates my goal.

Thanks again for your help.