So, I'm trying to learn PHP and MySQL (I have a basic understanding of both; I've read the first half of both Head First SQL and Head First PHP & MySQL) and I figure the best way to solidify my knowledge is by building something rather than reading.
With that in mind, I would like to create a basic webpage that connects to a MySQL database on a server. I will build a basic HTML form and allow users to input basic information, such as: last_name, first_name, email, birthday, gender.
My problem is I don't know how to design a database that will record the results of a basic quiz - I want just 5 multiple-choice problems. Eventually, I would like to display the results of the user versus the previous users' results.
If you could help me understand how to design table(s) for a 5-question Quiz I'd appreciate it. Thanks!
I would start with 4 simple table:
* User - user_id auto integer - regtime datetime - username varchar - useremail varchar - userpass varchar * Question - question_id auto integer - question varchar - is_active enum(0,1) * Question_choices - choice_id auto integer - question_id integer - is_right_choice enum(0,1) - choice varchar * User_question_answer - user_id integer - question_id integer - choice_id integer - is_right enum(0,1) - answer_time datetime
My though on this table design is:
Useris for storing registered user.
Questionis for storing all your question. It has is_active so that you can selectively display only active question (using
WHERE is_active = '1')
question_choicesis for storing all available options. It has
is_right_choicewhich define what choice is the right answer for particular question.
User_question_answeris for storing answer from your user. It has
is_rightfor faster lookup, to see whether that particular question and answer choice is right (based on
is_right_choicepreviously defined). It also has
answer_timejust to note when that particular user answer the question.