I'm trying to create a function that will select products that have the same model and then sort / order by price . The result I am getting though is finding the products with the same model but isn't ordering by price and I am struggling to work out why. Any help for an SQL noob would be much appreciated!
.Current result:
Code: Select all
Array ( [0] => Array ( [product_id] => 30 [name] => Canon EOS 5D [price] => $146.00 ) [1] => Array ( [product_id] => 34 [name] => iPod Shuffle [price] => $26.00 ) [2] => Array ( [product_id] => 42 [name] => Apple Cinema 30" [price] => $122.00 ) [3] => Array ( [product_id] => 47 [name] => HP LP3065 [price] => $218.00 ) )Code: Select all
Array ( [0] => Array ( [product_id] => 34 [name] => iPod Shuffle [price] => $26.00 ) [1] => Array ( [product_id] => 42 [name] => Apple Cinema 30" [price] => $122.00 ) [2] => Array ( [product_id] => 30 [name] => Canon EOS 5D [price] => $146.00 ) [3] => Array ( [product_id] => 47 [name] => HP LP3065 [price] => $218.00 ) )Code: Select all
public function getProductsAndComparePrice($model) {
$query = $this->db->query("SELECT * FROM " . DB_PREFIX . "product p LEFT JOIN " . DB_PREFIX . "product_to_store p2s ON (p.product_id = p2s.product_id) WHERE p.model = '" . $model . "' AND p.status = '1' AND p.date_available <= NOW() AND p2s.store_id = '" . (int)$this->config->get('config_store_id') . " ORDER BY (CASE WHEN special IS NOT NULL THEN special WHEN discount IS NOT NULL THEN discount ELSE p.price END)'");
$product_data = array();
foreach ($query->rows as $result) {
$product_data[] = $this->getProduct($result['product_id']);
}
return $product_data;
}
