Page 1 of 1

Looking for a function

Posted: Fri Dec 02, 2005 2:33 pm
by InnerShadow
Is there any function that will take variables that are generated by a sql query and return the one that has the highest numerical value?

for instance, i have these variables:

Code: Select all

<?php
$conn = mysql_connect("localhost", "root", "");
mysql_select_db("countries", $conn);

$result = mysql_query("SELECT UnitedStates, GreatBritain, France, Spain, Nicaragua, Hawaii, Panama, Jamaica, Sudan, Libya, India, Algeria, Vietnam, CostaRica, Philippines, China, PuertoRico, Cuba FROM forts WHERE Country = '$ID'") 
  or die(mysql_error()); 
while($row = mysql_fetch_assoc($result))
{
  $USF = $row['UnitedStates'];
  $GRF = $row['GreatBritain'];
  $FRF = $row['France'];
  $SPF = $row['Spain'];
  $NIF = $row['Nicaragua'];
  $HAF = $row['Hawaii'];
  $PAF = $row['Panama'];
  $JAF = $row['Jamaica'];
  $SUF = $row['Sudan'];
  $LIF = $row['Libya'];
  $INF = $row['India'];
  $ALF = $row['Algeria'];
  $VIF = $row['Vietnam'];
  $COF = $row['CostaRica'];
  $PHF = $row['Philippines'];
  $CHF = $row['China'];
  $PUF = $row['PuertoRico'];
  $CUF = $row['Cuba'];
}

?>
each of them has a numerical value, and i want it to do something like the max() function, but i want it to return the name of the variable as well? Is that even possible?

Posted: Fri Dec 02, 2005 2:55 pm
by Jenk

Code: Select all

max($row);

Posted: Fri Dec 02, 2005 2:56 pm
by neophyte
How about adding

Code: Select all

ORDER BY DESC LIMIT 1
to the end of your query?

Posted: Fri Dec 02, 2005 2:59 pm
by foobar
neophyte wrote: ORDER BY Country DESC LIMIT 1
Fixed! :wink:

Posted: Fri Dec 02, 2005 4:11 pm
by John Cartwright
foobar wrote:
neophyte wrote: ORDER BY Country DESC LIMIT 1
Fixed! :wink:
Country is not the column that has the numerical values.. its the id :wink:

Posted: Fri Dec 02, 2005 5:33 pm
by Jenk
To make up for my obvious miss of your line post-snippet, try this:

Code: Select all

<?php
$conn = mysql_connect("localhost", "root", "");
mysql_select_db("countries", $conn);

$result = mysql_query("SELECT UnitedStates, GreatBritain, France, Spain, Nicaragua, Hawaii, Panama, Jamaica, Sudan, Libya, India, Algeria, Vietnam, CostaRica, Philippines, China, PuertoRico, Cuba FROM forts WHERE Country = '$ID'")
  or die(mysql_error());
while($row = mysql_fetch_assoc($result))
{ 
  foreach($row as $key => $val) {
    if (isset($$key)) {
      $$key += $val;
    } else {
      $$key = $val;
    }
}

?>
You now have all the countries scores stored in a variable with their name (e.g. $UnitedStates)

Or instead of posting in seperate variables, use an array:

Code: Select all

<?php

$totals = array();

while ($row = mysql_fetch_assoc($result)) {
    foreach($row as $key => $val) {
        if (isset($totals[$key])) {
            $totals[$key] += $val;
        } else {
            $totals[$key] = $val;
        }
    }
}

$winner = max($totals);
$totals = array_flip($totals);

echo "The winner is {$totals[$winner]}!!";

?>