title: Refining search results based on multiple level filters Mysql + PHP
body: <p>I'm trying to create a multilevel search to allow user to refine the same search based on brand, category of product and price range (order is irrelevant).</p>
  
  <p>The following implementation might not work as is as i'm only providing a way to illustrate my problem.</p>
  
  <p><strong>Tables:</strong>
  products</p>
  
  <pre><code>|  id  |  name  | price_id  |
  |  1   |  test1 |     1     |
  |  2   |  test2 |     2     |
  |  3   |  test3 |     1     |
  </code></pre>
  
  <p>prices_ranges</p>
  
  <pre><code>|  id  |  price_from  | price_to  |
  |  1   |     100      |    200    |
  |  1   |     200      |    300    |
  </code></pre>
  
  <p>product_attributes</p>
  
  <pre><code>|  product_id  |  id_attribute  |
  |      1       |        1       |
  |      1       |        2       |
  |      1       |        3       |
  |      1       |        5       |
  |      2       |        5       |
  |      2       |        3       |
  |      3       |        5       |
  </code></pre>
  
  <p>attributes</p>
  
  <pre><code>|  id  |  name     |
  |  1   |   attr1   |
  |  2   |   attr2   |
  |  3   |   attr3   |
  |  4   |   attr4   |
  |  5   |   attr5   |
  |  6   |   attr6   |
  |  7   |   attr7   |
  </code></pre>
  
  <p>I'm passing the arguments in url through $_GET. And my code for the multiple filter search looks like somewhat like this:</p>
  
  <pre><code>//this generates the base query
  $dynamicsql = get_by_filters();
  
  //does the group by to discard duplicated products
  $filtersql= $dynamicsql."group by p.id";
  $query = mysql_query($filtersql) or die(mysql_error());
  //the data is now ready to be presented but 
  $total = mysql_num_rows($query);
  for($i=0; $i&lt;$total; $i++):
          $prods[$i] = mysql_fetch_object($query);
      endfor;
  
  //first let's build the html code for filtering the results
  list_prices_filter($dynamicsql);
  
  //i'll only show the code for the bellow function. the above is pretty similar
  list_attr_filter($dynamicsql);
  
  
  //now i print the results
  foreach($prods as $prod):
        //for the porpuse of this question will only print the product name
        echo $prod['pname'];
  endforeach;
  </code></pre>
  
  <p>The function to return the filtered base query</p>
  
  <pre><code>function get_by_filters(){
  
          $dynamicsql = "SELECT p.name as pname,p.price_from,p.price_to,a.name,a.id FROM `products` p ";
          $dynamicsql .= "left JOIN `price_ranges` pr ON p.price_id = pr.id ";
          $dynamicsql .= "LEFT JOIN `product_attributes` pa ON w.id = pa.product_id ";
          $dynamicsql .= "LEFT JOIN `attributes` a ON a.uid = pa.id_attribute where 1=1 ";
  
          foreach($_GET as $parameter=&gt;$value):
                  switch($parameter):
  
                      case 'price':
              $dynamicsql .= " AND p.price = ".$value;
              break;
  
                      //filtro por zip code
                      case 'attr':
                          $dynamicsql .= " AND a.id = ".$value;
                          break;
                      default:
                          $dynamicsql .= "AND ".$parameter." = ".$value." ";
                      break;
                  endswitch;
          endforeach;
  
          return $dynamicsql;
  }
  </code></pre>
  
  <p>The function to list the attribute selection:</p>
  
  <pre><code>function get_special_care($dynamicsql){
  
      $sql = "SELECT * FROM attributes a";
      $query = mysql_query($sql);
      $total = mysql_num_rows($query);
  
      $output = '&lt;ul&gt;';
      for($i=0; $i&lt;$total;$i++):
          $result[$i] = mysql_fetch_object($query);
          $active = ($_GET['attr'] == $result[$i]-&gt;uid) ? 'active':'';
  
          $workerstotal = mysql_query($dynamicsql  . "  and a.id ={$result[$i]-&gt;uid} group by p.id");
          $totalworkers = mysql_num_rows($workerstotal);
  
          $output .= '
              &lt;li class="'.$active.'"&gt;
                  &lt;a href="'.url_increment('attr='.$result[$i]-&gt;uid).'"&gt;'.$result[$i]-&gt;name.'&lt;span class="totalfound"&gt;('.$totalworkers.')&lt;/span&gt;&lt;/a&gt;
                  &lt;a title="Remove" href="'.url_decrement('attr').'" class="remove-criteria"&gt;&lt;img src="'image_src_url" alt="Remove" /&gt;&lt;/a&gt;
              &lt;/li&gt;';
      endfor;
  
      $output .= '&lt;/ul&gt;';
      echo $output;
  }
  </code></pre>
  
  <p><strong>Function url_increment() and url_decrement just adds or removes that filter to the url. I'm not listing it because I think it's not relevant to the problem.</strong></p>
  
  <p>Well introduction over!! My problem is that if user chooses attribute 5 in the first step the code shows product 1, 2 and 3 but I can't filter through the attributes again!</p>
  
  <p>In this case the function get_special_care($dynamicsql) says that there is 0 products for attribute 1, 2 (and there should be one for product 1) and 3 (should be two matches for product 1 and 2).</p>
  
  <p>I'm almost certain this is because of the function that generates the base query in:</p>
  
  <pre><code>case 'attr':
      $dynamicsql .= " AND a.id = ".$value;
      break;
  default:
  </code></pre>
  
  <p>But I can't figure out how to solve this.</p>
  
  <p>Also there is another problem with this code because once you select the attribute for the first time url looks like www.teste.com/search&amp;attr=5. After clicking the second time (let's say user clicks attribute 1) the url will be www.teste.com/search&amp;attr=5&amp;attr=1 which is a problem also (i think)</p>
  
  <p>Can anyone help? I'm sure many of you have implemented similar algorithyms and if you could help out would be really great.</p>
  
  <p>Lastly sorry for the long post but I wanted to explain myself and maybe it serves as reference for future help...</p>
  
  <p>Thanks in advance!!</p>