how do I submit many items into the database with out a huge insert

[Joe_10]

currently I have a survey with 10 questions on a html/ php form. I use post data and then have a long insert statment. My code looks something like this.

<php?
$surveyid = $_POST['surveyid'];
$q1 = $_POST['q1'];
$q1comments = $_POST['q1comments'];
$q2 = $_POST['q2'];
$q2comments = $_POST['q2comments'];
$q3 = $_POST['q3'];
$q3comments = $_POST['q3comments'];

$query "insert into survey(surveyid,q1,q1comments,q2,q2comments,q3,q3comments)
$parms1 = array($surveyid,$q1,$q1comments,$q2,$q2comments ..
$results = sqlsrv_query($conn,$query,$parms1);

I'm trying to use a for loop but I can't quite get it right;

foreach (question as $question )
    {
    $query = "INSERT INTO safteyudit(auditid,questions,comment,issue) VALUES('$auditid','$question','$comments') ";   
    $parms1 = array($auditid,$question,$comments,$issue);
    $query = sqlsrv_query($conn,$query,$parms1);
    }

[Atli]

The table structure of the survey table you are using in the first snippet must be very odd for something like that to work.

The second snippet looks more reasonable, but there are a lot of errors. For one thing, the VALUES list is missing one value.

The big question here is your table structures. You need to get those right before you start writing PHP code to insert into them. Can you show us exactly what your table struture(s) look like?

[Joe_10]

The code is just to show the format its not the real code. It works but its big and bulky. My current tables are like this

survey
surveyid|q1|q1comments|q2|q2comments|q3|q3comments|q4|q4|comments|q5|q5comments|q6|q6comments

I woud like to have something simplier like

Surveyid|question|answer|comments

but I am unsure how to do it.

[ryantroop]

If you have an up to date version of mySQL you can have PHP write something like this:

insert tableName (a, b) values (c,d), (e,f), (g,h) .... (y,z);

http://dev.mysql.com/doc/refman/5.5/en/insert.html

Alternatively, you can have PHP write a batch for you, and submit a batch (but that's less efficient than using the above method). Even using a stored proc would be somewhat wasteful, as you will have the same efficiency issues, unless you have PHP make some sort of delimiter for you, pass the delimited string to sql and parse it in the stored proc, which would ultimately come down to a series of inserts (or dynamically created SQL, which would be faster in PHP :-/ )

I certainly hope you are sanitizing your data before sending to the database, as well.. all that good stuff.. good luck!
ryan

[Joe_10]

its ms ql server but thank you I will take a look at those.

[ryantroop]

SQL server 2008+ has the same syntax, and same solutions otherwise. Batch it, or make a stored proc that takes a delimited string and makes the batch for you.

[Atli]

On a slight (yet relevant) side-note...

I woud like to have something simplier like
Surveyid|question|answer|comments

What exactly is in the "comments" filed? A plural field name is alwasy a red flag for me. Makes it sound like you're planing to serialize a bunch of comments in there, which is a major design issue.

A simple design for a survey, with questions, each with a list of comments, would be:

Then, the insert commands are very simple:

INSERT INTO survey (name) VALUES('My Survey');

INSERT INTO question (survey_id, question, answer)
VALUES
    (1, 'Question 1', 'Answer 1'),
    (1, 'Question 2', 'Answer 2'),
    (1, 'Question 3', 'Answer 3');

INSERT INTO comment (question_id, comment)
VALUES
    (1, 'Comment for Question 1'),
    (1, 'Another comment for Question 1'),
    (3, 'Comment for Question 3'),

[Joe_10]

In this case it is used for comments about the question ex:
Question 1 is there water on the floor? Yes.
Comment: Broken pipe

[ryantroop]

@Atli,

Your table design doesnt handle multiple answers per question well :-/

However, your point is valid and should probably be considered :)

[Joe_10]

Am I better off having each question on its on page rather then all one one?

[Atli]

@ryantroop

No, it wasn't meant to handle the actual answers. Only meant it to list the questions and their correct answers. The actual answers would have to go into another table, which would usually belong to a larger structure (with users and such.) - Guess I was thinking more test than survey.

Should have made that clearer :)

[Joe_10]

I guess a checklist would be the best way to describe the form. with yes .
If I break it up into tables like @Alti said I think it would be easier.

Thanks all for your help

[Joe_10]

would this be the correct syntax
$query = "INSERT INTO SURVEY(surveyid,q1,q2,q3) VALUES(?,?,?,?), VALUES(?,?,?,?), VALUES(?,?,?,?)";

[ryantroop]

No, correct syntax is
$query = "INSERT INTO SURVEY (surveyid,q1,q2,q3) VALUES (?,?,?,?), (?,?,?,), (?,?,?,)";