插入始终不同的变量的语句

Question

I have a page that is putting random test questions on the page. The database has a bank of over 200 questions. The questions are grabbed randomly for a 20 question test. Upon submit, I need to insert a record for each question with the question number, user ID and answer provided. I have this working fine previously but as the question bank has grown and the need to change up the test grows, I spend far too much time changing hard coded variables and insert statements on the script that processes the test and inserts the results to the database.

$fname=$_POST['EmployeeFirstM'];
$lname=$_POST['EmployeeLast'];
$ruser=$_POST['User'];
$1=$_POST['q1'];
$2=$_POST['q2'];
$3=$_POST['q3'];

With the variables on up to 200+. What comes from the previous page could be any mix of 20 questions. I need to do:

$sql="INSERT INTO $tbl_name(empID, empf, empl, QuestionNumber,  AnswerGiven)VALUES('$ruser','$fname', '$lname','1', '$1')";

20 times with whatever mix of questions come across. Am I going to have to hard code in 200+ insert statements for every question possible and just have it skip over the insert statements that aren't in the mix for each submission? The prior version of the test recorded to one line item but I had to keep adding columns to the table to accept more questions. I don't think that's efficient. Please and thanks.

After much trial and error this works for recording the question IDs into the database tables. I still can't figure out how to get the corresponding selected radio button input into the mix to record the answer.

foreach( $_POST as $q_id ) {
if( is_array( $q_id ) ) {
    foreach( $q_id as $qid ){
   $sql="INSERT INTO $tbl_name(empID, empf, empl, QuestionNumber,  AnswerGiven)VALUES('$ruser','$fname', '$lname','$qid', '$q')";
$result=mysql_query($sql);
}
}
}

Answer 1

Put your values in an array, then just loop through it so you can insert them in a loop.

$answerGiven[];// have all your answers here
$questionNumber[]; // have all your questions in here

foreach ($questionNumber as $key=>$value){
    $sql="INSERT INTO $tbl_name(empID, empf, empl, QuestionNumber,  AnswerGiven)VALUES('$ruser','$fname', '$lname','$questionNumber[$key]', '$answerGiven[$key]')";
    // execute
}

If you are looking for a more efficient way of doing it, use prepared statements... this will also prevent SQL Injection

$sql="INSERT INTO $tbl_name(empID, empt, empl, QuestionNumber,  AnswerGiven)VALUES(?,?,?,?,?)";

if($stmt = $mysqli->prepare($sql)){
    foreach ($questionNumber as $key=>$value){
        $stmt->bind_param('sssis',$ruser, $fname, $lname, $questionNumber[$key], $answerGiven[$key]);
        $stmt->execute();
    }
    $stmt->close();
}else die("Failed to prepare query");