Three dropdown filters

[Davidkarate]

Hi guys, I have a table called classifieds and it contains the following:

id, title, description, state_id, city_id, cat_id

This is dynamically populated from database.

 <form name="theForm" method="post" action="advanced_search.php">
 
 <input type="submit" name="Submit" value="Search" />

  <select name="categories" >
  <option value=""> Select Categoy</option>
   
  </select>

  <select name="state" >
  <option value=""> Select state</option>
   
  </select>

  <select name="city" >
  <option value=""> Select city</option>
   
  </select>

So, what I want is. When I select a category and click search, the result for that category will show up. And If I select a category and a state then I want it to be filter to a specified category and state, And same thing if city is selected.

EX: If I select employment category in Minneapolis city, then the search should be filter to all employments in that city.
I gave it a try like this:

  if (isset($_POST['Submit'])) {
 
 $select = "SELECT * FROM classifieds";
$where = " WHERE ( authorized = '1')";
    
  
  $where .= " AND (cat = '" . $categories . "' AND state_id = '" . $state . "' AND city_id = '" . $city . "')";

 $where .= " AND (state_id = '" . $state . "' )";
 $where .= " AND (city_id = '" . $city . "' )";
 $q = $select . $where . ' ORDER BY date DESC';

With this code I must select all dropdowns, and that's not what I want.

I hope someone will walk me through this, and thanks in advance.

[omoutop]

perhaps if you split it in multiple if statements?
something like this:

if (isset($_POST['city']))
{
/ /querywith all options
}
else if (isset($_POST['state']))
{
// query with first 2 options
}
else
{
// query with 1 option
}

[Davidkarate]

I can't thank you enough. That worked perfectly :)

[rocknbil]

This is going to grow on you (**especially** with something like "classifieds"), and coding a bunch of if/else statements will become unwieldy at best. Try something like this:

// This needs to be globally accessible in a configuration so you can change it.
$inputs = array (
'cat' => 'category',
state_id => 'state',
city_id => 'city',
'description' => 'descr'
);

$where = null; // so we can concatenate without error 
foreach ($inputs as $dbfield=>$formfield) { 
 if (isset($_POST[$formfield]) and ! empty($_POST[$formfield])) { 
 // you only need AND if $where has been added to once 
 if ($where) { $where .= ' and'; } 
 $where .= " $dbfield='" . mysql_real_escape_string($_POST[$formfield]) . "'" 
 } 
} 
// 
$query = "select * from classifieds"; 
if ($where) { $query .= " where $where"; } 

You can do similar things to add an order and limit to that.

You might already be wondering about "description" because it's likely a text field and you'd have to decide how you want to partially search that. I usually use four radios ('anywhere' => 'like '%term%', 'starts with' => like 'term%', 'ends with' => like '%term', 'exact' => ='term') and insert it programmatically instead of "=". Topic for another thread (or go with it and figure it out. :-) )