How do you handle questions?

[jemz]

Hi, I was asked to create an online exam, it’s just multiple-choice, and I want the question and answer is inputted in the backend so that it would be dynamic. but I’m confused about how will I design my database and how am I going to insert the data? should I insert it to HTML or JSON to the table? . can you please enlighten my mind on how you guys do it?

Thank you in advance.

[SamA74]

Just thinking out loud here, I’ve not made an online exam before. But I think for a multiple choice, I may have a table of questions, with something like: question ID, question text, correct answer ID. Then another table of answers, with something like: answer ID, answer text, parent question ID.
That would probably cover the Q&A part, I guess you would also want some means of recording results too.

Moved to Database, as it seems at this point to be about the database structure.

[Pepster64]

I have used this database table setup for years and it has worked pretty good for me so far:

--
-- Table structure for table `trivia_questions`
--

CREATE TABLE `trivia_questions` (
  `id` int(11) NOT NULL,
  `user_id` int(11) NOT NULL DEFAULT '1',
  `hidden` varchar(15) COLLATE latin1_german2_ci NOT NULL DEFAULT 'no',
  `question` text COLLATE latin1_german2_ci NOT NULL,
  `answer1` char(100) COLLATE latin1_german2_ci NOT NULL,
  `answer2` char(100) COLLATE latin1_german2_ci NOT NULL,
  `answer3` char(100) COLLATE latin1_german2_ci NOT NULL,
  `answer4` char(100) COLLATE latin1_german2_ci NOT NULL,
  `correct` int(1) NOT NULL,
  `category` varchar(60) COLLATE latin1_german2_ci NOT NULL,
  `play_date` datetime DEFAULT CURRENT_TIMESTAMP,
  `day_of_week` int(3) NOT NULL DEFAULT '0',
  `day_of_year` int(3) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_german2_ci ROW_FORMAT=DYNAMIC;

--
-- Indexes for dumped tables
--

--
-- Indexes for table `trivia_questions`
--
ALTER TABLE `trivia_questions`
  ADD PRIMARY KEY (`id`);

--
-- AUTO_INCREMENT for dumped tables
--

--
-- AUTO_INCREMENT for table `trivia_questions`
--
ALTER TABLE `trivia_questions`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;
COMMIT;

I have no idea why it has latin1_german2_ci for the collation, I need to correct that. Though it works for some strange reason.