How to do a INSERT with two different loops?

Question

Update

I have updated my code according to phant0m's suggestion. It still doesn't quite work yet, though: question_id is always 0 in the database, even though it's not in the array:

var_dump($_POST['question_id'])
array(2) { [0]=> string(2) "22" [1]=> string(2) "23" } 

The query:

string(122) "INSERT INTO student_score(course_uid, student_uid, question_uid, answer) VALUES
(1, 4, 0, 'answer1'), 
(1, 4, 0, 'answer4')

This is the new code:

$sql_data = array();
$sql_prefix = "INSERT INTO student_score(course_uid, student_uid, question_uid, answer) VALUES";
foreach($_POST['answer'] as $id => $answer){
    // don't use $_REQUEST!
    $course_id = (int) $_POST['course_id'][$i];
    $student_id  = (int) $_POST['student_id'][$i];
    $question_id   = (int) $_POST['question_id'][$i];
    $answer      = mysql_real_escape_string($answer);
    $sql_data[] = "($course_id, $student_id, $question_id, '$answer')";
}
$sql = $sql_prefix.implode(", \n", $sql_data);
var_dump($sql);
if(!mysql_db_query($dbName, $sql, $connect)){
    $_SESSION['msg'] = "Could not save information, Please try again";
    header("Location:student_assignment.php");
    //replaced die with else clause
}
else{
    $_SESSION['msg'] = "Question successfully created";
    header("Location:student_assignment.php");
}

---

Initial question:

I have a problem adding the values of an array into a mysql database. The thing is I have two loops and if I add the INSERT in one of the then the other one gives the wrong value. But if I echo inside each loop it gives the right values.

At the moment it adds two double rows of each value where I only want one row of each value.

Here is my code:

<?php
  require_once("settings.inc.php");

  // require_once("student_session.inc.php");
  session_start();

  for ($d = 0; $d <= count($_POST[answer]); $d++) {
      $answer = $_POST[answer][$d];//I want to insert this value          
      //echo $answer;
      $ids = $_REQUEST['question_id'];

      foreach ($ids as $value) {
          //echo $value; //and this value into the INSERT              
          $sql = "INSERT INTO student_score(answer) VALUES ('$answer')";
          $results = mysql_db_query($dbName, $sql, $connect);
      }
  }

  if (!$results) {
      $_SESSION['msg'] = "Could not save information, Please try again";          
      header("Location:student_assignment.php");          
      die;
  }    

  $_SESSION['msg'] = "Question successfully created";      
  header("Location:student_assignment.php");

  die;
?>

Answer 1

You're using the wrong variable:

"INSERT INTO student_score(answer) VALUES ('$answer')";

You comment that the variable you'd like inserted is called $value, so you meant to write:

"INSERT INTO student_score(answer) VALUES ".
       "('".mysql_real_escape_string($value)."')";

(mysql_real_escape_string is to prevent SQL injection attacks)

Answer 2

Your code does not make much sense.

This might more closely resemble what you want it to do:

// you will not want <=, that will create an index error upon the last 
// iteration, also, you need to quote the key!
// This is fixed:
//for ($d = 0; $d < count($_POST['answer']); $d++) {
// this is a better way
// this assumes, that the indices of the POST array nicely correspond with each 
// other.
$sql_data = array();
$sql_prefix = "INSERT INTO student_score(question_id, student_id, course_id, answer) VALUES";
foreach($_POST['answer'] as $id => $anwer){
    // don't use $_REQUEST!
    $question_id = (int) $_POST['question_id'][$i];
    $student_id  = (int) $_POST['student_id'][$i];
    $course_id   = (int) $_POST['course_id'][$i];
    $answer      = your_escape_function($answer)
    $sql_data[] = "($question_id, $student_id, $course_id, '$answer')";
}
$sql = $sql_prefix.implode(", \n", $sql_data);
if(!mysql_db_query($dbName, $sql, $connect)){
    $_SESSION['msg'] = "Could not save information, Please try again";
    header("Location:student_assignment.php");
    //replaced die with else clause
}
else{
    $_SESSION['msg'] = "Question successfully created";
    header("Location:student_assignment.php");
}

Attention

This code is mostly based on guesswork and assumptions what you want it to do. You need to have a function that properly escapes your code based on whether magic_quotes are enabled. Simply calling mysql_real_escape_string()as suggested in the other answer is incorrect.

Please note that mysql_* functions are outdated. Consider using parameterized queries using PDOs or myqsli.

PS: do not use $_REQUEST.

Answer 3

make use of MySQL transactions: PHP + MySQL transactions examples

Also can you post the output of the following?: print_r($_POST); and print_r($_POST[answer]);

Using $_REQUEST is bad Idea. either use POST or GET explicitly!