Help with sorting by price range

[squigs]

Hi I'm looking to add an option of sorting items by price range and have found the following query example from another source but it produces syntax errors. Help with this would be appreciated.

 

<?php
$refinePriceSQL = mysql_query("SELECT p.range, COUNT(*) AS numbers
                                    FROM (select case 
								when low_price =< 100 then ' Under $100'
   								        when low_price >= 100 and low_price < 150 then ' $100-$150'
    								        when low_price >= 150 and low_price < 200 then ' $150-$200'
								when low_price >= 200 and low_price < 250 then ' $200-$250'
								when low_price >= 250 and low_price < 300 then ' $250-$350'
								when low_price >= 300 and low_price < 350 then ' Over $350'
								else '350-100000000' end as low_price, range
  									from inventory) as p
                                    WHERE item_name LIKE '%$searchTermDB%' OR item_desc LIKE '%$searchTermDB%' OR a.sub_category_b LIKE                                          '%$searchTermDB%'
                                    GROUP BY p.range");	
?>

[requinix]

Where'd you get it from?

[squigs]

Forget the source its a popular sql forum site but I've got it working using this:

$refinePriceSQL = mysql_query("SELECT *, price_range, COUNT(*) AS numbers
                                     FROM (SELECT CASE 
                                                      WHEN low_price < 100 THEN 'Under $100'
					      WHEN low_price >= 100 AND low_price <= 150 THEN '$100-$150'
                                                      WHEN low_price >= 150 AND low_price <= 200 THEN '$150-$200'
                                                      WHEN low_price >= 200 AND low_price <= 250 THEN '$200-$250'
                                                      WHEN low_price >= 250 AND low_price <= 300 THEN '$250-$300'
                                                      WHEN low_price >= 300 AND low_price <= 350 THEN '$300-$350'
                                                      WHEN low_price >= 350 AND low_price <= 400 THEN '$350-$400'
                                                      WHEN low_price >= 400 AND low_price <= 450 THEN '$400-$450'
					      ELSE 'Over $500'
                                                      END as price_range
                                                      FROM inventory a
                                    WHERE 1 AND item_name LIKE '%$searchTermDB%' OR item_desc LIKE '%$searchTermDB%' 
                                                  OR a.sub_category_b LIKE '%$searchTermDB%') b
                                    GROUP BY price_range
                                                     ")

[squigs]

Now is there anyway to only showing entries with more than 1 entry?

[requinix]

Use a HAVING at the end of your query. It's just like a WHERE except it happens near the end of the query lifecycle (and can thus use column names in its expressions).

...HAVING numbers > 1

[squigs]

Thanks for the hints!