title: mySQL Search Statement with Multiple filters
body: <p>I've been trying to figure this out for a few days now, but haven't been able to find a solution.  Most likely due to the fact that I don't think I'm asking the right question.</p>
  
  <p>Here is goes...</p>
  
  <p>I am trying to create a search on my website of a list of attorneys.</p>
  
  <pre><code>Table 1
  ID (primary)
  Name
  Category1 (fkey)
  Category2 (fkey) 
  Category3 (fkey)
  Category4 (fkey)
  Category5 (fkey)
  Location1 (fkey)
  Location2 (fkey)
  Location3 (fkey)
  Location4 (fkey)
  Location5 (fkey)
  
  Table 2 - Locations
  ID (primary)
  Name 
  
  Table 3 - Categories
  ID (primary)
  Name
  </code></pre>
  
  <p>So Attorneys have multiple categories and multiple locations -> one to Many Relationship with both Table 2 and Table 3</p>
  
  <p><strong>First Question:</strong>  Do I have Table 1 set up correctly? Do I need to have multiple location and category columns (ie location1, location2, location3, etc...)  Or am I complicating this?</p>
  
  <p><strong>Next...</strong></p>
  
  <p>I want a checkbox style search on my site.  A user can search by Location and/or by Category.  And with checkbox they can choose multiple locations and/or categories.  The checkbox values are the IDs of the locations and categories (not the names)</p>
  
  <p><strong>So three ways this can happen.</strong>  </p>
  
  <ol>
  <li>Search by locations ONLY</li>
  <li>Search by categories ONLY</li>
  <li>Search by categories within locations</li>
  </ol>
  
  <p><strong>I have two problems.</strong>  </p>
  
  <ol>
  <li>I can get scenarios 1 &amp; 2 to work, but only if ONE checkbox is selected.</li>
  <li>I have no idea how to even begin to get scenario 3 to work.</li>
  </ol>
  
  <p>Here is what I have for scenario 1 &amp; 2</p>
  
  <pre><code>$AttorneyLocation = $_POST['AttorneyLocation'];
  
  for ($i="0"; $i&lt;count($AttorneyLocation); $i++) {
      if (!is_numeric($AttorneyLocation[$i])) {
          $AttorneyLocation[$i]="";
      }
      if (empty($AttorneyLocation[$i])) {
          unset($AttorneyLocation[$i]);
      }
  }
  
  $AttorneyLocation = implode (" OR ", $AttorneyLocation);
  
  $sqlCommand = "SELECT att_id, att_name, att_logo, att_addy, att_town, att_profile_url FROM attorneys WHERE att_location1='$AttorneyLocation' OR att_location2='$AttorneyLocation' OR att_location3='$AttorneyLocation' OR att_location4='$AttorneyLocation' OR att_location5='$AttorneyLocation'";
  </code></pre>
  
  <p>Again, this works but only when ONE checkbox is selected, it fails when two or more are selected.  Basically it seems to only search the LAST checkbox that has been selected, ignoring the ones before it.</p>
  
  <p><strong>For scenario 3</strong> - Again I'm just not sure where to start, how do I join together the category search within the location search?</p>
  
  <p>If someone can point me in the right direction that would be great, thanks so much!  This is my first try creating something like this!</p>
  
  <p>Here is my form code if necessary - all created dynamically</p>
  
  <pre><code> &lt;input type='checkbox' name='AttorneyCategory[]' value='$cat_id'&gt; $category&lt;br /&gt;
   &lt;input type='checkbox' name='AttorneyLocation[]' value='$loc_id'&gt; $location&lt;br /&gt;
  </code></pre>