基于多级过滤器Mysql + PHP优化搜索结果

Question

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).

The following implementation might not work as is as i'm only providing a way to illustrate my problem.

Tables: products

|  id  |  name  | price_id  |
|  1   |  test1 |     1     |
|  2   |  test2 |     2     |
|  3   |  test3 |     1     |

prices_ranges

|  id  |  price_from  | price_to  |
|  1   |     100      |    200    |
|  1   |     200      |    300    |

product_attributes

|  product_id  |  id_attribute  |
|      1       |        1       |
|      1       |        2       |
|      1       |        3       |
|      1       |        5       |
|      2       |        5       |
|      2       |        3       |
|      3       |        5       |

attributes

|  id  |  name     |
|  1   |   attr1   |
|  2   |   attr2   |
|  3   |   attr3   |
|  4   |   attr4   |
|  5   |   attr5   |
|  6   |   attr6   |
|  7   |   attr7   |

I'm passing the arguments in url through $_GET. And my code for the multiple filter search looks like somewhat like this:

//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<$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;

The function to return the filtered base query

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=>$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;
}

The function to list the attribute selection:

function get_special_care($dynamicsql){

    $sql = "SELECT * FROM attributes a";
    $query = mysql_query($sql);
    $total = mysql_num_rows($query);

    $output = '<ul>';
    for($i=0; $i<$total;$i++):
        $result[$i] = mysql_fetch_object($query);
        $active = ($_GET['attr'] == $result[$i]->uid) ? 'active':'';

        $workerstotal = mysql_query($dynamicsql  . "  and a.id ={$result[$i]->uid} group by p.id");
        $totalworkers = mysql_num_rows($workerstotal);

        $output .= '
            <li class="'.$active.'">
                <a href="'.url_increment('attr='.$result[$i]->uid).'">'.$result[$i]->name.'<span class="totalfound">('.$totalworkers.')</span></a>
                <a title="Remove" href="'.url_decrement('attr').'" class="remove-criteria"><img src="'image_src_url" alt="Remove" /></a>
            </li>';
    endfor;

    $output .= '</ul>';
    echo $output;
}

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.

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!

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).

I'm almost certain this is because of the function that generates the base query in:

case 'attr':
    $dynamicsql .= " AND a.id = ".$value;
    break;
default:

But I can't figure out how to solve this.

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&attr=5. After clicking the second time (let's say user clicks attribute 1) the url will be www.teste.com/search&attr=5&attr=1 which is a problem also (i think)

Can anyone help? I'm sure many of you have implemented similar algorithyms and if you could help out would be really great.

Lastly sorry for the long post but I wanted to explain myself and maybe it serves as reference for future help...

Thanks in advance!!

Answer 1

I managed to solve this issue as follows:

created a function to get all atributes from url (normal $_GET['name'] ony returns attribute=5 in an url like www.test.com/?mod=search&attribute=2&attribute=5

function getFilter(){
        $query  = explode('&', $_SERVER['QUERY_STRING']);
        $params = array();
    foreach( $query as $param )
    {
      list($name, $value) = explode('=', $param);
      $params[urldecode($name)][] = urldecode($value);
    }
    return $params;

}

then i changed function get_by_filters

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=>$value):
            switch($parameter):

                case 'price':
        $dynamicsql .= " AND p.price = ".$value;
        break;

                //changes here
                case 'attr':
                    $get = tools::getFilter();
            foreach ($get['attr'] as $attrs => $value) {
        $filter .= " and w.uid in (select a from `product_attributes` where attribute_id= {$value}) ";
                    }
                default:
                    $dynamicsql .= "AND ".$parameter." = ".$value." ";
                break;
            endswitch;
    endforeach;

    return $dynamicsql;
}

and finally we have to present all the active attribute filters so I changed this function likes this:

function get_special_care($dynamicsql){

    $sql = "SELECT * FROM attributes a";
    $query = mysql_query($sql);
    $total = mysql_num_rows($query);

    //changes here        
    $get = tools::getFilter();

    $output = '<ul>';
    for($i=0; $i<$total;$i++):
        $result[$i] = mysql_fetch_object($query);

        //changes here
        foreach ($get['attr'] as $attr => $value) {
        $active = ($value == $result[$i]->uid) ? 'active':$active;
    }


        $workerstotal = mysql_query($dynamicsql  . "  and a.id ={$result[$i]->uid} group by p.id");
        $totalworkers = mysql_num_rows($workerstotal);

        $output .= '
            <li class="'.$active.'">
                <a href="'.url_increment('attr='.$result[$i]->uid).'">'.$result[$i]->name.'<span class="totalfound">('.$totalworkers.')</span></a>
                <a title="Remove" href="'.url_decrement('attr').'" class="remove-criteria"><img src="'image_src_url" alt="Remove" /></a>
            </li>';
    endfor;

    $output .= '</ul>';
    echo $output;
}