Selecting minumum and maximum values, Please Help!

discostudio

I have read absolutely everything I can find on this subject and I am either looking in the wrong places or the answer really isn't out there.

Here goes:

I have have a price versions/options table where each product has several price options all stored as seperate rows in the price versions table.

I need to select the lowest priced row and the highest price row for any given product: eg:

$sql=mysql_query("SELECT FROM `tablename` min(Price) AND max(PRICE); ");

I know that wouldnt work but that's the kind of thing.

<?php if (mysql_num_rows() >1) { 
while($r=mysql_fetch_array($sql)) { ?>
Price From £199.99 to £249.00
<?php } } else { 
$r=mysql_fetch_array($sql); ?>
Price: £199.99
<?php } ?>

Does anyone know what that query should be?

discostudio

http://www.phpbuilder.com/board/showthread.php?s=&threadid=10291832

I found this:

 "SELECT MIN(`price`) AS `min_price`, MAX(`price`) AS `max_price` FROM `price_options`;   "

Maybe this would work but how would i extract the results? Would they be in a single row?

jbx

Yeah, that would work just fine. If you had a table with say 100 different products and each product had 3 variations (or price options) eg. budget, mid-range and hi-end and you wanted to extract the min and max price for a product with part number 001 then you would code:

SELECT MIN(`price`) AS `min_price`, 
MAX(`price`) AS `max_price` 
FROM `price_options` 
WHERE partnumber = '001';

This would give you a single row in your result containing two prices - $r[min_price] & $r[max_price].

Sounds like you're on the right track there...

discostudio

Excellent, that worked!

The reason i was getting it wrong was i assumed that the low price would be in one row and the high price would be in the second row.

The result can be found here:

http://www.discostudio.co.uk/djshop/product.php?cat_id=5

However, am currently using the following:

<?php
$ver_query_2 = mysql_query("SELECT MIN(`ver_price`) AS `min_price`, MAX(`ver_price`) AS `max_price` FROM `prod_versions` 
WHERE `ver_prod` = '".$r[product_id]."' AND `ver_type` = '1';   ");
$price = mysql_fetch_array($ver_query_2); 
if ($price['min_price'] == $price['max_price']) { ?>

Price - <?php echo CURSYMBOL.number_format($price['min_price'], 2); ?>
			  <?php } else { ?>
			 Price From - <?php echo CURSYMBOL.number_format($price['min_price'], 2); ?> to <?php echo CURSYMBOL.number_format($price['max_price'], 2); ?>
<?php } ?>

But is this the best way to extract the data from the row:

$price = mysql_fetch_array($ver_query_2);

or is there a better way?