选中进行更新，然后在mysql中一次插入多条记录 - Check to update and then insert multiple records at once in mysql?

我有两个与调查相关的表格。当用户回答每组问题，然后单击提交按钮时，它将根据提交的表格循环每个答案，以便首先在数据库中进行检查，如果找到了CustomerID和QuestionID，则执行更新。如果找不到，请执行"插入"操作。

问题表

问题ID（PK）
问题文本

答案表格

AnswerID（PK）
客户ID（FK）
问题ID（FK）

应答文本

<html>
....
<form action="/db.php" method="POST">
 <?php echo $questiontext[1]; ?><input type="text" name="answerswer1" id="answerswer1">
 <?php echo $questiontext[2]; ?><input type="text" name="answerswer2" id="answerswer2">
 <?php echo $questiontext[3]; ?><input type="text" name="answerswer3" id="answerswer3">
 <?php echo $questiontext[4]; ?><input type="text" name="answerswer4" id="answerswer4">
 <?php echo $questiontext[5]; ?><input type="text" name="answerswer5" id="answerswer5">
 <?php echo $questiontext[6]; ?><input type="text" name="answerswer6" id="answerswer6">
 <input type="submit" name="submit" id="submit" value="Submit">
</form>
...
</html>

db.php

    <?php
    if(isset($_POST['submit'])) {
      $cusid = intval($_POST['CustomerID']);
      $answer1 = $db->real_escape_string($_POST['answer1']);
      $answer2 = $db->real_escape_string($_POST['answer2']);
      $answer3 = $db->real_escape_string($_POST['answer3']);
      $answer4 = $db->real_escape_string($_POST['answer4']);
      $answer5 = $db->real_escape_string($_POST['answer5']);
      $answer6 = $db->real_escape_string($_POST['answer6']);
      $sql = "INSERT INTO Answers (CustomerID, QuestionID, AnswerText) VALUES     
      ('".$cusid."','".$quesid."','".$answer."')";
      $res = $db->query($sql) or die ('Error: ' . mysqli_error($db));
    }
    ?>

我的问题是：

如果使用数组和SQL查询未找到CustomerID和QuestionID，如何逐一更新每个答案（1-6），然后插入数据库，如果找到，则只更新
我如何引用QuestionID以便与HTML和PHP中的AnswerText相关

这只是你的一个想法。希望你能理解。确保用$db对象替换数据库驱动的函数。

if(isset($_POST['submit'])) {
    $sql = "SELECT QuestionID FROM Questions ORDER BY QuestionID ";
    $res = $db->query($sql);
    $qus = 1;
    while ($row = mysqli_fetch_array($res , MYSQLI_ASSOC)) {
    {
        $questionID = $row['QuestionID'] ;
        $answer = $db->real_escape_string($_POST['answer' . $qus ]);

        $sql = "SELECT AnswerID  FROM Answers WHERE CustomerID='$cusid' AND QuestionID='$questionID' ";
        $resAns = $db->query($sql);
        $num_rows = $resAns->num_rows; // This should be replace with your $db object record count obtaining method
        if($num_rows == 1)
        {
            $sql = "UPDATE Answers SET AnswerText = '$answer' WHERE CustomerID='$cusid' AND QuestionID='$questionID'  ";
            // Execute your update query
        }
        else
        {
            $sql = "INSERT INTO Answers (CustomerID, QuestionID, AnswerText) VALUES     
  ('".$cusid."','".$questionID."','".$answer."')";
            // Execute your insert statement
        }
        $qus ++;
    }
}

您可以先运行一个选择查询，然后查看从那里返回了多少行，类似于

$check = mysql_query("SELECT * FROM Answers WHERE CustomerId = '$cusid' OR QuestionId =                '$quesid' LIMIT 1") or die(mysql_error());
  $num_rows = mysql_num_rows($check);
   if($num_rows == 1)
     {
          // value exists run the update
     }
     else
     { 
           // go ahead with insert query
     }

如果您在CustomerID+QuestionID上设置了一个唯一密钥，那么您只需执行INSERT。。。正在进行重复密钥更新。。。

http://dev.mysql.com/doc/refman/5.1/en/insert-on-duplicate.html

让数据库处理检查。