How do I insert survey results into the database?

[sonnieboy]

Greetings gurus,

Sorry if this question is too basic. I am still a php newbie.

 

I am using the following code to generate survey results and their various choices.

 

All a user does is check all applicable options and submit their choices to our table called surveyAnswers.

 

The problem I am having is how to generate the INSERT statement from the following code.

 

Thanks in advance for your help:

$tsql = "select q.qID, q.qText, c.choices, c.ChoiceText from tblQuestions q 
inner join tblChoices c ON q.qID = c.qID 
order by q.qID ASC, c.choices DESC "; 
$result = sqlsrv_query( $con, $tsql); 

   while($row = sqlsrv_fetch_array($result, SQLSRV_FETCH_ASSOC)) 
   { 
     if ($row['qID'] != $lastQuestionID) { 
       echo "<p style='font-weight:bold;color:#000000;'>" . $row['qText'] . "</p><br />"; 
       $lastQuestionID = $row['qID']; 
     } 
   echo "<div style='background:#ACD1E9;'><input type='radio' name='".$row['qID']."'  value='".$row['choices']."' />".$row['ChoiceText']; ?> 
    </div> 
            <br> 
            <p style="font-weight:bold;color:steelBLUE;"><label>Additional Comments/Suggestion:</label></p> 
            <p><textarea name="comments" rows="2" cols="50"></textarea></p> 

           <input type="submit" class="button"  name="submit" value="Submit response >>" onClick="return validate(this.form);">  

[Barand]

This has to fairly general as we have no idea about the table you want to inrt into. First, your radio button names need to be something like

"answer[$row['qID']]"

Then when you process the form

foreach ($_POST['answer'] as $id => $choice) {
    // insert id and choice into your answer table
}

[sonnieboy]

CREATE TABLE [dbo].[CustomerFeedback](
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[WORKORDERID] [nvarchar](60) NULL,
	[CustomerName] [nvarchar](50) NULL,
	[Org] [nvarchar](100) NULL,
	[Division] [nvarchar](50) NULL,
	[surveydate] [datetime] NULL,
	[qID] [int] NOT NULL,
	[ChoiceID] [int] NOT NULL,
	[comments] [nvarchar](255) NULL,
	[yourIPAddress] [nvarchar](50) NULL
) ON [PRIMARY]

GO

Thank you very Barand for your kind response.

 

Here is the table information above.

 

So, the table stores information about the customer taking the survey, the department, the division, the survey date, question ID (qID), choice ID, comments and IP address to prevent the user from taking the survey more than once.

 

About the answer[$row['qID']], does that replace  name='".$row['qID']."' on the radio button box?

 

I am a bit confused about that.

[Barand]

That's a lot of information that you are duplicating for every answer. Split off the answer data into a separate table

       +-------------------+                                                                                              
       | customer_feedback |                                                                                              
       +-------------------+                                                                                              
       | ID (PK)           |---------+                                                                                              
       | WorkOrderID       |         |                                                                                     
       | CustomerName      |         |     +------------------+                                                           
       | Org               |         |     | feedback_answers |                                                           
       | Division          |         |     +------------------+                                                           
       | surveydate        |         +----<| feedback_id(PK)  |                                                           
       | IPAddress         |               | qID        (PK)  |                                                           
       +-------------------+               | ChoiceID         |                                                           
                                           | comments         |                                                           
                                           +------------------+     

and

name = "answer[$row['qID']]"

[sonnieboy]

So, as I understand it, we first insert those records into customer_feedback table.

 

Then using the ID from that table, insert the survey records into feedback_answers?

 

Well, that makes a lot of sense but what is the relationship between the two tables?

 

It isn't obvious to me.

 

It is also not clear to me how you insert values for ChoiceID and comments.

 

I can understand how to pass the comments variable to the insert statement but not sure about ChoiceID.

 

Example,

 

name="answer[$row['qID']];

comments = $_POST["comments'];

ChoiceID = ??

 

Thanks very much for your generous assistance.

[sonnieboy]

Too bad this forum doesn't edits unless I am missing it.

 

Here is a fiddle of what I am trying to do.

 

I used your answer[$row['qID']] to generate the markup for the survey questions and choices.

 

But I need to be able to capture the customer name, location, department, date of survey, the user's choices to the six questions and the comments.

 

Thanks again.

[Barand]

The comments fields would also require an array index, like the answer e.g.

<textarea name="comment[$row['qID']]"></textarea>

So

foreach ($_POST['answer'] as $qid => $choiceid) {
     $comment = $_POST['comment'][$id];     // get the associated comment

     // insert feedbackid, qid, choiceid and comment into feedback_answer
}

You are saving customer name etc in the customer_feedback record. The related answers have the same feedback id. (see diagram ^^)

[sonnieboy]

You are awesome!

 

Thanks a lot but I am still not clear about a couple of things.

 

1, If the relationship keys between Customer_Feedback (ID) and Feedback_Answers (feedbackId) are those, why are they both PK to their respective tables?

 

Shouldn't it be ID PK for Customer_Feedback and feedBackID FK to Customer_Feedback?

 

Second, this is really important.

 

Following your example:

foreach ($_POST['answer'] as $qid => $choiceid) {
     $comment = $_POST['comment'][$id];     // get the associated comment

     // insert feedbackid, qid, choiceid and comment into feedback_answer
}

//my version:

Insert into feedback_answers (feedbackid, qid, choiceid, Comment)
Values(Value from ID (CUstomer_Feedback), where is qID value, where is choidID value?, $comment)

Can you please help fill the variables for qID and choiceid?

 

I can get the value of ID from Customer_feedback table and feed it to feedbackid but I need help with qID and choiceid and again I use $comment to feed value to comment field.

 

Many thanks for your continued patience.

[Barand]

 

Can you please help fill the variables for qID and choiceid?

 

You get them from the data posted from the form

 

foreach ($_POST['answer'] as $qid => $choiceid) {

[sonnieboy]

Awesome!

 

Thanks so much for your help and time.

[sonnieboy]

Sorry; I know I have closed this but still one little problem that won't go away.

 

The comments is not writing to the database.

 

Any ideas what I am doing wrong?

//Mark up
$tsql = "select q.qID, q.qText, c.choiceID, c.ChoiceText from tblQuestions q
inner join tblChoices c ON q.qID = c.qID
order by q.qID ASC, c.choices DESC ";
$result = sqlsrv_query( $con, $tsql);

   while($row = sqlsrv_fetch_array($result, SQLSRV_FETCH_ASSOC))
   {
     if ($row['qID'] != $lastQuestionID) {
       echo "<p style='font-weight:bold;color:#000000;'>" . $row['qText'] . "</p><br />";
       $lastQuestionID = $row['qID'];
     }
     echo "<div style='background:#ACD1E9;'><input type='radio' name='answer[".$row['qID']."]' value='".$row['choiceID']."' />".$row['ChoiceText']; ?>
    </div>
   <?php
   }
 ?>
 <br>
  <p style="font-weight:bold;color:steelBLUE;"><label>Additional Comments/Suggestion:</label></p>
<?php echo "<p><textarea name='comments[".$row['qID']."]' rows='2' cols='50'></textarea></p>"; ?>

//Processing page

     foreach ($_POST['answer'] as $qid => $choiceid) {
     $comments = $_POST['comments'][$id];     // get the associated comment

     // insert responses into feeback_answers
     $strSQL1 = "INSERT INTO Feedback_Answers(qID, choiceID, comments)
     VALUES ('". ms_escape_string($lastId)."','". ms_escape_string($choiceid)."','". ms_escape_string($comments)."')";
		$results = sqlsrv_query($con, $strSQL1);
       }

Everything else is working except the comments box.

[Barand]

Your first post seems to have the comments inside the while loop (comment for each question). Your last one doesn't. Which is it?

 

If there is only one comment, Then just have "name='comments'" and storeit in the customer_feedback table, not the answer table. You will get the value from $_POST['comments']

[sonnieboy]

Man, why didn't I think about that?

 

I decided to have three tables.

 

Customer_feedback,

feedback_answers

Comments

 

All responses go to feedback_answers table and the associated comments go to Comments table.

 

Great mind, great work Barand!

Everything is working great now.

 

Many thanks to you indeed.

 

Much appreciated.