Quiz - user submissions into MySQL database
What I am trying to achieve: a quiz system with user account and cumulative results table.
Process:
-User sets up an account -User logs in -User completes quiz -Answers go into results table -Results table displayed
My database structure:
Table 1: users
user_id
username
password
email
Table 2: quizzes
quiz_id
title
Table 3: questions
question_id
quiz_id
question
question_notes
Table 4: answers
answer_id
question_id
user_id
answer
answer_notes
Table 5: responses
response_id
quiz_id
user_id
submit_time
The questions will be output from Table 3 via a SELECT.
What I am looking for开发者_如何转开发 some pointers for is how I can ensure the relationships for each quiz entry is consistent, so when I run the INSERT statements the IDs are consistent (so the "question_id" for Table 3 and Table 4 are the same, for example)
I am thinking I will have 2 INSERT statements for Table 4 and Table 5. In these insert statements, is there a way to ensure the relationships match?
I am having some trouble visualising how this will work for entering the data into the database, once I've got this figured out I can tackle using the data.
Any pointers to decent tuts or a bit of insight into possible form processing would be much appreciated.
Many Thanks
There's LAST_INSERT_ID()
function in MySQL ( mysql_insert_id() in PHP ) that will return auto_increment id from last insert query. This will let you keep consistency.
See here for more details: http://dev.mysql.com/doc/refman/5.0/en/information-functions.html#function_last-insert-id
Foreign keys and transactions enforce referential integrity...
For example:
Answers
question_id is a foreign key to question.id
user_id is a foreign key to user.id
(These are set in the table definition)
You insert in to answers like so (sloppy pseudocode):
begin transaction
int qid = select id from question
int uid = select id from user
insert (qid,uid,...) into answers
commit transaction
精彩评论