如何在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));
    }


}

?>

这段代码在body部分

<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 engine中事务无法工作。

Answer 2

我没有读完整个代码，但是如果你有多条记录，并且你希望更新相同的字段(使用相同的值)，你可以这样实现：

UPDATE mytable SET filed = '$value' WHERE id IN (1,2,3,4,5)

如果你有一个带有if的数组，你可以这样做：

$ids = implode(',',$array_ids);
UPDATE mytable SET field = '$value' WHERE id IN ('$ids')

但是，如果每个id的值不同，只需运行一个循环来更新每一行的值。