[RESOLVED] can someone help me with an update problem please

7724

Hi,

I’m getting some database rows out of a mysql db table using mysql_fetch_array. This works fine, but when I try to update the table with the values outputted using foreach the table does not update. Can anyone tell me where I am going wrong?

Thanks

Chris

<?php

// Get the data from the league table
$databaseInfo = mysql_query("SELECT * FROM `league_table` WHERE league='p' ORDER BY team_points_total DESC, team_gf-team_ga DESC") or die(mysql_error()); 

// Update the new values in the database
if(isset($_POST['Submit']))
{

foreach($_REQUEST['r'] as $team_wins => $row)
{ 
  $result = mysql_query("Update league_table set team_wins='".$row['team_wins']."' WHERE league='p'") OR DIE(mysql_error());
  $msg = "League Table Updated";
}

} //end of post

?>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<title></title>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
</head>

<body>
<?php echo $msg;?> 
<table width="937" border="0" align="center" cellpadding="1" cellspacing="1" bgcolor="#CCCCCC">
  <tr> 
    <td bgcolor="#FFFFFF">
<form name="table" method="post" action="">
        <table width="100%">
          <tr> 
            <td width="14%">Team</td>
            <td width="86%">Won//</td>
          </tr>
          <?php while ($databaseArray = mysql_fetch_array($databaseInfo)){ ?>
          <tr> 
            <td><?php echo $databaseArray['team_id']; ?></td>
            <td> 
              <input name="r[team_wins]" type="text" id="r[team_wins]" value="<?php echo $databaseArray['team_wins']?>" size="12" />
            </td>
          </tr>
          <?php } // end of while loop ?>
        </table>
      <br /> <input type="submit" name="Submit" value="Submit" class="button"> 
</form>	  

	  </td>
  </tr>
</table>
</body>
</html>

nemonoman

Are there many unique rows WHERE league='p' ?
If so, your update statement is updating ALL of those rows with every iteration with $row['team_wins'].

I THINK you want to update individual rows, not all rows each interation.

7724

Thanks,

Yes there are. I also tried it with:

team_wins='$team_wins'

But that did not work either. Is that what you meant?

TheoGB

What sort of value is 'team_wins'? Y/N or a number (of goals)?

Anyway, I think your HTML is somewhat incorrect

<?php

// Get the data from the league table
$databaseInfo = mysql_query("SELECT * FROM `league_table` WHERE league='p' ORDER BY team_points_total DESC, team_gf-team_ga DESC") or die(mysql_error()); 

// Update the new values in the database
if(isset($_POST['Submit']))
{

for($i = 0; $i < count($_REQUEST['win']) ; $i++)
{ 
  $result = mysql_query("Update league_table set team_wins='".$_REQUEST['win'][$i]."' WHERE team_id=".$_REQUEST['id'][$i]) OR DIE(mysql_error());
  $msg = "League Table Updated";
}

} //end of post

?>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<title></title>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
</head>

<body>
<?php echo $msg;?> 
<table width="937" border="0" align="center" cellpadding="1" cellspacing="1" bgcolor="#CCCCCC">
  <tr> 
    <td bgcolor="#FFFFFF">
<form name="table" method="post" action="">
        <table width="100%">
          <tr> 
            <td width="14%">Team</td>
            <td width="86%">Won//</td>
          </tr>
          <?php while ($databaseArray = mysql_fetch_array($databaseInfo)){ ?>
          <tr> 
            <td><input type="hidden" name="id[]" value="<?php echo $databaseArray['team_id']; ?>"><?php echo $databaseArray['team_id']; ?></td>
            <td> 
              <input name="win[]" type="text" id="win <?php echo $databaseArray['team_wins']?>" value="<?php echo $databaseArray['team_wins']?>" size="12" />
            </td>
          </tr>
          <?php } // end of while loop ?>
        </table>
      <br /> <input type="submit" name="Submit" value="Submit" class="button"> 
</form>	  

	  </td>
  </tr>
</table>
</body>
</html>

I have no idea if the above will work as you want: Note that you are now updating each individual team based on the ID in the table with the wins shown. If that's not what you want then you don't need to use an array at all: Previously you were just updating all the records in the database that had league 'p' with the value in 'team_wins'.

Unfortunately you had it so that you were updating all of them with the value of the current team in the array and then deleting that and replacing it with the value of the next team. I.e.

Your screen showed:

TEAM --- WINS
 12    |  Yes
 14    |  No
 22    |  No
 26    |  Yes

would have gone through the league and updated all the teams in league 'p' four times. The first time it would have given them all the value of Team_wins from team 12 ('Yes'). The next two times would have ben the values from teams 14 and 22, which would have been No, and finally they'd all have got 'Yes'.

The code I put in above should apply the value for a given id back to the correct team only.

EDIT: You'll note I also changed the ID setting on your text input for 'wins'. Now I would say you should really have a class not an ID (IDs are for one-off fields) but I've appended the team wins value to the end of the it so that you can create a class in your CSS that matches the Team Wins value and allows you to colour that row.

Oh and consider using the to uppercase functionality and trim() before you write the value to the database in case people put in text that looks messy.

7724

That's absolutely awesome. Thank yo so much!