Page 1 of 1
[SOLVED] Printing the thousandths between 2 values [maths]
Posted: Thu Feb 16, 2006 9:11 am
by jayshields
Hi.
My brain hurts
I'm grabbing the highest and lowest values out of my database, which at the moment are 895 and 10995.
I want to display links like this to the user:
Code: Select all
0-999
1000-1999
2000-2999
....
9000-9999
10000-10999
I don't want it to start at 0-999 if there is no value to put it in, I want it to start at the lowest range with a price in it, same with the highest range.
I originally thought the best way to start would be to floor and ceil the highest and lowest values to the nearest 1000, but I can't even get that far.
I was going to do a poor mans job and do a case switch for each range from 0 to 20000 but I thought I would see if this is possible with loops first of all.
Thanks for any input

Posted: Thu Feb 16, 2006 9:39 am
by jayshields
Cracked it. It might have fried my brain but I got there.
Code: Select all
//Fetch the prices and let the user choose a range
$query = 'SELECT MIN(price), MAX(price) FROM cars'; //Build the query
$result = mysql_query($query); //Execute it
//If more than one record was returned
if(mysql_num_rows($result) > 0) {
echo '<span class="title">Price Range</span><br />'; //Show the search links list title
//Set the lowest and highest pricest
$lowest = mysql_result($result, 0, 0);
$highest = mysql_result($result, 0, 1);
echo (floor($lowest / 1000) * 1000).' - '.((floor($lowest / 1000) * 1000) + 999).'<br />';
echo '...<br />';
echo (floor($highest / 1000) * 1000).' - '.((floor($highest / 1000) * 1000) + 999).'<br />';
}
Could've used ceil for the highest range one and then switched the method but I thought I'd stick to what worked and thus save a little time
Haven't put the loop in yet, but I've got it working to as much of a degree so that it's now a no brainer to complete
Edit:
My finished snippet:
Code: Select all
//Fetch the prices and let the user choose a range
$query = 'SELECT MIN(price), MAX(price) FROM cars'; //Build the query
$result = mysql_query($query); //Execute it
//If more than one record was returned
if(mysql_num_rows($result) > 0) {
echo '<span class="title">Price Range</span><br />'; //Show the search links list title
//Set the range start and end
$rangestart = floor(mysql_result($result, 0, 0) / 1000) * 1000;
$rangeend = ceil(mysql_result($result, 0, 1) / 1000) * 1000;
//Loop through all the ranges
for($range = $rangestart; $range < $rangeend; $range += 1000) {
//Fetch the amount of vehicles that are in the price range
$query = "SELECT COUNT(*) FROM cars WHERE price BETWEEN " . $range . " AND " . ($range + 999); //Build the query
$amount = mysql_query($query); //Execute it
//Show the range link
echo '<a href="viewcar.php?field=price&criteria=' . $range . '-' . ($range + 999) . '">£' . $range . ' - £' . ($range + 999) . '</a> (' . mysql_result($amount, 0) . ')<br />';
}
echo '<hr />'; //Show a horizontal rule
}
Is there a better way to find out how many cars are in each price range without executing a query on every loop?