can't figure min() out.. please help...

szym9341

the below script works fine... it returns a each price for the given style_id.. only problem, I want the minimum price (echo'ed ONCE) for each style... I have used ..."SELECT MIN(our_price) FROM... but I didn't get any results... any ideas?

while ($usestyles = mysql_fetch_array($getstyles)){
$style_id = $usestyles["style_id"];
$getitems = @mysql_query("SELECT our_price FROM items
                          WHERE style_id = $style_id");
while ($useitems = mysql_fetch_array($getitems)){
$our_price = $useitems["our_price"];
echo "<br>";
echo "$our_price";
}
}

Sphynx

This is just a guess from past experience. But whenever I have that happen -- no result for something I know is there -- then it's usually that my WHERE clause is specifying the wrong thing.

So check the contents of $style_id, and run an open query for min(our_price) to see if that works at all. 🙂

szym9341

Thanks... but still no luck. When I run the below script, I get nothing.. when I take out the "min( )" and just have SELECT our_price, I get a single value... but not the lowest one. Am I having a problem with the parenthesis following min??

$trial = @mysql_query("SELECT min(our_price) FROM items");
$gettrial = mysql_fetch_array($trial);
$our_price = $gettrial["our_price"];
echo "$our_price";

Sxooter

I prefer to get min max from a table with order by and limit. It always uses an index on postgresql, and it's easier to figure out what it's doing:

select id from table order by id ASC limit 1

will get the lowest, and

select id from table order by id DESC limit 1

will get the highest.

nemonoman

If your table allows NULL values for the our_price column, than you MIGHT be returning a record where the MIN(our_price) is NULL:

"SELECT style_id, MIN(our_price) FROM items
WHERE style_id = $style_id
GROUP BY style_id

Since you are including multiple columns, you have to GROUP BY something.

If MIN() is not working then you won't return a style_id OR a NULL min. If MIN is working, then you'll return a style id, and a MIN our_price value, which MIGHT be a NULL

Sxooter

if an aggregate function returns a null with min or max, then it, and by extension, your database, are both broken.

aggregate functions, by the SQL spec, should not consider NULLS in their operation.

I.e. if you have three rows, with 0,1 and NULL stored and ask for AVG you should get 0.5, since the NULL shouldn't count on the top or the bottom of the average equation, but should be thrown out.

On the other hand, if it's a string, and you use min, and the string if '', you might get an empty string back, which isn't the same thing as a null.

nemonoman

DOH! You learn something new every day!

szym9341

Wow... thanks for the help! I'll get it working for sure with this help... Thanks again.