current community

your communities

more stack exchange communities

company blog
Stack Exchange Inbox Reputation and Badges
tour help
Stack Overflow is a community of 4.7 million programmers, just like you, helping each other.

Join them; it only takes a minute:

Sign up
Join the Stack Overflow community to:
  1. Ask programming questions
  2. Answer and help your peers
  3. Get recognized for your expertise
up vote 0 down vote favorite

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,

share|improve this question
    
INSERT with WHERE dont mix,your first query shoudnt work – Mihai Oct 7 '14 at 18:39
    
I agree with Mihai, the only thing you can do is try to help the user enter the right data, by validating it somehow ( like an email, phone number) and /or by the way you title the fields, like "first name" "last name" etc.. I wouldn't change my design to account for users entering the wrong data in, because they will always do that at some point. Latter on you will be glad all the fields are separate, as It will be much easier to use that data. – ArtisiticPhoenix Oct 7 '14 at 18:45
    
Really, the database design has to reflect how you plan to use the data, It could be perfectly fine to have both "Tom cruise" and "Brad Pitt" in the same field, that all depends what you want to do with it in the end. As, you can search for them like this Like %tom cruise% and find it just fine even with the other one there. – ArtisiticPhoenix Oct 7 '14 at 18:51
    
@ArtisiticPhoenix: thanks for your great info! actually, I need to know which answers are written by which user (because I need it for my research project which is user-based and it will need later for recommendation purposes.. (my research is on recommender system algorithms) – mOna Oct 7 '14 at 21:00
    
@mihai: you are right, I alrady got the error for that, thanks for mention it :) – mOna Oct 7 '14 at 21:02
up vote 1 down vote

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. :)

share|improve this answer
    
Thanks Aret for your answer, my problem is that I don't know how to define this array.. should I write s.th like this ?? $arr = array(); $sql="select qId from Answer"; $result = mysql_query($sql) or die(mysql_error()); while( $row = mysql_fetch_assoc( $result ) ) { arr[] = $row[ 'qId' ]; } – mOna Oct 7 '14 at 21:25
    
I think it fetch rows of "qId" from table "questions" and insert them in array... if yes, then how can I iterate over this array?? :| – mOna Oct 7 '14 at 21:27
    
You will have array of questions after you create the form from these. Use square brackets on form creation, and the magic happens after pressing the submit. Google the examples like these: davidwalsh.name/checkbox-form-input-arrays – Aret Oct 8 '14 at 4:54
up vote 0 down vote

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

  }

}
share|improve this answer
    
Note that this assumes all the answers are in arrays, even if there's only one answer possible for that question. – Tim Smith Oct 14 '14 at 11:41
    
sorry, but what does this last line execute?! Shouldn't be a line before to update or insert answers into a table?!!! – mOna Oct 14 '14 at 13:02
    
I've added comments to explain that the last line runs the sql from the first line. – Tim Smith Oct 16 '14 at 0:03

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.