Product filtering with MySQL and PHP for opencart

Question

I am trying to make dynamic product filtering for opencart in which user could add filters, assign them to categories and assign filter values for products.

Currently I Have these mysql tables:

1. Filters - ID NAME
2. Filters_categories - ID FILTER_ID CATEGORY_ID
3. Filters_values - ID FILTER_ID VALUE
4. Filters_products - ID FILTER_ID VALUE_ID PRODUCT_ID

The problem is with this structure that i can't get products when more than one filter is activated because i get MySQL query which looks something like this:

SELECT product_id FROM Filters_products WHERE ((name_id='1' OR name_id='2') AND filter_id='1') AND ((name_id='3' OR name_id='4') AND filter_id='2')

And it doesn't make any sense. I can't figure out how should i change my database structure to make dynamic filtering possible. How can I solve this situation? Thank you.

Answer 1

SELECT product_id FROM Filters_products WHERE (screen_id='1' OR screen_id='2') and (connection_id='1')

Answer 2

Use this awesome extension,

http://www.opencart.com/index.php?route=extension/extension/info&extension_id=10098

Hope this helps.

Answer 3

in file /catalog/model/catalog/product.php find line nr. 91 and replace "if {}" block whit this code...

if (!empty($data['filter_category_id'])) {
        if (!empty($data['filter_sub_category'])) {
            $sql .= " AND cp.path_id = '" . (int)$data['filter_category_id'] . "'"; 
        } else {
            $sql .= " AND p2c.category_id = '" . (int)$data['filter_category_id'] . "'";            
        }   

        if (!empty($data['filter_filter'])) {
            $implode = array();

            $filters = explode(',', $data['filter_filter']);

            foreach ($filters as $filter_id) {

                $filterSQL = $this->db->query("SELECT * FROM " . DB_PREFIX . "filter WHERE filter_id = ". $filter_id);

                $implode[$filterSQL->row['filter_group_id']][] = (int)$filter_id;

            }

            foreach($implode AS $implode2) {

                $sql .= " AND (";
                     foreach($implode2 AS $filterID) {
                        $sql .= "(SELECT count(*) FROM product_filter WHERE product_filter.product_id=p.product_id AND product_filter.filter_id=$filterID) OR ";
                     }
                     $sql = substr($sql, 0, -4);
                $sql .= ")";
            }

        }
    }