using mysql values in php

[joebudden]

hi, im doing a football manager application and need to do things with mysql values in php

+----+------------+---------+---------+
| id | name      | ability | fitness |
+----+------------+---------+---------+
|  1 | Yeboah    |      5 |      5 |
|  2 | Mostovoi  |      5 |      2 |


i want to firstly get a total of both abilities ( i.e. 5 + 5 ) using php

any ideas if this is possible ???

[thedarkwinter]

Hi

In MYSQL:

If they are integer fields, then just

SELECT name,ability+fitness FROM table;

returns

+-------------+----------------+
| name      | ability+fitness        |
+-------------+----------------+
| Yeboah  |            10            |
| Mostovoi |            7              |

or to select the fields individually and then add them
SELECT name,ability,fitness,ability+fitness FROM table;



or in PHP:

<?php

mysql_connect("localhost", "usr", "pass");
mysql_select_db("mydb");
$result = mysql_query("SELECT name,ability,fitness FROM table");
while ($row = mysql_fetch_row($result))
{
  echo "$row[0] - $row[1] - $row[2] - " . ($row[1] + $row[2]) . "\n<br>";
}

?>

hope that helps,

cheers,
tdw

[timmah1]

I believe if you pass the total variable to the 'INSERT' command, it should work

[code]
$ability = 5;
$fitness = 5;
$total = ($ability+$fitness);

echo "$total";

$insert = mysql_query("insert into $table values ('$total')")
[/code]

[joebudden]

yes this works...

$result = mysql_query("SELECT name,ability,fitness FROM player");
while ($row = mysql_fetch_row($result))
{
echo "$row[0] - $row[1] - $row[2] - " . ($row[1] + $row[2]) . "\n";
}


but i want to....

+----+------------+---------+---------+
| id | name      | ability | fitness |
+----+------------+---------+---------+
|  1 | Yeboah    |      5 |      5 |
|  2 | Mostovoi  |      5 |      2 |

....make a total of both players abilities

..then a players ability will change during the match depending on their fitness

[nuntius]

If I'm understanding this correctly, couldn't you just use 2 total variables? then echo them afterwards

[code]
$ability=0;
$fitness=0;
$result = mysql_query("SELECT name,ability,fitness FROM player");
  while ($row = mysql_fetch_row($result))
  {
      echo "$row[0] - $row[1] - $row[2]\n";
      $ability += $row[1]; (or $ability = $ability + $row[1];)
      $fitness += $row[2]; (or $fitness = $fitness + $row[2];)
  }
echo $abilty;
echo $fitness;
[/code]

[joebudden]

...no this isnt what i mean....

i want to build an application that uses the total of 5 players abilities and then decreases at certain intervals depending on the players fitness

example /*************************************

+----+------------+------+---------+---------+
| id | name      | team | ability | fitness |            **ability 20mins**
+----+------------+------+---------+---------+
|  1 | Yeboah    | A    |      5 |      5 |                        6
|  2 | Mostovoi  | A    |      5 |      3 |                        4
|  3 | Rivaldo      | A    |      5 |      5 |                        6
|  4 | Adams      | A    |      4 |      3 |                          3
|  5 | Chilavert  | A    |        3 |      1 |                        1
                         
totalability = 22 / 25

    if fitness == 5 ability = ability ++ 1 
    if fitness == 3 ability = ability -- 1
    if fitness == 1 ability = ability -- 2

so the totalability becomes ..... 20/25

i then want to apply the same rules to a second team , team B

and say the totalability of team B is .....19/25

then i want to have two ifs saying -----

            if (team A totalability > team B totalability )
              {
                  echo ('Team A wins');
                }

          if (team B totability > team A totalability )
              {
                  echo ('Team B wins');
                }

Any ideas any one ???

[joebudden]

any 1 got any ideas???

[HuggieBear]

Use SUM() for that...

[code=php:0]$sql = "SELECT SUM(ability) FROM player WHERE team = 'A'";[/code]

This will select the total ability for the whole of Team A

Regards
Huggie

[joebudden]

what about altering the sum of ability depending on players fitness???

can this be done???

[HuggieBear]

Yes, that can be done too.  With the code that you've been given in this thread, you should now be able to achieve exactly what you need.

Regards
Huggie

[joebudden]

ok im probly gonna work on my application 2nite as i have uni now, il re - post later to show any progress!!

cheers dudes

[HuggieBear]

Your welcome, look forward to seeing the progress.

Regards
Huggie

[joebudden]

hmmmmmmmmm rite///

if i use the code u gave me....

    $sql = "SELECT SUM(ability) FROM player WHERE team = 'A'";

and echo out $sql it just outputs the query...

I'v altered it slightly so it is ...

    $sql = mysql_query("select sum(ability) from player where team='A'",$con);

and this outputs    -->      Resource id #3 ?????

but when i paste the actual query into mysql root client it outputs ......

--------------+
sum(ability) |
--------------+
          22 |
--------------+
row in set (0.00 sec)

which is what I need?????

[HuggieBear]

Sorry, I assumed you knew what you were doing with what I'd posted...

[code]<?php
$sql = "SELECT SUM(ability) AS total FROM player WHERE team = 'A'";
$result = mysql_query($sql);
$row = mysql_fetch_array($result, MYSQL_ASSOC);
echo $row['total'];
?>
[/code]

Regards
Huggie

[craygo]

Chucked this together for ya
[code]<?php
function totalability($fitness, $ability){
  if($fitness == 5){
  $totalability = $ability+1;
  } else {
    if($fitness == 3){
    $totalability = $ability-1;
    } else {
    $totalability = $ability-2;
    }
  }
  return $totalability;
}
$lastteam = "";
$tot_ability = 0;
$sql = "SELECT id, name, team, fitness, ability
        FROM player GROUP BY team, name";
$res = mysql_query($sql) or die (mysql_error());
while($r = mysql_fetch_assoc($res)){
  if($r['team'] != $lastteam){
    if($lastteam != ''){
    echo "<table align=center width=350><tr><td align=right>Total ability: ".$tot_ability."</td></tr></table>";
    $team1 = $tot_ability;
    $tot_ability = 0;
    }
  echo "<table align=center width=350><tr><td align=center><b>TEAM ".$r['team']."</b></td></tr></table>";
  }
echo "<table align=center width=350><tr>
<td width=50>".$r['id']."</td>
<td width=150>".$r['name']."</td>
<td width=50>".$r['ability']."</td>
<td width=50>".$r['fitness']."</td>
<td width=50>".totalability($r['fitness'], $r['ability'])."</td>
</tr>
</table>";

$tot_ability += totalability($r['fitness'], $r['ability']);
$lastteam = $r['team'];
}
$team2 = $tot_ability;
echo "<table align=center width=350><tr><td align=right>Total ability: ".$tot_ability."</td></tr></table>";
if($team1 > $team2){
$outcome = "Team 1 wins $team1 to $team2";
} else {
  if($team1 < $team2){
  $outcome = "Team 2 wins $team2 to $team1";
  } else {
  $outcome = "Team1 Tied Team2, $team1 to $team2";
  }
}
echo "<table align=center width=350><tr><td align=center><b>$outcome</b></td></tr></table>";
?>[/code]

I used some table to output the data. Not gonna spend too much time with the layout you can take care of that.

Ray

[joebudden]

cheers for that craygo

cud i then use another loop to run the function agen but

      with the values that have just been generated so...

TEAM A
4 Adams       3
5 Chilavert 1
2 Mostovoi      4
3 Rivaldo 6
1 Yeboah        6

    becomes

TEAM A
4 Adams       2
5 Chilavert 0
2 Mostovoi      3
3 Rivaldo 7
1 Yeboah        7

  total becomes 19

do u know if this is possible ???

????