Leaderboard

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

<style type='text/css'> .lr-border { display: inline-block; border-left: 3px solid gray; border-right: 3px solid gray; padding: 0 8px; margin: 30px 10px; } </style> <div class='lr-border'> Are you wanting something like this? </div> <br> <div class='lr-border'> ( where the width adjusts automatically with the text length ) </div>