mysql query mistake opencart filter price

Question

I am creating filter module for opencart 2.0 Here is my code:
model/catalog/product.php
function getProducts()

................................... SOME CODES.............
            //Filter products based on slider price range

            if ((isset($this->request->get['lower']))&&(isset($this->request->get['higher'])))
            {
            $sql .=  " AND p.price >='". $this->request->get['lower'] ." ' AND p.price <='". $this->request->get['higher'] ."'" ;
            }

            //Filter products based on price slider

        if (!empty($data['filter_manufacturer_id'])) {
            $sql .= " AND p.manufacturer_id = '" . (int)$data['filter_manufacturer_id'] . "'";
        }

This works great for products don't have special. But i am trying to apply this code for special. So I am changing my SQL query to this:

    if ((isset($this->request->get['lower']))&&(isset($this->request->get['higher'])))
    {
    $sql .=  " AND (CASE WHEN special IS NOT NULL THEN special WHEN discount IS NOT NULL THEN discount ELSE p.price END) >='". $this->request->get['lower'] ." ' AND (CASE WHEN special IS NOT NULL THEN special WHEN discount IS NOT NULL THEN discount ELSE p.price END) <='". $this->request->get['higher'] ."'" ;
    }

But it don't works for products which have special.

I got this error: Unknown column 'special' in 'where clause'

try the function `error_log($sql)` right before `$this->db->query($sql)` and make sure that the tables that contain special prices and discounts are in the query (I may be able to help if you post the resultant `$sql` here) — Abdo Adel, Jun 15 '15 at 22:05
Please see this both answer http://stackoverflow.com/questions/10142583/mysql-error-unknown-column-in-where-clause and http://stackoverflow.com/questions/153598/unknown-column-in-where-clause hope, helpfully you. — HDP, Jun 16 '15 at 09:49

score 0 · Answer 1 · answered Jun 17 '15 at 14:19

Add following before WHERE statement in sql

LEFT JOIN " . DB_PREFIX . "product_special p2s ON p.product_id = ps.product_id LEFT JOIN " . DB_PREFIX . "product_discount p2d ON p.product_id = pt.product_id

then use 'special' as 'p2s.special' and 'discount' as 'p2d.discount' on sql

mysql query mistake opencart filter price

1 Answers1