how to get a range?

[acidglitter]

for this site i'm working on, we have a search page and want to be able to refine the search by like category and price. is there an easy way with mysql to get a range? like the query i have now selects products by what was searched for, and now i also want it to be able to get the price range. or would i have to do that part in php?

[rhodesa]

SELECT * FROM items WHERE price BETWEEN 1 AND 10

[acidglitter]

i don't want to actually LIMIT the query, i want to be able to select the range as a variable. so the person searching can view the range and then choose how they want to limit it

[rhodesa]

You have to build the query using PHP...so if you have an html form with price_min and price_max, it would look something like:

 

<?php
  if(!is_numeric($_POST['price_min']))
    die("Min is not a number");
  if(!is_numeric($_POST['price_max']))
    die("Max is not a number");
  $sql = "SELECT * FROM items WHERE price BETWEEN {$_POST['price_min']} AND {$_POST['price_max']}";
  $result = mysql_query($sql) or die(mysql_error());
?>

 

[fenway]

You may also want to enforce max > min.

[acidglitter]

thats still limiting it. i mean something like

 

SELECT *, something() AS 'highest_price', something() AS 'lowest_price' FROM products

[fenway]

You mean like

 

SELECT MAX(priceField) AS 'highest_price', MIN(price_field) AS 'lowest_price' FROM products