当前位置： 文江博客话题详情

Mysql PHP 从结果列表更新表时遇到问题

发布于 2024-11-02 01:34:35 字数 5540 浏览 1 评论 0原文

如果有耐心的人能阅读本文并帮助我，我将不胜感激。我在更新一张 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 语句吗？再次感谢您的帮助——我对此有点陌生。

原文

If anyone with patience can read this and help me, I would be overly grateful. I am having difficulty updating one mysql table. I have a table that stores dj's comments and feedback on record albums,and I have a second table that store the general info about the album. My problem is looping through each feedback row to UPDATE my table. I'm just going to post all of my code and perhaps it will be clearer (sorry for a lengthy post).

<?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>");
  }
}
?>

This is correctly updating album_info, but album_comments needs to be looped through, as seen below:

<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 } ?>

How do amend the MYSQL statement to go through each row in album_comments and update them? Do I need a prepared statement, or can I change the PHP sql statement?? Thanks again for any help—I am somewhat new at this.

如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

扫码二维码加入Web技术交流群

发布评论

需要 登录 才能够评论， 你可以免费 注册 一个本站的账号。

评论（1）

兮颜 2024-11-09 01:34:35

假设您的 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 } ?>

Supposing that your album_comment table has a comment_id primary key (integer, auto increment, like album_info.album_id I guess), you could try the following:

<?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>");
    }
}
?>

Second part:

    <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 } ?>
回复 原文
~没有更多了~

关于作者

吝吻

暂无简介

0 文章
0 评论
24 人气
发私信

相关话题

更多

热门标签

操作系统 程序设计 IT运维 Linux系统管理 JavaScript 服务器应用 solaris C/C++ PHP Shell BSD Vue.js aix Oracle Python HTML 系统管理 HTML5 CSS 前端
更多

推荐作者

離殇

文章 0 评论 0

小姐丶请自重

文章 0 评论 0

Aik

文章 0 评论 0

国产ˉ祖宗

文章 0 评论 0

猥琐帝

文章 0 评论 0

半仙

文章 0 评论 0

更多

友情链接

文江博客
    我们使用 Cookies 和其他技术来定制您的体验包括您的登录状态等。通过阅读我们的 隐私政策 了解更多相关信息。 单击 接受 或继续使用网站，即表示您同意使用 Cookies 和您的相关数据。
    原文