构建一个表单，其中无线电选项来自存储的数据库值

Question

Im trying to build a dynamic form that uses questions in the database as the form fields.

In my database, I have a table named questions, with "Question 1" "Question 2" and "Question 3". They are all yes or no questions.

Here is the code the correctly retrieves the info and builds my dynamic form:

<?php  require_once 'includes/session.php'; 
include 'includes/functions.php';

$questions = mysqli_query($dbconn, "SELECT * FROM rules");
?>

<form action="submit.php">
<?php
while($show= mysqli_fetch_array($questions))
{ ?>
 <label id="<?php echo $show['id']; ?>"><?php echo $show['Rule_Name']; ?></label><input name="<?php echo $show['id']; ?>" type="radio" value="Yes">Yes</input><input name="<?php echo $show['id']; ?>" type="radio" value="No">No</input><br>
  <?php  } ?> 
 <input name="submit" type="submit" value="submit" />
</form>

Because all these fields are dynamically generated, how would I structure the submit.php page to store all the values of the submitted form so that I may insert into my database.

Normally I would write my code like this when I know the values:

<?php
if($_POST) {
$answer1 = $_POST['question1'];

and so forth.

Since the values are unknown (don't know what questions are going to be added in the future) how would I build that to store the values of all the answers?

I spent some time googling, but didnt come across anything noteworthy. It may be my phrasing of the question was off.

Answer 1

In order to build a really dynamic system, you need to use the potential of the database. MySQL is a relational database for a reason.

Please do not store new questions as columns, but one per row. So, instead of having a questionnaire table like this

Questionnaire ID | Question 1 | Question 2 | ... | Question X

create two tables, one for the questionnaires:

Questionnaire ID | Questionnaire name | Description

and another one for the questions:

Questionnaire ID | Question Order/ID | Question text | Question options

That way, you can easily add new questions to any questionnaire without the need to alter the database schema. Further, you can use a similar setup for the answers:

Questionnaire ID | Question ID | User ID | Answer | Timestamp

Again, this table schema allows you to save individual answers without the need to alter the table schema when you add new questions.

To actually show a question form (aka load all required questions), you just query the questions table with the respective questionnaire ID, e.g.

SELECT * FROM questions WHERE questionnaire_id = 1 ORDER BY question_id ASC;

This will return all questions of your first questionnaire in the pre-defined order.

UPDATE

If you use tables similar to the ones I proposed above, storing the answers is fairly easy. First, add a hidden field to your form which stores the questionnaire ID, e.g.

<input type="hidden" name="questionnaire_id" value="1" />

Next, add your questions just like you do already:

<input name="<?php echo $show['id']; ?>" type="radio" value="Yes">Yes</input>

Eventually - to avoid ambiguous form field names - you can add the letter "q" in front of the name, so instead of having name="1" for the first question, you would get something like name="q1".

In your submit handler, just cycle through the POST vars:

foreach ($_POST as $key => $val) {
    if (substr($key, 0, 1) == "q") {
        // Remember the added "q"?
        // So, it's an answer to a question!
        $save_answer = array(
            'questionnaire_id' => (int) $_POST['questionnaire_id'], // the hidden form element
            'question_id' => substr($key, 1),
            'answer' => (in_array((string) $val, array('Yes', 'No')) ? (string) $val : null),
            'user_id' => [...], // your logic here
            'timestamp' => time(),
        );
    }
}