Saving Answers

Olumide · July 24

Hi,

I am having issues with this cbt project whereby my questions are selected 5 per page and when the user click on NEXT, the first page selected radio button option should be saved so that when the user click on previous button, he should be able to see the selected options. But I have been finding difficult to do.

And I don't think it is right to create another table again to store the user answers because in each subjects, there are more than 15 questions. Please here is exam script and the screen short of the first page of the exam.

<!DOCTYPE html>
<html>
<head>
    <title><?php echo htmlspecialchars($exam['title']); ?></title>
    <link rel="stylesheet" type="text/css" href="styles.css">
    <script src="https://code.jquery.com/jquery-3.6.0.min.js"></script>
  <script>
    $(document).ready(function() {
        let duration = <?php echo $remainingTime; ?>;
        let timer;

        function startTimer() {
            timer = setInterval(function() {
                duration--;
                if (duration < 0) duration = 0;
                $('#timer').text(`${Math.floor(duration / 60)}:${duration % 60 < 10 ? '0' : ''}${duration % 60}`);
                if (duration === 0) {
                    clearInterval(timer);
                    submitExam(); // Automatically submit the form when time expires
                } else if (duration <= 30) {
                    // Disable form elements when less than 30 seconds remain
                    $('#examForm input[type="radio"]').prop('disabled', true);
                    if (duration === 5) {
                        // Play alarm sound if time is below 5 seconds
                        new Audio('alarm.mp3').play();
                    }
                }
            }, 1000);
        }

        startTimer();

        function saveAnswers() {
            let answers = {};
            $('#examForm input[type="radio"]:checked').each(function() {
                let name = $(this).attr('name');
                let value = $(this).val();
                answers[name] = value;
            });

            return $.ajax({
                url: 'save_answers.php',
                type: 'POST',
                data: {
                    page: <?php echo $page; ?>,
                    answers: JSON.stringify(answers)
                }
            });
        }

        function submitExam() {
            clearInterval(timer);

            let answers = {};
            $('#examForm input[type="radio"]:checked').each(function() {
                let name = $(this).attr('name');
                let value = $(this).val();
                answers[name] = value;
            });

            $('<input>').attr({
                type: 'hidden',
                name: 'selected_answers',
                value: JSON.stringify(answers)
            }).appendTo('#examForm');

            $.ajax({
                url: 'submit_exam.php',
                type: 'POST',
                data: $('#examForm').serialize(),
                success: function(response) {
                    $('#examStatus').html(response);
                    $('#examForm').hide(); // Hide the form after submission
                },
                error: function() {
                    $('#examStatus').html('Error submitting exam.');
                }
            });
        }

        $('#examForm').on('submit', function(e) {
            e.preventDefault();
            if (duration > 0) {
                if (confirm('Are you sure you want to submit the exam?')) {
                    saveAnswers().done(function() {
                        submitExam();
                    });
                } else {
                    if (timer) clearInterval(timer);
                    startTimer();
                }
            } else {
                submitExam();
            }
        });

        $('.pagination a').on('click', function(e) {
            e.preventDefault();
            saveAnswers().done(function() {
                window.location.href = $(this).attr('href'); // Navigate to the new page
            }.bind(this));
        });

        function preselectAnswers() {
            let selectedAnswers = <?php echo json_encode($selected_answers); ?>;
            for (let questionId in selectedAnswers) {
                let answer = selectedAnswers[questionId];
                $(`input[name="answers[${questionId}]"][value="${answer}"]`).prop('checked', true);
            }
        }

        preselectAnswers();
    });
</script>

</head>
<body>
    <div class="header">
        <div class="timer">Time Remaining: <span id="timer"></span></div>
        <div class="subject">
            <h1><?php echo htmlspecialchars($exam['title']); ?></h1>
        </div>
        <div class="name">
            <?php echo htmlspecialchars($fullName); ?>
        </div>
    </div>
    <div style="padding-top: 60px; height: calc(100vh - 60px); overflow: auto;"> <!-- Adjusted for fixed header -->
        <form id="examForm">
            <input type="hidden" name="exam_id" value="<?php echo htmlspecialchars($exam['id']); ?>">
            <?php foreach ($questionsToDisplay as $index => $question): ?>
                <div class="question">
                    <p><?php echo ($offset + $index + 1) . '. ' . htmlspecialchars($question['question']); ?></p>
                    <?php
                    $options = isset($question['options']) ? json_decode($question['options'], true) : null;
                    if (is_null($options) || !is_array($options)) {
                        $options = [
                            'A' => $question['answer_a'] ?? '',
                            'B' => $question['answer_b'] ?? '',
                            'C' => $question['answer_c'] ?? '',
                            'D' => $question['answer_d'] ?? '',
                        ];
                    }
                    ?>
                    <ul style="list-style-type: none;">
                        <?php foreach ($options as $key => $option): ?>
                            <?php if (!empty($option)): ?>
                                <li>
                                    <label>
                                        <input type="radio" name="answers[<?php echo $question['id']; ?>]" value="<?php echo htmlspecialchars($key); ?>"
                                            <?php echo (isset($_SESSION['selected_answers'][$question['id']]) && $_SESSION['selected_answers'][$question['id']] === $key) ? 'checked' : ''; ?>>
                                        <?php echo htmlspecialchars($key . '. ' . $option); ?>
                                    </label>
                                </li>
                            <?php endif; ?>
                        <?php endforeach; ?>
                    </ul>
                </div>
            <?php endforeach; ?>
            <div class="pagination">
                <?php if ($page > 1): ?>
                    <a href="?page=<?php echo ($page - 1); ?>" class="pagination button">&laquo; Previous</a>
                <?php endif; ?>
                <?php if ($page < $totalPages): ?>
                    <a href="?page=<?php echo ($page + 1); ?>" class="pagination button">Next &raquo;</a>
                <?php endif; ?>
            </div>
            <button type="submit">Submit Exam</button>
        </form>
        <div id="examStatus"></div>
    </div>
</body>
</html>

save_answers.php

<?php
session_start();

if ($_SERVER['REQUEST_METHOD'] === 'POST') {
    $page = isset($_POST['page']) ? (int)$_POST['page'] : 0;
    $answers = isset($_POST['answers']) ? json_decode($_POST['answers'], true) : [];

    if (!isset($_SESSION['selected_answers'])) {
        $_SESSION['selected_answers'] = [];
    }
    
    // Update session with the received answers
    $_SESSION['selected_answers'][$page] = $answers;

    echo 'Answers saved.';
}
?>

screen short

Barand · July 24

1 hour ago, Olumide said:

And I don't think it is right to create another table again to store the user answers

Of course you should create a table for the students' answers, mainly because it gives an audit trail for the student's grade and provides a historical record of their past performance. (And if every student gave the same wrong answer you have evidence of poor teacher performance or cheating in the exam )

Olumide · July 25

Thank you sir, but I have created a table for user_responses and yet, while using the pagination (next, previous) the selected option are all deselected. Attached is the screenshot of page1 and page 2 and database with my script


DROP TABLE IF EXISTS `exams`;
CREATE TABLE IF NOT EXISTS `exams` (
  `id` int NOT NULL AUTO_INCREMENT,
  `title` varchar(255) DEFAULT NULL,
  `description` text,
  `duration` int DEFAULT NULL,
  `pass_mark` int NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=42 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

DROP TABLE IF EXISTS `questions`;
CREATE TABLE IF NOT EXISTS `questions` (
  `id` int NOT NULL AUTO_INCREMENT,
  `exam_id` int NOT NULL,
  `question` text NOT NULL,
  `answer_a` varchar(255) DEFAULT NULL,
  `answer_b` varchar(255) DEFAULT NULL,
  `answer_c` varchar(255) DEFAULT NULL,
  `answer_d` varchar(255) DEFAULT NULL,
  `correct_answer` char(1) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `exam_id` (`exam_id`)
) ENGINE=MyISAM AUTO_INCREMENT=252 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

DROP TABLE IF EXISTS `results`;
CREATE TABLE IF NOT EXISTS `results` (
  `id` int NOT NULL AUTO_INCREMENT,
  `user_id` int DEFAULT NULL,
  `exam_id` int DEFAULT NULL,
  `score` int DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `user_id` (`user_id`),
  KEY `exam_id` (`exam_id`)
) ENGINE=MyISAM AUTO_INCREMENT=37 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

DROP TABLE IF EXISTS `users`;
CREATE TABLE IF NOT EXISTS `users` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(100) DEFAULT NULL,
  `email` varchar(100) DEFAULT NULL,
  `password` varchar(255) DEFAULT NULL,
  `role` enum('admin','student') DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=29 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

DROP TABLE IF EXISTS `user_responses`;
CREATE TABLE IF NOT EXISTS `user_responses` (
  `id` int NOT NULL AUTO_INCREMENT,
  `user_id` int NOT NULL,
  `exam_id` int NOT NULL,
  `question_id` int NOT NULL,
  `selected_option` varchar(255) NOT NULL,
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=27 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

exam.php

<?php
if (session_status() == PHP_SESSION_NONE) {
    session_start();
}

include 'db_connect.php';

if (!isset($_SESSION['fullName']) || !isset($_SESSION['email']) || !isset($_SESSION['exam_id']) || !isset($_SESSION['user_id'])) {
    header('Location: instruction.php?exam_id=' . $_SESSION['exam_id']);
    exit;
}

$exam_id = $_SESSION['exam_id'];
$user_id = $_SESSION['user_id'];
$fullName = $_SESSION['fullName'];
$email = $_SESSION['email'];

$stmt = $pdo->prepare('SELECT * FROM exams WHERE id = ?');
$stmt->execute([$exam_id]);
$exam = $stmt->fetch(PDO::FETCH_ASSOC);

if (!$exam) {
    die('Exam not found');
}

$stmt = $pdo->prepare('SELECT * FROM questions WHERE exam_id = ?');
$stmt->execute([$exam_id]);
$questions = $stmt->fetchAll(PDO::FETCH_ASSOC);

$remainingTime = $_SESSION['remaining_time'] ?? ($exam['duration'] * 60);

if (isset($_SESSION['exam_start_time'])) {
    $elapsedTime = time() - $_SESSION['exam_start_time'];
    $remainingTime = max(0, $remainingTime - $elapsedTime);
} else {
    $_SESSION['exam_start_time'] = time();
}

$_SESSION['remaining_time'] = $remainingTime;

$selected_answers = $_SESSION['selected_answers'] ?? [];

$questionsPerPage = 5;
$totalQuestions = count($questions);
$totalPages = ceil($totalQuestions / $questionsPerPage);
$page = isset($_GET['page']) ? (int)$_GET['page'] : 1;
$page = max(1, min($page, $totalPages));
$offset = ($page - 1) * $questionsPerPage;
$questionsToDisplay = array_slice($questions, $offset, $questionsPerPage);
?>

<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <title><?php echo htmlspecialchars($exam['title']); ?></title>
    <link rel="stylesheet" type="text/css" href="styles.css">
    <script src="https://code.jquery.com/jquery-3.6.0.min.js"></script>
    <script>
    $(document).ready(function() {
        let duration = <?php echo $remainingTime; ?>;
        let timer;

        function startTimer() {
            timer = setInterval(function() {
                duration--;
                if (duration < 0) duration = 0;
                $('#timer').text(`${Math.floor(duration / 60)}:${duration % 60 < 10 ? '0' : ''}${duration % 60}`);
                if (duration === 0) {
                    clearInterval(timer);
                    submitExam();
                } else if (duration <= 30) {
                    $('#examForm input[type="radio"]').prop('disabled', true);
                    if (duration === 5) {
                        new Audio('alarm.mp3').play();
                    }
                }
            }, 1000);
        }

        startTimer();

        function saveAnswers(callback) {
            let answers = {};
            $('#examForm input[type="radio"]:checked').each(function() {
                let questionId = $(this).attr('name').match(/\d+/)[0];
                let value = $(this).val();
                answers[questionId] = value;
            });

            $.ajax({
                url: 'save_answers.php',
                type: 'POST',
                data: {
                    answers: JSON.stringify(answers)
                },
                success: function(response) {
                    if (callback) callback();
                }
            });
        }

        function submitExam() {
            clearInterval(timer);

            let answers = {};
            $('#examForm input[type="radio"]:checked').each(function() {
                let questionId = $(this).attr('name').match(/\d+/)[0];
                let value = $(this).val();
                answers[questionId] = value;
            });

            $('<input>').attr({
                type: 'hidden',
                name: 'selected_answers',
                value: JSON.stringify(answers)
            }).appendTo('#examForm');

            $.ajax({
                url: 'submit_exam.php',
                type: 'POST',
                data: $('#examForm').serialize(),
                success: function(response) {
                    $('#examStatus').html(response);
                    $('#examForm').hide();
                },
                error: function() {
                    $('#examStatus').html('Error submitting exam.');
                }
            });
        }

        $('#examForm').on('submit', function(e) {
            e.preventDefault();
            if (duration > 0) {
                if (confirm('Are you sure you want to submit the exam?')) {
                    saveAnswers(submitExam);
                } else {
                    if (timer) clearInterval(timer);
                    startTimer();
                }
            } else {
                submitExam();
            }
        });

        $('.pagination a').on('click', function(e) {
            e.preventDefault();
            saveAnswers(function() {
                window.location.href = $(this).attr('href');
            }.bind(this));
        });

        function preselectAnswers() {
            let selectedAnswers = <?php echo json_encode($selected_answers); ?>;
            for (let questionId in selectedAnswers) {
                let answer = selectedAnswers[questionId];
                $(`input[name="answers[${questionId}]"][value="${answer}"]`).prop('checked', true);
            }
        }

        preselectAnswers();
    });
    </script>
</head>
<body>
    <div class="header">
        <div class="timer">Time Remaining: <span id="timer"></span></div>
        <div class="subject">
            <h1><?php echo htmlspecialchars($exam['title']); ?></h1>
        </div>
        <div class="name">
            <?php echo htmlspecialchars($fullName); ?>
        </div>
    </div>
    <div style="padding-top: 60px; height: calc(100vh - 60px); overflow: auto;">
        <form id="examForm">
            <input type="hidden" name="exam_id" value="<?php echo htmlspecialchars($exam['id']); ?>">
            <?php foreach ($questionsToDisplay as $index => $question): ?>
                <div class="question">
                    <p><?php echo ($offset + $index + 1) . '. ' . htmlspecialchars($question['question']); ?></p>
                    <?php
                    $options = [
                        'A' => $question['answer_a'] ?? '',
                        'B' => $question['answer_b'] ?? '',
                        'C' => $question['answer_c'] ?? '',
                        'D' => $question['answer_d'] ?? '',
                    ];
                    ?>
                    <ul style="list-style-type: none;">
                        <?php foreach ($options as $key => $option): ?>
                            <?php if (!empty($option)): ?>
                                <li>
                                    <label>
                                        <input type="radio" name="answers[<?php echo $question['id']; ?>]" value="<?php echo htmlspecialchars($key); ?>"
                                            <?php echo (isset($selected_answers[$question['id']]) && $selected_answers[$question['id']] === $key) ? 'checked' : ''; ?>>
                                        <?php echo htmlspecialchars($key . '. ' . $option); ?>
                                    </label>
                                </li>
                            <?php endif; ?>
                        <?php endforeach; ?>
                    </ul>
                </div>
            <?php endforeach; ?>
            <div class="pagination">
                <?php if ($page > 1): ?>
                    <a href="?page=<?php echo $page - 1; ?>">&laquo; Previous</a>
                <?php endif; ?>
                <?php if ($page < $totalPages): ?>
                    <a href="?page=<?php echo $page + 1; ?>">Next &raquo;</a>
                <?php endif; ?>
            </div>
            <div style="text-align: center;">
                <button type="submit" style="display: inline-block;">Submit</button>
            </div>
        </form>
        <div id="examStatus"></div>
    </div>
</body>
</html>

save_answers.php

<?php
if (session_status() == PHP_SESSION_NONE) {
    session_start();
}

include 'db_connect.php';

if (!isset($_SESSION['exam_id']) || !isset($_SESSION['user_id'])) {
    die('Invalid request');
}

$exam_id = $_SESSION['exam_id'];
$user_id = $_SESSION['user_id'];

$answers = json_decode($_POST['answers'], true);

if (!$answers) {
    die('No answers to save');
}

$_SESSION['selected_answers'] = array_merge($_SESSION['selected_answers'] ?? [], $answers);

try {
    foreach ($answers as $question_id => $selected_option) {
        $stmt = $pdo->prepare('REPLACE INTO user_responses (user_id, exam_id, question_id, selected_option) VALUES (?, ?, ?, ?)');
        $stmt->execute([$user_id, $exam_id, $question_id, $selected_option]);
    }
    echo 'Answers saved successfully';
} catch (PDOException $e) {
    echo 'Error saving answers: ' . $e->getMessage();
}

submit_exam.php

<?php
if (session_status() == PHP_SESSION_NONE) {
    session_start();
}

include 'db_connect.php'; // Ensure this file sets up the $pdo variable

if ($_SERVER['REQUEST_METHOD'] === 'POST') {
    $exam_id = $_SESSION['exam_id'];
    $user_id = $_SESSION['user_id'];
    $selected_answers = json_decode($_POST['selected_answers'], true);

    // Insert or update user responses
    foreach ($selected_answers as $question_id => $selected_option) {
        $stmt = $pdo->prepare('REPLACE INTO user_responses (user_id, exam_id, question_id, selected_option) VALUES (?, ?, ?, ?)');
        $stmt->execute([$user_id, $exam_id, $question_id, $selected_option]);
    }

    // Calculate the score
    $score = 0;
    foreach ($selected_answers as $question_id => $selected_option) {
        $stmt = $pdo->prepare('SELECT correct_answer FROM questions WHERE id = ?');
        $stmt->execute([$question_id]);
        $correct_answer = $stmt->fetchColumn();

        if ($correct_answer === $selected_option) {
            $score++;
        }
    }

    // Insert or update the result in the results table
    $stmt = $pdo->prepare('REPLACE INTO results (user_id, exam_id, score) VALUES (?, ?, ?)');
    $stmt->execute([$user_id, $exam_id, $score]);

    // Destroy the session
    session_unset();
    session_destroy();

    // Response message
    echo "Exam submitted successfully! Your score is: $score";
}
?>

Screen shot

mac_gyver · July 25

your current symptom of not rechecking the radio fields is either because you changed the way you are saving the answers to the session variable (there would be php errors), and you didn't close all the instances of your browser to clear the session data and start over, or the session start is failing (there would be php errors.)

do you have php's error_reporting set to E_ALL (it should always be set to this value) and display_errors set to ON, so that php will help you by reporting and displaying all the errors it detects?

note: array_merge() renumbers numerical array indexes (your last code 'worked' for me but moved the checked entries to the next radio button due to the re-indexing.) you need to do what the 1st posted code was doing, and save them based on the page number, but you also need to reference the correct page of answers when you are rechecking the radio fields.

gizmola · July 30

This is unrelated to your current problem, but I really hate to see people make database design mistakes like this:

CREATE TABLE IF NOT EXISTS `questions` (
  `id` int NOT NULL AUTO_INCREMENT,
  `exam_id` int NOT NULL,
  `question` text NOT NULL,
  `answer_a` varchar(255) DEFAULT NULL,
  `answer_b` varchar(255) DEFAULT NULL,
  `answer_c` varchar(255) DEFAULT NULL,
  `answer_d` varchar(255) DEFAULT NULL,
  `correct_answer` char(1) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `exam_id` (`exam_id`)
) ENGINE=MyISAM AUTO_INCREMENT=252 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

So a couple of things:

Don't use the MyISAM engine. Literally nobody uses it for any serious deployment. You lose declarative referential integrity, ACID / transactions, a real query data cache, and row level locking at minimum.
- Use InnoDb! It is now the default engine (for a reason).
All your integer keys and foreign keys should be declared as unsigned. They can only ever be positive, so there is no reason to allow them to be signed values, which is just allocating half the available integer range to negative values.
answer is clearly a repeating group. You should see this whenever you have thing1, thing2... or thing_a, thing_b.

When you design wtih a repeating group you make the system less flexible and maintainable and the sql becomes fixated on structure and generally worse. The only concern here is the single join which in this case is not a concern. Instead you should remove the answer columns and replace that with a questions_answers tables.

Move correct as an attribute of the questions_answers value.
- This is also helpful as you might have a question with more than one acceptable value
Add an order column
- You can default to order by the id, but use this value to re-order answers if need be. So your code can display answers with ORDER BY sort, questions_answers_id
I added a unique index to user_responses on user_id, questions_answers_id to prevent a user from submitting more than one row with the same answer.
- You would want to enforce additional integrity yourself, which you could do in various ways
  - Make sure that a user isn't submitting a user_responses row with another answer to a question, where a previous answer for that user exists.
    - You can do this with pre-check SQL
      - This would be safe so long as you do a SELECT FOR UPDATE
    - Write an insert trigger for user_responses that does the same check

Here is an ERD for the structure I would suggest you adopt:

Obviously structural changes like this mean you will have to change code, and your current issues are no longer relevant until you recode to accommodate the more relationally correct structure.

Here's all the SQL

# ---------------------------------------------------------------------- #
# Add table "exams"                                                      #
# ---------------------------------------------------------------------- #

CREATE TABLE `exams` (
    `id` INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
    `title` VARCHAR(255),
    `description` TEXT,
    `duration` INTEGER,
    `pass_mark` INTEGER NOT NULL,
    PRIMARY KEY (`id`)
)
 ENGINE=InnoDB  DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;



# ---------------------------------------------------------------------- #
# Add table "questions"                                                  #
# ---------------------------------------------------------------------- #

CREATE TABLE `questions` (
    `id` INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
    `exam_id` INTEGER UNSIGNED NOT NULL,
    `question` TEXT NOT NULL,
    `is_active` TINYINT NOT NULL DEFAULT 1,
    PRIMARY KEY (`id`)
)
 ENGINE=InnoDB  DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE INDEX `exam_id` ON `questions` (`exam_id`);

# ---------------------------------------------------------------------- #
# Add table "users"                                                      #
# ---------------------------------------------------------------------- #

CREATE TABLE `users` (
    `id` INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
    `name` VARCHAR(100),
    `email` VARCHAR(100),
    `password` VARCHAR(255),
    `role` ENUM('admin','student'),
    PRIMARY KEY (`id`)
)
 ENGINE=InnoDB  DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;



# ---------------------------------------------------------------------- #
# Add table "questions_answers"                                          #
# ---------------------------------------------------------------------- #

CREATE TABLE `questions_answers` (
    `id` INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
    `questions_id` INTEGER UNSIGNED NOT NULL,
    `answer` TEXT NOT NULL,
    `is_correct` TINYINT NOT NULL DEFAULT 0,
    `sort` TINYINT NOT NULL DEFAULT 0,
    CONSTRAINT `PK_questions_answers` PRIMARY KEY (`id`)
)
 ENGINE=InnoDB  DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;



# ---------------------------------------------------------------------- #
# Add table "results"                                                    #
# ---------------------------------------------------------------------- #

CREATE TABLE `results` (
    `id` INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
    `user_id` INTEGER UNSIGNED,
    `exam_id` INTEGER UNSIGNED,
    `score` INTEGER UNSIGNED,
    PRIMARY KEY (`id`)
)
 ENGINE=InnoDB  DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE INDEX `user_id` ON `results` (`user_id`);
CREATE INDEX `exam_id` ON `results` (`exam_id`);

# ---------------------------------------------------------------------- #
# Add table "user_responses"                                             #
# ---------------------------------------------------------------------- #

CREATE TABLE `user_responses` (
    `id` INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
    `user_id` INTEGER UNSIGNED NOT NULL,
    `questions_answers_id` INTEGER UNSIGNED,
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`)
)
 ENGINE=InnoDB  DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE UNIQUE INDEX `IDX_user_responses_1` ON `user_responses` (`user_id`,`questions_answers_id`);

# ---------------------------------------------------------------------- #
# Add foreign key constraints                                            #
# ---------------------------------------------------------------------- #

ALTER TABLE `results` ADD CONSTRAINT `users_results` 
    FOREIGN KEY (`user_id`) REFERENCES `users` (`id`);

ALTER TABLE `results` ADD CONSTRAINT `exams_results` 
    FOREIGN KEY (`exam_id`) REFERENCES `exams` (`id`);

ALTER TABLE `user_responses` ADD CONSTRAINT `users_user_responses` 
    FOREIGN KEY (`user_id`) REFERENCES `users` (`id`);

ALTER TABLE `user_responses` ADD CONSTRAINT `questions_answers_user_responses` 
    FOREIGN KEY (`questions_answers_id`) REFERENCES `questions_answers` (`id`);

ALTER TABLE `questions_answers` ADD CONSTRAINT `questions_questions_answers` 
    FOREIGN KEY (`questions_id`) REFERENCES `questions` (`id`);

gizmola · July 30

The only thing I'd add about this structure is that it assumes that a user can only take an exam once.

You would need some additional changes to allow a user to take the same exam more than once.

You also might want to add "started_at" and "ended_at" timestamps to the results table. This would then become your record that a particular user started taking an exam, as you would make that row at the beginning of the exam, and can then use that to insure that the timing is not subverted.

You can re-intialize the timer, and also add timing checks on each answer submission that utilizes the "started_at" timestamp.

Dealmightyera · August 2

no table that store the exam and urser id

gizmola · August 8

On 8/2/2024 at 2:12 PM, Dealmightyera said:

no table that store the exam and urser id

At least in the structure I provided, that is not needed, as each question is related to an exam. What you are suggesting is to "de-normalize" by redundantly storing the exam id in a user_responses row. It is not necessary and should be avoided.

Sign In

Saving Answers

Recommended Posts

Olumide

Link to comment

Share on other sites

Barand

Link to comment

Share on other sites

Olumide

Link to comment

Share on other sites

mac_gyver

Link to comment

Share on other sites

gizmola

Link to comment

Share on other sites

gizmola

Link to comment

Share on other sites

Dealmightyera

Link to comment

Share on other sites

gizmola

Link to comment

Share on other sites

Join the conversation

Browse

Activity

Important Information