CODEWITHSUNDEEP
phpmysqlformspdo
mOna
mOna

Reputation: 2459

php insert multiple values into separate rows in mysql table

I have a PHP form with various types of input fields (checkbox, drop-down, radio, auto-complete, etc.) What I would like to do is get users input(which might be more than one value, for instance, for question: which is your favourite movie? he might write more than 1 movie) and store these values in separate rows in Mysql table. here are tables in my database:

Answer: Id, userId, questionId, answer

member: userId, name, family name etc.

qustion: questionId, questionText

What I can do is something like this:(Note: in this case the design would be different, here I have a separate field for each question)

$stmt = $conn->prepare('UPDATE test SET q1 = :q1, q2 = :q2, q3 = :q3, q4 = :q4, q5 = :q5, q6     = :q6, q7 = :q7 WHERE username = :username');
$stmt->execute(array(':q1' => $q1,':q2' => $q2, ':q3' => $q3, ':q4' => $q4, ':q5' => $q5, ':q    6' => $q6, ':q7' => $q7, ':username' => $_SESSION['SESS_USERNAME']));

My problem with this approach was that multiple value answers were inserted in the same row with a comma separated them(for instance if user wrote "Tom cruise" and "Brad Pitt", it stored both these names in the same row)

I thought I have to replace it with something like this:

$stmt = $conn->prepare('INSERT INTO Answer (qId, answer) VALUES (:qId, :answer)');

but here I don't know how to define qId? (how it can recognize that each answer is related to which question)

EDIT:

In the code below, I can fetch questions form database and store it in array, but still Iam not sure how to complete this code.. should I write insert statement inside the loop (the loop which get questions from DB, so for each question, it insert qId and the answer user has inserted... am I right ??)

$arr = array(); 
$sql="select qId from question"; 
$result = mysql_query($sql) or die(mysql_error());
while( $row = mysql_fetch_assoc( $result ) ) {
   arr[] = $row[ 'qId' ]; 
}

Could someone kindly help me to know how I can insert these values into multiple rows?

Many thanks,

Upvotes: 0

Views: 1283

Answers (2)

Tim Smith
Tim Smith

Reputation: 1846

If you've already got the answers stored in $_POST by qId, and you're using [] in your input names, as Aret suggests, you could do something like:

// creates an object, $stmt, which will do an insert whenever $stmt->execute()
// is called
$stmt = $conn->prepare('INSERT INTO Answer (qId, answer) VALUES (:qId, :answer)');

$sql="select qId from Answer"; 
$result = mysql_query($sql) or die(mysql_error());

// loop over all question ids
while( $row = mysql_fetch_assoc( $result ) ) {

  $qId = $row['qId'];

  // loop over all the answers for one question
  foreach ($_POST[$qId] as $answer) {

    // this runs the sql from the first line with :qId set to $qId, 
    // and :answer set to $answer
    $stmt->execute(array(':qId' => $qId, ':answer' => $answer));

  }

}

Upvotes: 0

Aret
Aret

Reputation: 475

The id for the question can be array at the backend side. You can iterate over the array and insert questions in loop.

The other way is to compose the variable names in for loop by using variable variables.

Array seems like a cleaner code to me. :)

Upvotes: 1

Related Questions