multiple parameter search

[beermaker74]

ok on page 1 I have a form that has multiple search parameters
ie
State
City
Zipcode
Price
etc
the form action is post and its goes to searchlistings.php
on this page  I have my recordset

I want to be able to display the results from page 1 with the multiple parameters. This is my problem.  if the user  picks two of the parameters then it doesnt pull the records from both fields it only pulls it from one of them. I need the query to be smart and know that only one parameter was posted. if multiple params were posted I need the query to use all of them.
Example
if you search for alabama it pulls all the records from alabama
if you search for athens and not list a state it pulls all the records that match athens.
if you search for athens and alabama it pulls all the records from alabama and isnt distinct.

$query_search = "SELECT houseid, address, city, `state`, zipcode, county, status, zoning, sqft, housetype, yearbuilt, schooldistrict, bedrooms, baths, totalrooms, housedescription, specialfeatures, price, virtualtour FROM house WHERE (city = '$city' AND state = '$state')OR (city = '$city') OR (state = '$state')";
the $city and $state variables are listed above= to the post
im sure it is an easy fix I am just stumped
I hope that I explained this properly. Basically I need to filter the results by whatever the user entered but I need the flexibility to filter if the user only uses one of the params from the search
thanks

[Psycho]

There's no need to use both of these clauses:
(city = '$city' AND state = '$state')
(city = '$city') OR (state = '$state')

Every record that matches the first clause will be in the 2nd one.

Anyway, I would just do some pre-processing of the search criteria before building the query. Something like this"
[code]<?php
if ($_POST['city']) { $whereParts[] = "city = '$_POST['city']'"; }
if ($_POST['state']) { $whereParts[] = "state = '$_POST['state']'"; }
if ($_POST['zipcode']) { $whereParts[] = "zipcode = '$_POST['zipcode']'"; }
if ($_POST['price']) { $whereParts[] = "price = '$_POST['price']'"; }
if ($_POST['etc']) { $whereParts[] = "etc = '$_POST['etc']'"; }

$wherClause = implode(' OR ', $whereParts);
$query_search = "SELECT
                      houseid, address, city, `state`, zipcode, county, status, zoning, sqft,
                      housetype, yearbuilt, schooldistrict, bedrooms, baths, totalrooms,
                      housedescription, specialfeatures, price, virtualtour
             FROM house WHERE " . $whereClause;
?>[/code]

[alpine]

Or something like this - example with textfields
[code]

<?php

if(isset($_POST['submit']))
{
$query = "select * from house";
$count = 0;
foreach($_POST as $key => $value){
$value = htmlspecialchars($value, ENT_QUOTES);
if($count == 0): $qq = " WHERE"; else: $qq = " OR"; endif;
if(!empty($value)): $query .= "$qq $key = '$value'"; endif;
$count++;
}
$query .= " order by houseid desc";
$sql = mysql_query($query) or die(mysql_error());

// fetch array, show results

}
else
{
// Each formfield name here, must match the target column names in your table (city,state)
echo <<<_HTML

<form action="search.php" method="post">
<p>City: <input type="text" name="city" /></p>
<p>State: <input type="text" name="state" /></p>
<p><input type="submit" name="submit" value="search" /></p>
</form>

_HTML;
}

?>

[/code]

[boo_lolly]

here's my version:
[code]
<?php
$query_array = array();
if(isset($_POST['lname'])){
$query_array[] = "brideLname LIKE '%". $_POST['lname'] ."%' OR groomLname LIKE '%". $_POST['lname'] ."%'";
}
if(isset($_POST['fname'])){
$query_array[] = "brideFname LIKE '%". $_POST['fname'] ."%' OR groomFname LIKE '%". $_POST['fname'] ."%'";
}
if(isset($_POST['event_day'])){
$query_array[] = "event_day LIKE '%". $_POST['event_day'] ."%'";
}
if(isset($_POST['event_month'])){
$query_array[] = "event_month LIKE '%". $_POST['event_month'] ."%'";
}
if(isset($_POST['event_year'])){
$query_array[] = "event_year LIKE '%". $_POST['event_year'] ."%'";
}

      /*
*$query string becomes all the sql queries
*with 'AND' in between them
*/
$query_string = implode(" AND ", $query_array);

#echo $query_string ."<br />\n"; /*check sql query*/

$result = mysql_query("SELECT * FROM my_search_table WHERE ". $query_string ."") OR die(mysql_error());
?>
[/code]

[beermaker74]

thanks for all the responses
by the time I saw the rest of the posts I had already used mjdamato's suggestion.
but thanks for the help