column types and searching between price ranges

[garethj]

Hi,

I'm trying to have 2 columns in a table - one column is "price from" and the other is "price to" - allowing users to input their price ranges.

2 questions:

1. what column types should i use here? i've used 'enum' types where users can select 1 of 6 different choices, i.e. $500, $600, etc... - seems like this isn't the best way to go.

2. I want to allow the users (when searching on the web) to specify a price range - search from this price to that price... using the enum column types only pulls up exact searches and not ranges. e.g. 'search for $500 to $1000' only pulls up instances where the user specified those exact values and will not pull up any price between those #'s like $800... any suggestions?

thanks for your help, greatly appreciated.

[raymie]

I am a bit confused here on what you are actually asking but here goes....

if you want to store the range values on the database and you are having the users enter numeric data then it only seems sensible to store the fields in a numeric format. Since the data is a price I would tend to store it in an integer field in pence (GBP) or cents (USA).

If you are asking what is the best way to ask for the field on the screen, then I would just use a text box unless you really want to limit the choices in which case a select box could be used (and the data could be stored as an enum).

You don't really say what and how you are then using these values for with regards to the searching you mention but I assume if searching a database you know to build an sql statement along the lines of

$query = "select product from table where '$low_range' <= price and $high_range' >= price";

[garethj]

thanks Raymie, that answers my question. great help!