[Resolved] [Resolved] [Resolved] [Resolved] MySQL/PHP - Form Help Please - Adding Numbers, etc.

[Airdog]

Hello and sorry if what I'm asking for might be a bit unclear, but I think you'll understand my explanation.

The webpage that I have is for an online XBox Live hockey league. Basically it keeps stats, etc. etc. What I want this script to do is accept stats submitted after a game and add them to the previous stats. So basically when a user inputs 1 goal, I want it to add that goal to the previous goal total. This problem is really holding up my development and will solve many more problems in my site.

This code will show all of the players on Toronto and Boston and display them in a table where they can all be edited and saved.

Sorry if I've posted too much code but I figured it was all pretty neccessary.

$query="SELECT * FROM players WHERE Team='Toronto' OR Team='Boston' ORDER BY Team"; 
  $result=mysql_query($query); 
  echo "<center><h1> Game Submission Testing </h1></center>"; 
  if (mysql_num_rows($result) !=0) { 
    echo "<table width=75% align=center>\n"; 
    echo "<form method=POST>\n"; 
    echo "<tr><td>Team</td><td>Name</td><td>Position</td><td>GP</td><td>Goals</td><td>Assists</td><td>SHG</td><td>PPG</td><td>PIM</td></tr>\n"; 
while ($r = mysql_fetch_array($result)) { 
      $id = $r["id"]; 
      $Team = $r["Team"]; 
      $Name = $r["Name"]; 
      $Position = $r["Position"]; 
      $Goals = $r["Goals"]; 
      $Assists = $r["Assists"]; 
      $SHG = $r["SHG"]; 
      $PPG = $r["PPG"]; 
      $PIM = $r["PIM"]; 
      $GP = $r["GP"]; 


  echo "<input type=hidden name=yapp[$id] value='$id'>\n"; 
  echo "<tr><td><input type=text readonly name=yapp[$id][Team] value='$Team' size=20></td>\n"; 
  echo "<td><input type=text readonly name=yapp[$id][Name] value='$Name' size=20></td>\n"; 
  echo "<td><input type=text readonly name=yapp[$id][Position] value='$Position' size=2></td>\n"; 
  echo "<td><input type=text name=yapp[$id][GP] value='$GP' size=2></td>\n"; 
  echo "<td><input type=text name=yapp[$id][Goals] value='$Goals' size=2></td>\n"; 
  echo "<td><input type=text name=yapp[$id][Assists] value='$Assists' size=2></td>\n"; 
  echo "<td><input type=text name=yapp[$id][SHG] value='$SHG' size=2></td>\n"; 
  echo "<td><input type=text name=yapp[$id][PPG] value='$PPG' size=2></td>\n"; 
  echo "<td><input type=text name=yapp[$id][PIM] value='$PIM' size=2></td></tr>\n"; 

} 
echo "<tr><td align=center colspan=8><input type=submit name=submit value=submit></td></tr>\n"; 
echo "</form>";    
echo "</table>"; 
  } else { 
    echo "Couldn't fetch info from the db..."; 
  } 
} else { 

  $db=mysql_connect ("localhost", "USERNAME", "PASSWORD") or die ('I cannot connect to the database because: ' . mysql_error()); 
  mysql_select_db ("rsmeaton_players",$db); 

  while (list ($key) = each ($HTTP_POST_VARS[yapp])) { 
    $query="UPDATE players SET "; 
    foreach ($HTTP_POST_VARS[yapp][$key] as $key1 => $value1) { 
      if (!empty($value1)) { 
        $query.="$key1='$value1', "; 
      } 
    } 
    $query=substr($query, 0,-2); 
    $query.=" WHERE id=$key"; 
    $result=mysql_query($query) or die (mysql_error()); 
  } 
} 

Thanks for any help given!

[bastien]

consider using the sum(fieldname) and group by function to do the adding by player...

if you could post the db structure i can give you a hand...

[Airdog]

Hello, thanks for the response. My db structure goes a little like this:

Database xshocke_players - Table players

id int(5) (auto_increment)
Team varchar(20)

Name varchar(25)

GP int(2)

Goals int(2)
Assists int(3)
Points int(3)

SHG int(2)
PPG int(2)
PIM int(3)

[bastien]

if the $keys value can give you the column name then

while (list ($key) = each ($HTTP_POST_VARS[yapp])) {  

    $query="UPDATE players SET ";  

    foreach ($HTTP_POST_VARS[yapp][$key] as $key1 => $value1) {  

      if (!empty($value1)) {  

[b]         //see if the $key1 variable is the correct column then
         //change the code to add the current value and the input value[/b]
         [color=red]if (($key1=="Goals")||($key1=="Assists")){
            $query.="$key1=$key1+$value1";
        }else{ 

        $query.="$key1='$value1', ";  
    }[/color]

   } 


}  
$query=substr($query, 0,-2);  
$query.=" WHERE id=$key";  
$result=mysql_query($query) or die (mysql_error());  
  }  

[Airdog]

With using your code it had one minor mistake but I fixed it and everything's working properly!

The only other things I had to do was obviously put in the other values that I wanted 'added'. I also just put all of default values in the form as 0.

Thank you so much - this has finally answered the problem I've been having over like the last month, heh.

The site it's being used on is http://www.xshockey.com btw, an online hockey league for NHL 2K4.

Thanks again bastien!