UPDATE problem

Mr Chris · June 25, 2007

Morning All,

I was kindly helped yesterday write a query to insert data into a database, and now I want to write a script to edit the data. I've got so far (see below) but failed! Here's the scenario:

I'm building a site for statistical data for a five aside football team. So my team has five players

- goalkeeper

- player_two

- player_three

- player_four

- player_five

and each match we play in is given a distinct match_id. Now for each player I record the following data:

position (ie goalkeeper, player_two)

name

goals

and then this data is nicely saved into a database as so:

And here’s the code for this, which works great:

<?php

if(isset($_POST['Submit']))
{
  $match_id=mysql_insert_id();
  if(!isset($_GET['match_id']))
  { $match_id = $_GET['match_id'];
    foreach($_REQUEST['r'] as $position => $row) 
    
  {   $row = implode("', '",$row);
      $result = mysql_query("Insert into player_stats(position,name,goals,match_id)   
                             values('$position','$row','$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"></td>
    <td width="33%"><input name="r[goalkeeper][goals]" type="text" /></td>
  </tr>
  <tr>
    <td width="33%">Player Two</td>
    <td width="33%"><input name="r[player_two][name]" type="text" /></td>
    <td width="33%"><input name="r[player_two][goals]" type="text" /></td>
  </tr>
  <tr>
    <td width="33%">Player Three</td>
    <td width="33%"><input name="r[player_three][name]" type="text" /></td>
    <td width="33%"><input name="r[player_three][goals]" type="text" /></td>
  </tr>
  <tr>
    <td width="33%">Player Four</td>
    <td width="33%"><input name="r[player_four][name]" type="text" /></td>
    <td width="33%"><input name="r[player_four][goals]" type="text" /></td>
  </tr>
  <tr>
    <td width="33%">Player Five</td>
    <td width="33%"><input name="r[player_five][name]" type="text" /></td>
    <td width="33%"><input name="r[player_five][goals]" type="text" /></td>
  </tr>
</table>
<input type="submit" name="Submit" value="Submit"/>
</form>
</body>
</html>

But here’s my new problem. Say I enter a players name wrong, or the amount of goals he scored incorrect I want to be able to edit that data. So I’d like to:

- Grab all the details in the database where (get) .com?match_id=1 (as an example).

- Put the values back in their corresponding textboxes on my form.

- Then modify their values and hit submit which updates accordingly in the MYSQL database.

Now I’ve tried, and think I’m partically there, but got stuck (see below). Can anyone help?

Thanks

<?php

// Get the data where report id=get value at url
if(isset($_GET['report_id']))

{
  $result = mysql_query("Select * From player_stats where report_id=".$_GET['report_id']);
  $row = mysql_fetch_array($result);
  $name = $row['name'];
  $goals = $row['goals'];
  }

// Submit the data using foreach
if(isset($_POST['Submit']))
{
  $match_id=mysql_insert_id();
  if(isset($_GET['match_id']))
  { $match_id = $_GET['match_id'];
    foreach($_REQUEST['r'] as $position => $row) 
    
  {   $row = implode("', '",$row);
      $result = mysql_query("UPDATE player_stats SET(position,name,goals,match_id)   
                             values('$position','$row','$match_id' where report_id=".$_GET['report_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>

pocobueno1388 · June 25, 2007

Your update query is not correct.

Change this:

<?php
      $result = mysql_query("UPDATE player_stats SET(position,name,goals,match_id)   
                             values('$position','$row','$match_id' where report_id=".$_GET['report_id']);
?>

To This:

<?php
   $result = mysql_query("UPDATE player_stats SET position='$position', name='$row'...etc WHERE report_id=".$_GET['report_id']."");
?>

You have an uneven count though....you are trying to change 4 rows, but you have only 3 variables

Mr Chris · June 25, 2007

Thanks,

Yep there are 3 variables as both name and position I run an implode:

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

How do I show this on an update statement like I do with my insert statement?

Thanks

Chris

Sign In

UPDATE problem

Recommended Posts

Mr Chris

Link to comment

Share on other sites

pocobueno1388

Link to comment

Share on other sites

Mr Chris

Link to comment

Share on other sites

Archived

Browse

Activity

Important Information