Find lowest value from query result

[RLJ]

Hi all,

 

Just a pretty basic question that I can't find the asnwer to:

 

I have a MySQL query of the following form:

$result = mysql_query (" SELECT $cols FROM tablename   WHERE.......");

Where $cols is as follows:

$cols = firstname, lastname, $price1, $price2, etc.

($price1 and $price2 are variable column names that contains prices)

 

How do I find the lowest value for $price1 and/or $price2 out of the results set of this query?

 

Thanks!

[Skewled]

Added the MIN (will only work if the field is numeric otherwise you'll have to add CAST to it MIN(CAST()) ) function to the prices:

 

$cols = firstname, lastname, MIN($price1), MIN($price2), etc.

 

Now we can gather the information using this query and only ouput the lowest price:

 

$result = mysql_query (" SELECT $cols FROM tablename GROUP BY firstname LIMIT 1");

 

I haven't tried the above query but hope it helps you figure this out.

[RLJ]

Thanks for your help, but I think it's not quite what I need.

 

I would like to leave $cols untouched so that my query produces an array of several rows or results and then - in addition - I would like to find the lowest values of $price1 and $price2 out of this results array.

 

And btw, values for columns $price1 and $price2 are either numeric or "NO" (the datatype is VARCHAR).

 

So the output that I want would be something like this:

-------------------------------------------------------------------------

John  Jackson  0.13  25

Bob  Johnson 0.14  NO

Tom  Marley    NO    21

Russ Brady    0.19  22

 

lowest $price1: 0.13

lowest $price2: 21

---------------------------------------------------------------------------

So I don't actually need to know the other data stored in the same row as the lowest price, just the price.

 

So I was hoping I could write some PHP that would take $result and produce the lowest price values, but I don't really know how to do this. Any ideas? Thanks!

[RLJ]

.....never mind. Solved it myself using a while loop on $result and comparing the new and old value of the price each time. Thanks anyway though.