php - 如何更新MySQL中的多条记录？

Question

我正在尝试从表中提取记录并更新其中的一个记录。我能够提取记录并创建表单，但是更新部分不起作用。

下面的代码在我的 HTML 部分之上。

<?php require_once('../Connections/connect.php'); ?>
<?php
session_start();
$MM_authorizedUsers = "";
$MM_donotCheckaccess = "true";

// *** Restrict Access To Page: Grant or deny access to this page
function isAuthorized($strUsers, $strGroups, $UserName, $UserGroup) { 
  // For security, start by assuming the visitor is NOT authorized. 
  $isValid = False; 

  // When a visitor has logged into this site, the Session variable MM_Username set equal to their username. 
  // Therefore, we know that a user is NOT logged in if that Session variable is blank. 
  if (!empty($UserName)) { 
    // Besides being logged in, you may restrict access to only certain users based on an ID established when they log in. 
    // Parse the strings into arrays. 
    $arrUsers = Explode(",", $strUsers); 
    $arrGroups = Explode(",", $strGroups); 
    if (in_array($UserName, $arrUsers)) { 
      $isValid = true; 
    } 
    // Or, you may restrict access to only certain users based on their username. 
    if (in_array($UserGroup, $arrGroups)) { 
      $isValid = true; 
    } 
    if (($strUsers == "") && true) { 
      $isValid = true; 
    } 
  } 
  return $isValid; 
}

$MM_restrictGoTo = "sorry.php";
if (!((isset($HTTP_SESSION_VARS['MM_Username'])) && (isAuthorized("",$MM_authorizedUsers, $HTTP_SESSION_VARS['MM_Username'], $HTTP_SESSION_VARS['MM_UserGroup'])))) {   
  $MM_qsChar = "?";
  $MM_referrer = $HTTP_SERVER_VARS['PHP_SELF'];
  if (strpos($MM_restrictGoTo, "?")) $MM_qsChar = "&";
  if (isset($QUERY_STRING) && strlen($QUERY_STRING) > 0) 
  $MM_referrer .= "?" . $QUERY_STRING;
  $MM_restrictGoTo = $MM_restrictGoTo. $MM_qsChar . "accesscheck=" . urlencode($MM_referrer);
  header("Location: ". $MM_restrictGoTo); 
  exit;
}
?>
<?php
$col_points = "0";
if (isset($HTTP_GET_VARS['tournament_id_num'])) {
  $col_points = (get_magic_quotes_gpc()) ? $HTTP_GET_VARS['tournament_id_num'] : addslashes($HTTP_GET_VARS['tournament_id_num']);
}
mysql_select_db($database_camsports, $camsports);
$query_points = sprintf("SELECT cam_registered_tbl.team_id_num, cam_registered_tbl.wins, cam_registered_tbl.losses, cam_registered_tbl.points, cam_teams_tbl.team_name, cam_registered_tbl.registered_id_num FROM cam_registered_tbl, cam_teams_tbl WHERE cam_registered_tbl.tournament_id_num=%s AND cam_teams_tbl.team_id_num=cam_registered_tbl.team_id_num", $col_points);
$points = mysql_query($query_points, $camsports) or die(mysql_error());
$row_points = mysql_fetch_assoc($points);
$totalRows_points = mysql_num_rows($points);

$col_tournament = "0";
if (isset($HTTP_GET_VARS['tournament_id_num'])) {
  $col_tournament = (get_magic_quotes_gpc()) ? $HTTP_GET_VARS['tournament_id_num'] : addslashes($HTTP_GET_VARS['tournament_id_num']);
}
mysql_select_db($database_camsports, $camsports);
$query_tournament = sprintf("SELECT cam_tournaments_tbl.tournament_name FROM cam_tournaments_tbl WHERE cam_tournaments_tbl.tournament_id_num=%s", $col_tournament);
$tournament = mysql_query($query_tournament, $camsports) or die(mysql_error());
$row_tournament = mysql_fetch_assoc($tournament);
$totalRows_tournament = mysql_num_rows($tournament);
?>
<?php

//This loops through all the records that have been displayed on the page.

for ($index = 0; $index <= $index_count; $index++) {


    /*
    This part sets a variable with the names we created in the first section.  
    We start with 0 and go until the number saved in the $index_count variable.
    */ 

    $varregistered_id_num = 'registered_id_num'.$index;
    $varteam_name = 'team_name'.$index;
    $varwins = 'wins'.$index;
    $varlosses = 'losses'.$index;
    $varpoints = 'points'.$index;


    /*
    This is the variable variable section.  We take the value that was assigned 
    to each name variable.  For example the first time through the loop we are 
    at the record assigned with SubmissionID0.  The value given to SubmissionID0 
    is set from the first section.  We access this value by taking the variable 
    variable of what SubmissionID0 is.
    */

    $registered_id_numvalue = $$varregistered_id_num;
    $team_namevalue = $$varteam_name;
    $winsvalue = $$varwins;
    $lossesvalue = $$varlosses;
    $pointsvalue = $$varpoints;


    //Update the database

    $sql = "UPDATE cam_registered_tbl SET points='$pointsvalue',wins='$winsvalue',".
        "losses='$lossesvalue' WHERE registered_id_num='$registered_id_numvalue'";
    $result = mysql_query($sql);

    //If the link was marked approved set the value of the Approved field

    if ($goto == '1') {
        $insertGoTo = "menu.php";
        header(sprintf("Location: %s", $insertGoTo));
    }


}

?>

这段代码在正文部分

<div align="center">
  <p><font size="4"><?php echo $row_tournament['tournament_name']; ?></font></p>
  </div>
<?php
//Initialize counter variables

$index = 0;
$index_count = 0;
echo "<form method=post action=$PHP_SELF>\n";
echo "<table>\n";
echo "<tr><td><b>Team</b></td>".
    "<td><b>Points</b></td></tr>\n";


/*
Assuming we already have retrieved the records from the database into an array setting 
$myrow = mysql_fetch_array().  The do...while loop assigns a value to the $xstr variable 
by taking the name and concatenating the value of $index to the end starting with 0.  So 
the first time through the loop $SubmissionIDStr would have a value of SubmissionID0 the 
next time through it would be SubmissionID1 and so forth.
*/

do {

$registered_id_numStr = registered_id_num.$index;
$team_nameStr = team_name.$index;
    $pointsStr = points.$index;


//This section would print the values onto the screen one record per row

printf("<tr><td><input type=hidden name=%s value=%s>%s</td>
<td><input type=text name=%s value=%s size='5'></td></tr>\n", 
$registered_id_numStr, $row_points["registered_id_num"], $row_points["team_name"], $pointsStr, $row_points["points"]);


//Increase counter values by 1 for each loop

$index++;
$index_count++;

} while ($row_points = mysql_fetch_array($points));

// I also had to create an index count to keep track of the total number of rows.

echo "<INPUT TYPE=hidden NAME=counter VALUE=$index_count>\n";
echo "<INPUT TYPE=hidden NAME=goto VALUE='1'>\n";

echo "<INPUT TYPE=submit></form>\n";
 echo "</table>";

?>

如有任何帮助，我们将不胜感激。

Answer 1

你做得对 - 我不知道有什么比在 for 循环中运行 update 更好的方法来处理你的情况。您应该做的是将其包含在事务中:

mysql_query("start transaction");
for ($index = 0; $index <= $index_count; $index++) {
     ...
     $sql = "UPDATE cam_registered_tbl SET points='$pointsvalue',wins='$winsvalue',"."losses='$lossesvalue' WHERE registered_id_num='$registered_id_numvalue'";
     $result = mysql_query($sql);
     if (!$result) { // you possibly should do some error checking
          mysql_query("rollback"); // cancel the transaction
          //print error
          exit(0); 
     }
     ...
}
mysql_query("commit"); // commit the transaction

如果您不使用该事务，您最终可能只会更新一些记录，这将使数据库处于不一致状态。交易在这里非常重要——有了它，所有的记录都会更新，或者没有。

确保您使用的是 InnoDB 引擎，在 MyISAM 引擎中事务不起作用。