Mysql PHP无法从结果列表中更新表

| 如果任何有耐心的人都能阅读并帮助我，我将不胜感激。我在更新一个mysql表时遇到困难。我有一个表，用于存储dj的评论和对唱片专辑的反馈，还有第二个表，用于存储唱片的一般信息。我的问题是遍历每个反馈行以更新我的表。我将发布所有代码，也许会更清楚（抱歉，冗长的帖子）。 
<?php

$done = false;
$problem = false;

$expected = array(\'album_id\', \'dj\',\'affilliations\',\'rating\',\'comments\'
                  , \'content_id\',\'title\',\'ep\',\'date\',\'genre\');

$conn = dbConnect(\'admin\');

if ($_GET && !$_POST) {
  if (isset($_GET[\'album_id\']) && is_numeric($_GET[\'album_id\'])) {
    $album_id = $_GET[\'album_id\'];
  }
  else {
    $album_id = NULL;
  }
  if ($album_id) {
    //this statement updates album_info correctly, but not album_comments
      $sql = \"SELECT album_info.album_id, album_info.title, album_info.ep
               , album_info.genre, album_info.date, album_comments.content_id
               , album_comments.album_id, album_comments.dj
               , album_comments.affilliations, album_comments.rating
               , album_comments.comments 
             FROM album_info, album_comments 
             WHERE album_info.album_id = $album_id 
               AND album_comments.album_id = $album_id\";

    $result = mysql_query($sql) or die (mysql_error());
    $row = mysql_fetch_assoc($result);
  }
}

// if form has been submitted, update record
if (array_key_exists(\'update\', $_POST)) {
  // prepare expected items for insertion in to database
  foreach ($_POST as $key => $value) {
    if (in_array($key, $expected)) {
      ${$key} = mysql_real_escape_string($value);
    }
  }
  // abandon the process if primary key invalid
  if (!is_numeric($album_id)) {
    die(\'Invalid request\');
  }
  if(!empty($_POST[\'dj\']) && !empty($_POST[\'title\'])) {
    $album_id = mysql_real_escape_string(trim($album_id));
    $dj = mysql_real_escape_string(trim($_POST[\'dj\']));
    $affilliations = mysql_real_escape_string(trim($_POST[\'affilliations\']));
    $rating = mysql_real_escape_string(trim($_POST[\'rating\']));
    $comments = mysql_real_escape_string(trim($_POST[\'comments\']));
    $title = mysql_real_escape_string(trim($_POST[\'title\']));
    $ep = mysql_real_escape_string(trim($_POST[\'ep\']));
    $genre = mysql_real_escape_string(trim($_POST[\'genre\']));
    $date = mysql_real_escape_string(trim($_POST[\'date\']));

  }

  $sql=\"UPDATE album_info, album_comments 
       ON album_info.album_id = album_comments.album_id 
       SET album_info.title = \'$title\', album_info.ep = \'$ep\'
         , album_info.date = \'$date\', album_info.genre = \'$genre\'
         , album_comments.dj = \'$dj\'
         , album_comments.affilliations = \'$affilliations\'
         , album_comments.rating = \'$rating\'
         , album_comments.comments = \'$comments\' 
           album_comments.album_id = \'$album_id\' 
       AND album_info.album_id = \'$album_id\'\";
    // submit the query and redirect if successful
  $done = mysql_query($sql) or die(mysql_error());
  if($done) {
    printf(\"<script>location.href=\'?page=albums\'</script>\");
  }
}
?>
这可以正确更新album_info，但是需要循环遍历album_comments，如下所示： 
<form id=\"album_form\" name=\"album_form\" method=\"post\" action=\"\">
  <fieldset>
    <legend>Album Info</legend>
    <p>
      <label for=\"title\">Title</label>
      <input type=\"text\" name=\"title\" id=\"title\" 
        value=\"<?php echo htmlentities($row[\'title\']); ?>\" />
    </p>
    <p>
      <label for=\"ep\">EP</label>
      <input type=\"text\" name=\"ep\" id=\"ep\" 
        value=\"<?php echo htmlentities($row[\'ep\']); ?>\" />
    </p>
    <p>
     <label for=\"day\">Date:</label>
      <input name=\"day\" type=\"text id=\"day: size=\"2\" maxlength=\"2\" 
        value=\"<?php echo htmlentities($row[\'date\']); ?>\"/>
    </p>
    <p>
      <label for=\"genre\">Genre</label>
      <input type=\"text\" name=\"genre\" id=\"genre\" 
        value=\"<?php echo htmlentities($row[\'genre\']); ?>\"/>
  </fieldset>
  </p>
  <fieldset>
    <legend>Comments</legend>
    <!--data below is from table album_comments -->
<table id=\"tblInsertRowPHP\" class=\"tableResults\" cellpadding=\"0\" 
  cellspacing=\"0\">
      <tbody>
      <?php
      //this spits out all the feedback for the particular album; 
      //this is the part I need help with
      while ($row = mysql_fetch_assoc($result)) { 
      ?>
      <tr>
          <td>
          <?php

        echo \'<input type=\"text\" name=\"dj\" size=\"15\" value=\"\'.$row[\'dj\'].\'\" />\';
        echo \'<input type=\"text\" name=\"affilliations\" size=\"30\"
              value=\"\'.$row[\'affilliations\'].\'\" />\';
        echo \'<input type=\"text\" name=\"rating\" size=\"8\" 
              value=\"\'.$row[\'rating\'].\'\" />\';
        echo \'<input type=\"text\" name=\"comments\" size=\"68\"
              value=\"\'.$row[\'comments\'].\'\" />\';
          ?>
          </td>
        </tr>
        <?php } 
        $sql = \"SELECT album_id FROM album_info\";
        $result = mysql_query($sql) or die (mysql_error());
        $row = mysql_fetch_assoc($result);?>
      </tbody>
    </table>
  </fieldset>
    <input type=\"submit\" name=\"update\" 
     value=\"Update entry\" id=\"submit\" />
  <input name=\"album_id\" type=\"hidden\" 
     value=\"<?php echo $row[\'album_id\']; ?>\" />
</form>
<?php } ?>
如何修改MYSQL语句以遍历album_comments中的每一行并更新它们？我需要准备好的语句，还是可以更改PHP sql语句？再次感谢您的帮助-我对此有些陌生。     
2019-12-09 2 条评论
分享

没有找到相关结果

    已邀请:

    玩翁文醚碱

    假设您的
    album_comment
    表具有一个
    comment_id
    主键（整数，自动递增，我猜是
    album_info.album_id
    ），则可以尝试以下操作： 
    <?php

$done = false;
$problem = false;

$expected = array(\'album_id\', \'dj\',\'affilliations\',\'rating\',\'comments\', \'content_id\',\'title\',\'ep\',\'date\',\'genre\');

$conn = dbConnect(\'admin\');

if ($_GET && !$_POST) {
    if (isset($_GET[\'album_id\']) && is_numeric($_GET[\'album_id\'])) {
        $album_id = $_GET[\'album_id\'];
    }
    else {
        $album_id = NULL;
    }
    if ($album_id) {
    //this statement updates album_info correctly, but not album_comments
        $sql = \"SELECT album_info.album_id, album_info.title, album_info.ep, album_info.genre, album_info.date, album_comments.content_id, album_comments.album_id, album_comments.dj, album_comments.affilliations, album_comments.rating, album_comments.comments FROM album_info, album_comments WHERE album_info.album_id = $album_id AND album_comments.album_id = $album_id\";

        $result = mysql_query($sql) or die (mysql_error());
        $row = mysql_fetch_assoc($result);
    }
}

// if form has been submitted, update record
if (array_key_exists(\'update\', $_POST)) {
    // prepare expected items for insertion in to database
    foreach ($_POST as $key => $value) {
        if (in_array($key, $expected)) {
            ${$key} = mysql_real_escape_string($value);
        }
    }
    // abandon the process if primary key invalid
    if (!is_numeric($album_id)) {
        die(\'Invalid request\');
    }
    if(!empty($_POST[\'dj\']) && !empty($_POST[\'title\'])) {
        $album_id = mysql_real_escape_string(trim($album_id));
        $title = mysql_real_escape_string(trim($_POST[\'title\']));
        $ep = mysql_real_escape_string(trim($_POST[\'ep\']));
        $genre = mysql_real_escape_string(trim($_POST[\'genre\']));
        $date = mysql_real_escape_string(trim($_POST[\'date\']));

    }

    $sql=\"UPDATE album_info SET title = \'$title\', ep = \'$ep\', date = \'$date\', genre = \'$genre\' WHERE album_id = \'$album_id\'\";

    $done = mysql_query($sql) or die(mysql_error());

    foreach($_POST[\'comment_id\'] as $index => $comment_id)
    {
        $comment_id = intval($comment_id);
        $dj = mysql_real_escape_string(trim($_POST[\'dj\'][$index]));
        $affilliations = mysql_real_escape_string(trim($_POST[\'affilliations\'][$index]));
        $rating = mysql_real_escape_string(trim($_POST[\'rating\'][$index]));
        $comments = mysql_real_escape_string(trim($_POST[\'comments\'][$index]));

        $sql=\"UPDATE album_comments SET dj = \'$dj\', affilliations = \'$affilliations\', rating = \'$rating\', comments = \'$comments\' WHERE comment_id = \'$comment_id\'\";

        $done = $done && mysql_query($sql) or die(mysql_error());
    }

    // submit the query and redirect if successful
    if($done) {
        printf(\"<script>location.href=\'?page=albums\'</script>\");
    }
}
?>
    第二部分： 
        <form id=\"album_form\" name=\"album_form\" method=\"post\" action=\"\">
  <fieldset>
    <legend>Album Info</legend>
    <p>
      <label for=\"title\">Title</label>
      <input type=\"text\" name=\"title\" id=\"title\" value=\"<?php echo htmlentities($row[\'title\']); ?>\" />
    </p>
    <p>
      <label for=\"ep\">EP</label>
      <input type=\"text\" name=\"ep\" id=\"ep\" value=\"<?php echo htmlentities($row[\'ep\']); ?>\" />
    </p>
    <p>
     <label for=\"day\">Date:</label>
      <input name=\"day\" type=\"text id=\"day: size=\"2\" maxlength=\"2\" value=\"<?php echo htmlentities($row[\'date\']); ?>\"/>
    </p>
    <p>
      <label for=\"genre\">Genre</label>
      <input type=\"text\" name=\"genre\" id=\"genre\" value=\"<?php echo htmlentities($row[\'genre\']); ?>\"/>
  </fieldset>
  </p>
  <fieldset>
    <legend>Comments</legend>
    <!--data below is from table album_comments -->
<table id=\"tblInsertRowPHP\" class=\"tableResults\" cellpadding=\"0\" cellspacing=\"0\">
      <tbody>
      <?php
      //this spits out all the feedback for the particular album; this is the part I need help with
        while ($row = mysql_fetch_assoc($result)) { 
      ?>
      <tr>
          <td>
          <?php
        echo \'<input type=\"hidden\" name=\"comment_id[]\" value=\"\'.$row[\'comment_id\'].\'\" />\';
        echo \'<input type=\"text\" name=\"dj[]\" size=\"15\" value=\"\'.$row[\'dj\'].\'\" />\';
        echo \'<input type=\"text\" name=\"affilliations[]\" size=\"30\" value=\"\'.$row[\'affilliations\'].\'\" />\';
        echo \'<input type=\"text\" name=\"rating[]\" size=\"8\" value=\"\'.$row[\'rating\'].\'\" />\';
        echo \'<input type=\"text\" name=\"comments[]\" size=\"68\" value=\"\'.$row[\'comments\'].\'\" />\';
          ?>
          </td>
        </tr>
        <?php } 
        $sql = \"SELECT album_id FROM album_info\";
        $result = mysql_query($sql) or die (mysql_error());
        $row = mysql_fetch_assoc($result);?>
      </tbody>
    </table>
  </fieldset>
    <input type=\"submit\" name=\"update\" value=\"Update entry\" id=\"submit\" />
  <input name=\"album_id\" type=\"hidden\" value=\"<?php echo $row[\'album_id\']; ?>\" />
</form>
<?php } ?>
        
    2019-12-09 0 0
    分享
    为什么被折叠? 0 个回复被折叠

    要回复问题请先登录或注册

    或代码 OrCode.com 备案号：粤ICP备15020848号-1
    Escape time: 0.042688846588135