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 :P

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 :P

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?