9GHS3gh0

1Troubled with making a PHP db driven multiple choice Quiz
2CREATE TABLE IF NOT EXISTS `answer` (
3  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
4  `question_id` int(10) unsigned NOT NULL,
5  `answer` varchar(100) NOT NULL,
6  PRIMARY KEY (`id`),
7  KEY `question_id` (`question_id`)
8) ENGINE=InnoDB ;
9
10CREATE TABLE IF NOT EXISTS `question` (
11  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
12  `quiz_id` int(10) unsigned NOT NULL,
13  `question` varchar(500) NOT NULL,
14  `correct_anwer_id` int(10) unsigned NOT NULL,
15  PRIMARY KEY (`id`),
16  KEY `quiz_id` (`quiz_id`,`correct_anwer_id`),
17  KEY `correct_anwer_id` (`correct_anwer_id`)
18) ENGINE=InnoDB ;
19
20CREATE TABLE IF NOT EXISTS `quiz` (
21  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
22  `category` int(10) unsigned NOT NULL,
23  `title` varchar(100) NOT NULL,
24  PRIMARY KEY (`id`)
25) ENGINE=InnoDB ;
26
27ALTER TABLE `answer`
28  ADD CONSTRAINT `answer_ibfk_1` FOREIGN KEY (`question_id`) REFERENCES `question` (`id`);
29
30ALTER TABLE `question`
31  ADD CONSTRAINT `question_ibfk_2` FOREIGN KEY (`correct_anwer_id`) REFERENCES `answer` (`id`),
32  ADD CONSTRAINT `question_ibfk_1` FOREIGN KEY (`quiz_id`) REFERENCES `quiz` (`id`);
33	
34CREATE TABLE IF NOT EXISTS `test` (
35  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
36  `user_id` int(10) unsigned NOT NULL,
37  `quiz_id` int(10) unsigned NOT NULL,
38  `date_taken` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
39  `score` mediumint(9) NOT NULL,
40  PRIMARY KEY (`id`),
41  KEY `quiz_id` (`quiz_id`)
42) ENGINE=InnoDB ;
43
44CREATE TABLE IF NOT EXISTS `test_answer` (
45  `test_id` int(10) unsigned NOT NULL,
46  `question_id` int(10) unsigned NOT NULL,
47  `answer_id` int(10) unsigned NOT NULL,
48  `single_score` mediumint(9) NOT NULL,
49  PRIMARY KEY (`test_id`,`question_id`),
50  KEY `question_id` (`question_id`),
51  KEY `answer_id` (`answer_id`)
52) ENGINE=InnoDB ;
53
54ALTER TABLE `test`
55  ADD CONSTRAINT `test_ibfk_1` FOREIGN KEY (`quiz_id`) REFERENCES `quiz` (`id`);
56
57ALTER TABLE `test_answer`
58  ADD CONSTRAINT `test_answer_ibfk_3` FOREIGN KEY (`answer_id`) REFERENCES `answer` (`id`),
59  ADD CONSTRAINT `test_answer_ibfk_1` FOREIGN KEY (`test_id`) REFERENCES `test` (`id`),
60  ADD CONSTRAINT `test_answer_ibfk_2` FOREIGN KEY (`question_id`) REFERENCES `question` (`id`);
61	
62INSERT INTO `answer` (`id`, `question_id`, `answer`) VALUES
63(3, 1, 'stackoverflow.com '),
64(4, 1, 'example.com');
65
66INSERT INTO `question` (`id`, `quiz_id`, `question`, `correct_anwer_id`) VALUES
67(6, 1, 'What is the best website on the whole internet?', 3);
68
69INSERT INTO `quiz` (`id`, `category`, `title`) VALUES
70(1, 1337, 'My Great Quiz. Take me!');