MYSQL: Finding highest column value from a particular row

[andrewgarn]

Ok so if I have a table set out like this

 

id

number 1

number 2

number 3

 

I'd want to locate data from the row where id = 3, but retrieve information from the column with the highest value in it, ie number 1, 2 or 3.

 

Is this possible? or do i need to retrieve all the data then use a set of nested ifs to find the highest?

[ratcateme]

i think it is best to get all three fields then compare them

 

Scott.

[andrewgarn]

The problem with that is my table doesnt have 3 fields

 

it has about 25 which i need to find largest of

 

how do you compare 25 variables easily?

[ratcateme]

if you get a array with just the numbers in them then use rsort() the top element will be the largest value try it but i don't know if it will rember if the array keys.

 

Scott.

[andrewgarn]

will that keep the column name though? I need to use the column name afterwards

[ratcateme]

i did some reading on php.net and i don't think it does try this it should work arsort()

 

Scott.

[andrewgarn]

<?php

$sql = mysql_query("select * from table where username = 'user1'")
while($r=mysql_fetch_array($sql)) {
     $var1 = $r["var1"];
     $var2 = $r["var2"];
     $var3 = $r["var3"];
}
?>

 

Not sure how to insert value + variable name into array

 

then how to sort into order, then retrieve the 3 highest?

 

something to do with array compact? I dont really use arrays much

[.josh]

$id = 3; // example
$sql = "select * from table where id = $id";
$result = mysql_query($sql);
if ($result) {
   $nums = mysql_fetch_assoc($result);
   arsort($nums);
}

[Barand]

I really think those who can't be bothered to normalize their data deserve to struggle, but here

 

$sql = "SELECT a.number1, a.number2, a.number3 
        FROM andrewgarn a
        WHERE id = 1";
$res = mysql_query($sql);
$row = mysql_fetch_assoc($res);
$hival = max($row);                            // find highest value
$colname = array_search($hival, $row);         // find it's column name

echo "$hival : $colname";

[andrewgarn]

Thanks, i'll give it a try