selectbox dynamic range options

i would like to dynamically output such a selectbox below using php
and mysql:
<select>
<option> < 100 </option>
<option> 100 to 150 </option>
<option> 150 to 200 </option>
<option> 200 to 250 </option>
<option> 250 to 300</option>
<option> > 300 </option>
</select>

the selectbox will show a range of available prices of products in a
table

the table has the following fields:
ID
PRICE
ITEM

so depending on the actual prices in the table, the selectbox will
vary.

if the cheapest thing is $1 and the most expensive thing is $1,000,
then the selectbox will change accordingly.
it will break the prices from $1 to $1,000 into categories:
for example...
< 10
10 to 500
500 to 900
>900

i don't know what kind of sql to write to achieve something like this
could you please guide me in the right way to get started on something
like this?

a good example is: on froogle.com
they also have a similar price range search, which is dynamically
generated according to actual search results.

thanks!

Ok, you do this

1.) you would pull the row with the largest price and the row with the
smallest price. Ex:
quarry 1.) SELECT price FROM table ORDER BY price ASC LIMIT 1

quarry 2.) SELECT price FROM table ORDER BY price DESC LIMIT 1

Those will return the highest and lowest prices.

2.) Subtract the lowest price from the highest price
$result = $high-$low;

3.) divide that result by the amount of rages you want
$num_ranges = 4;
$range = $result/$num_ranges;

4.)the ranges are these
for ($n = 0; $n < $num_ranges; $n++){
echo $low+($range*$n);
echo " - ";
echo $low+($range*($n+1));
}

That should work. I know I didn't write the exact code. Sry

On Aug 14, 7:26 pm, Poppitypop <poppity...@gmail.com> wrote:
> Ok, you do this
>
> 1.) you would pull the row with the largest price and the row with the
> smallest price. Ex:
> quarry 1.) SELECT price FROM table ORDER BY price ASC LIMIT 1
>
> quarry 2.) SELECT price FROM table ORDER BY price DESC LIMIT 1
>
> Those will return the highest and lowest prices.
>
> 2.) Subtract the lowest price from the highest price
> $result = $high-$low;
>
> 3.) divide that result by the amount of rages you want
> $num_ranges = 4;
> $range = $result/$num_ranges;
>
> 4.)the ranges are these
> for ($n = 0; $n < $num_ranges; $n++){
> echo $low+($range*$n);
> echo " - ";
> echo $low+($range*($n+1));
>
> }
>
> That should work. I know I didn't write the exact code. Sry

Thank you for your fast reply!
your idea is very clever and would absolutely work.

now this brings another concern. what if the selectbox displays
options that would return 0 results in the search if selected.

if all of the items in the table had prices of $500, $400, $150 and
$100 lets say.
the selectbox would display

100 - 200
200 - 300
300 - 400
400 - 500

if i chose 200 - 300, the query would return 0 results. there are no
items in the table with a price between 200-300.

How would i make this selectbox "smarter" in a way that it would only
display valid options?

giloosh wrote:
> On Aug 14, 7:26 pm, Poppitypop <poppity...@gmail.com> wrote:
>> Ok, you do this
>>
>> 1.) you would pull the row with the largest price and the row with the
>> smallest price. Ex:
>> quarry 1.) SELECT price FROM table ORDER BY price ASC LIMIT 1
>>
>> quarry 2.) SELECT price FROM table ORDER BY price DESC LIMIT 1
>>
>> Those will return the highest and lowest prices.
>>
>> 2.) Subtract the lowest price from the highest price
>> $result = $high-$low;
>>
>> 3.) divide that result by the amount of rages you want
>> $num_ranges = 4;
>> $range = $result/$num_ranges;
>>
>> 4.)the ranges are these
>> for ($n = 0; $n < $num_ranges; $n++){
>> echo $low+($range*$n);
>> echo " - ";
>> echo $low+($range*($n+1));
>>
>> }
>>
>> That should work. I know I didn't write the exact code. Sry
>
> Thank you for your fast reply!
> your idea is very clever and would absolutely work.
>
> now this brings another concern. what if the selectbox displays
> options that would return 0 results in the search if selected.
>
> if all of the items in the table had prices of $500, $400, $150 and
> $100 lets say.
> the selectbox would display
>
> 100 - 200
> 200 - 300
> 300 - 400
> 400 - 500
>
> if i chose 200 - 300, the query would return 0 results. there are no
> items in the table with a price between 200-300.
>
> How would i make this selectbox "smarter" in a way that it would only
> display valid options?
>

There you'll have to get all of the prices, sort them and figure out
what your ranges should be.

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex@attglobal.net
==================

On Aug 14, 8:21 pm, Jerry Stuckle <jstuck...@attglobal.net> wrote:
> giloosh wrote:
> > On Aug 14, 7:26 pm, Poppitypop <poppity...@gmail.com> wrote:
> >> Ok, you do this
>
> >> 1.) you would pull the row with the largest price and the row with the
> >> smallest price. Ex:
> >> quarry 1.) SELECT price FROM table ORDER BY price ASC LIMIT 1
>
> >> quarry 2.) SELECT price FROM table ORDER BY price DESC LIMIT 1
>
> >> Those will return the highest and lowest prices.
>
> >> 2.) Subtract the lowest price from the highest price
> >> $result = $high-$low;
>
> >> 3.) divide that result by the amount of rages you want
> >> $num_ranges = 4;
> >> $range = $result/$num_ranges;
>
> >> 4.)the ranges are these
> >> for ($n = 0; $n < $num_ranges; $n++){
> >> echo $low+($range*$n);
> >> echo " - ";
> >> echo $low+($range*($n+1));
>
> >> }
>
> >> That should work. I know I didn't write the exact code. Sry
>
> > Thank you for your fast reply!
> > your idea is very clever and would absolutely work.
>
> > now this brings another concern. what if the selectbox displays
> > options that would return 0 results in the search if selected.
>
> > if all of the items in the table had prices of $500, $400, $150 and
> > $100 lets say.
> > the selectbox would display
>
> > 100 - 200
> > 200 - 300
> > 300 - 400
> > 400 - 500
>
> > if i chose 200 - 300, the query would return 0 results. there are no
> > items in the table with a price between 200-300.
>
> > How would i make this selectbox "smarter" in a way that it would only
> > display valid options?
>
> There you'll have to get all of the prices, sort them and figure out
> what your ranges should be.
>
> --
> ==================
> Remove the "x" from my email address
> Jerry Stuckle
> JDS Computer Training Corp.
> jstuck...@attglobal.net
> ==================

Yea, makes sense. I would grab all of the prices and put them into one
array and do a bunch of sorting with the array. Could you please give
me an example of what kind of sorting i could do with the array of
prices? I am still a but stuck on that.
Thank you very much for your help!

giloosh wrote:
> On Aug 14, 8:21 pm, Jerry Stuckle <jstuck...@attglobal.net> wrote:
>> giloosh wrote:
options?
>> There you'll have to get all of the prices, sort them and figure out
>> what your ranges should be.
>>
>
> Yea, makes sense. I would grab all of the prices and put them into one
> array and do a bunch of sorting with the array. Could you please give
> me an example of what kind of sorting i could do with the array of
> prices? I am still a but stuck on that.
> Thank you very much for your help!
>

Check out http://us.php.net/manual/en/function.sort.php and
http://us.php.net/manual/en/function.asort.php for starters.


--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex@attglobal.net
==================