php mysql case syntax mistake

Here is part of opencart:

model/catalog/product.php

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

$sql .= " GROUP BY p.product_id";

$sort_data = array(
    'pd.name',
    'p.model',
    'p.quantity',
    'p.price',
    'rating',
    'p.sort_order',
    'p.date_added'
);

if (isset($data['sort']) && in_array($data['sort'], $sort_data)) {
    if ($data['sort'] == 'pd.name' || $data['sort'] == 'p.model') {
        $sql .= " ORDER BY LCASE(" . $data['sort'] . ")";
    } elseif ($data['sort'] == 'p.price') {
        $sql .= " ORDER BY (CASE WHEN special IS NOT NULL THEN special WHEN discount IS NOT NULL THEN discount ELSE p.price END)";
    } else {
        $sql .= " ORDER BY " . $data['sort'];
    }
} else {
    $sql .= " ORDER BY p.sort_order";
}

I am trying to create “price range” filter. And here is my code:

    //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'] . "'";
}

$sql .= " GROUP BY p.product_id";

$sort_data = array(
    'pd.name',
    'p.model',
    'p.quantity',
    'p.price',
    'rating',
    'p.sort_order',
    'p.date_added'
);

if (isset($data['sort']) && in_array($data['sort'], $sort_data)) {
    if ($data['sort'] == 'pd.name' || $data['sort'] == 'p.model') {
        $sql .= " ORDER BY LCASE(" . $data['sort'] . ")";
    } elseif ($data['sort'] == 'p.price') {
        $sql .= " ORDER BY (CASE WHEN special IS NOT NULL THEN special WHEN discount IS NOT NULL THEN discount ELSE p.price END)";
    } else {
        $sql .= " ORDER BY " . $data['sort'];
    }
} else {
    $sql .= " ORDER BY p.sort_order";
}

It works! But it doesn’t work when products have special price from our store. So I’m trying to copy some code from SORT BY PRICE. I copied this SQL:

(CASE WHEN special IS NOT NULL THEN special WHEN discount IS NOT NULL THEN discount ELSE p.price END)

to my custom code:

    //Filter products based on slider price range

    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'] ."'" ;
    }

    //Filter products based on price slider

But still doesn’t work. I get this error:

Notice: Error: Unknown column ‘special’ in ‘where clause’
Error No: 1054


Source: syntax

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.