search by price

[jgmills]

Hi everyone.

I have a database (MySQL) full of items. I have added a search to search for type etc... however i would like to add an option to only pull results in a certain price range.

Currently i have the price set like this in the database:
itemPrice decimal(4,2)

Could anyone suggest how to move forward?

Thanks

 

[mweinstock]

try this:

SELECT Item, ItemPrice FROM table WHERE ItemPrice > $lowerbound AND ItemPrice < $upperbound

another option is

SELECT Item, ItemPrice FROM table WHERE ItemPrice BETWEEN $lowerbound AND $upperbound

Mark

--
How can you be in two places at once when you're not anywhere at all?

 

[jgmills]

Yer thats what i thought.

However i have a select drop down right.
So i can not really do AND if you get me.

 

[mweinstock]

You said that you want to pull prices in a certain range. How is that range defined? You could have two drop downs, or if you have a single drop down, the VALUE of the OPTIONs should be something that can be separated out to identify the range. Or you can identify the lower (or upper) limit, and use a fixed range size. For example...

$upper=$lower + 10, then
select * from table where ItemPrice BETWEEN $upper and $lower

--
How can you be in two places at once when you're not anywhere at all?

 

[jgmills]

thats a good idea
$upper=$lower + 10;

i have decided to just go a with this:

if ($searchPrice == "0") { $lowerbound = "0.00" ; $upperbound = "500.00" ; }
if ($searchPrice == "1") { $lowerbound = "0.00" ; $upperbound = "5.99" ; }

$q = functionQuery("SELECT * FROM items WHERE itemCountry = '$searchCountry' AND itemType = '$searchType' AND itemPrice > '$lowerbound' AND itemPrice <= '$upperbound' ORDER BY '$orderBy' ASC ");
$counter = mysql_num_rows($q);

Thanks for all help and advise.

 

[mweinstock]

You might also consider using arrays. Something like this:

$lowerbound[0]=0.00;
$upperbound[0]=500.00;
$lowerbound[1]=0.00;
$upperbound[1]=5.99;

That way you can reference the bounds directly through the $searchprice var.

$lowerbound[$searchprice]
$upperbound[$searchprice]

--
How can you be in two places at once when you're not anywhere at all?