CODEWITHSUNDEEP
phpsqldatabase
Cas
Cas

Reputation: 55

Check to update and then insert multiple records at once in mysql?

I have 2 tables relating to a survey. When user answers each set of questions and then click the submit button, it will loop each answer according to the form submitted in order to check within the database first, if the CustomerID and QuestionID have been found, then do the Update. If not found, do the Insert instead.

QUESTIONS table

ANSWERS table

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

My questions are:

  1. How to update each answer(1-6) one by one and then insert into the database if CustomerID and QuestionID have not been found by using array and SQL query, if found, then just update?
  2. How could I reference the QuestionID in order to related with AnswerText in HTML and PHP?

Upvotes: 1

Views: 474

Answers (3)

Prasad Rajapaksha
Prasad Rajapaksha

Reputation: 6190

This is just an idea for you. Hope you can understand it. Make sure you replace database driven functions with your $db object.

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 ++;
    }
}

Upvotes: 1

Brent Baisley
Brent Baisley

Reputation: 12721

If you set a unique key on CustomerID+QuestionID, then you can just do an INSERT ... ON DUPLICATE KEY UPDATE ...

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

Let the database handle the checks.

Upvotes: 0

Aman Virk
Aman Virk

Reputation: 3977

You can run a select query first and then see how many rows have been returned from there something like this

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

Upvotes: 1

Related Questions