Updating values in a database?

**chris_stfc** · Jul 1, 2007 07:43

Hi All,

I’m completely stuck with an update script I’ve working on the details aresaved in the database and I want to edit these all in one form.

My first problem:

(1) When I try and call the data out of my database for match_id=1 back into my form it duplicates the the first entry in every form element like so:

Despite my actual database looking like this:

Here's my second problem:

(2) When I try to edit all data and fill in all the boxes with all different data - (ie Update) and hit submit, my data is then saved strangely like this in the MYSQL database like so:

Would anyone be able to help?

PHP Code:


<?php 



if(isset($_GET['match_id'])) 



{ 

  $result = mysql_query("Select * From player_stats where match_id=".$_GET['match_id']); 

  $row = mysql_fetch_array($result); 

  $name = $row['name']; 

  $goals = $row['goals'];  

  $match_id = $row['match_id']; 

} 



if(isset($_POST['Submit'])) 

{ 

    foreach($_REQUEST['r'] as $position => $row)  

  {   

     $x = implode("', '",$row); 

     $position =  $x[0]; 

     $name = $x[1]; 

     $result = mysql_query("UPDATE player_stats SET position='$position', name='$name', goals='$goals', match_id='$match_id' WHERE match_id=".$_GET['match_id']); 

  } 

} 

?> 

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"> 

<html xmlns="http://www.w3.org/1999/xhtml"> 

<head> 

<meta http-equiv="Content-Type" content="text/html; charset=utf-8" /> 

<title>Player stats</title> 

</head> 



<body> 

<form name="statistics" method="post" action=""> 

<table width="100%"> 

  <tr> 

    <td width="33%">Player</td> 

    <td width="33%">Name</td> 

    <td width="33%">Goals</td> 

  </tr> 

  <tr> 

    <td width="33%">Goalkeeper:</td> 

    <td width="33%"><input name="r[goalkeeper][name]" type="text" value="<?php echo $name?>" size="36" /></td> 

    <td width="33%"><input name="r[goalkeeper][goals]" type="text" value="<?php echo $goals?>" size="36" /></td> 

  </tr> 

  <tr> 

    <td width="33%">Player Two</td> 

    <td width="33%"><input name="r[player_two][name]" type="text" value="<?php echo $name?>" size="36" /></td> 

    <td width="33%"><input name="r[player_two][goals]" type="text" value="<?php echo $goals?>" size="36" /></td> 

  </tr> 

  <tr> 

    <td width="33%">Player Three</td> 

    <td width="33%"><input name="r[player_three][name]" type="text" value="<?php echo $name?>" size="36" /></td> 

    <td width="33%"><input name="r[player_three][goals]" type="text" value="<?php echo $goals?>" size="36" /></td> 

  </tr> 

  <tr> 

    <td width="33%">Player Four</td> 

    <td width="33%"><input name="r[player_four][name]" type="text" value="<?php echo $name?>" size="36" /></td> 

    <td width="33%"><input name="r[player_four][goals]" type="text" value="<?php echo $goals?>" size="36" /></td> 

  </tr> 

  <tr> 

    <td width="33%">Player Five</td> 

    <td width="33%"><input name="r[player_five][name]" type="text" value="<?php echo $name?>" size="36" /></td> 

    <td width="33%"><input name="r[player_five][goals]" type="text" value="<?php echo $goals?>" size="36" /></td> 

  </tr> 

</table> 

<input type="submit" name="Submit" value="Submit"/> 

</form> 

</body> 

</html>

Thank you

**IronCode** · Jul 1, 2007 08:03

try something like the following

PHP Code:


<?php 



if(isset($_GET['match_id']))
{ 

  $result = mysql_query("Select * From player_stats where match_id=".$_GET['match_id']); 
  
  foreach($row = mysql_fetch_array($result, MYSQL_ASSOC)) {
    $name = $row['name']; 
    $goals = $row['goals'];  
    $match_id = $row['match_id']; 
    
$html .= "<tr>\n";
    $html .= "<td width=\"33%\">Goalkeeper:</td>"; 
    $html .= "<td width=\"33%\"><input name=\"r[goalkeeper][name]\" type=\"text\" value=\"".$name."\" size=\"36\" /></td>\n"; 
    $html .= "<td width=\"33%\"><input name=\"r[goalkeeper][goals]\" type=\"text\" value=\"".$goals."\" size=\"36\" /></td>\n"; 
    $html .= "</tr>\n"; 
    
  }


} 



if(isset($_POST['Submit'])) 
{ 

  foreach($_REQUEST['r'] as $position => $row)  
  {   
     $x = implode("', '",$row); 
     $position =  $x[0]; 
     $name = $x[1]; 
     $result = mysql_query("UPDATE player_stats SET position='$position', name='$name', goals='$goals', match_id='$match_id' WHERE match_id=".$_GET['match_id']); 

  } 

} 

?> 

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"> 
<html xmlns="http://www.w3.org/1999/xhtml"> 
<head> 
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" /> 
<title>Player stats</title> 
</head> 



<body> 

<form name="statistics" method="post" action=""> 

<table width="100%"> 
  <tr> 
    <td width="33%">Player</td> 
    <td width="33%">Name</td> 
    <td width="33%">Goals</td> 
  </tr> 

  <?php echo $html; ?>

</table> 
<input type="submit" name="Submit" value="Submit"/> 
</form> 
</body> 
</html>

Also you should blindly use $_GET in the query without making sure it's what you expecting. try at least using mysql_real_escape_string() or something.

**chris_stfc** · Jul 1, 2007 09:32

Thanks for your help,

Still a problem though as each of the players is not going to be a goalkeeper ie there is: goalkeeper, player_two etc...

Also getting some errors in this line syntax error, unexpected ')' in :

PHP Code:


foreach($row = mysql_fetch_array($result, MYSQL_ASSOC))

Any ideas?

**IronCode** · Jul 2, 2007 16:55

hehe yeah that foreach was originaly a while then i changed it. So that line should read as follows,

PHP Code:


$r = $row = mysql_fetch_array($result, MYSQL_ASSOC);
foreach($r as $row) {

But i'm confused as to what your trying to do. Are you trying to simply get how many goals each player scored or how many the team scored against each player. Also if every player can be goalie and all your looking for is the number of goals then why are you storing the position to begin with?

I'm assuming since you are tracking the position that there are other positions in the game. Is each player only going to have a single entry in this table for each game or are they going to play multiple positions within the same game?

User Tag List

Thread: Updating values in a database?

Thread Tools

Display

Updating values in a database?

Bookmarks

Bookmarks

Posting Permissions