I've created a test system using Wordpress, jQuery and PHP which is working great. The area that I don't have much experience in is mysql databases.
I'm not really sure how to save the results in a neat and tidy way.
The things I want to save are the following:
Points 1, 2, 5 & 6 are fairly straight forward to save the data. The confusion comes with the questions and answers.
How can I save these in a neat way to the mysql database?
Say there are 10 questions, each with multiple choice answers, how can I save each of those in the database but have it linked to the user that filled them in? I'm not sure how to keep the database neat and this could quickly spiral out of control.
In the future I may want to do some searching on the those questions and answers.
You need to normalize your database structure.
**Table: users**
id - int
username - varchar
password - varchar
active - enum (y,n)
**Table: questions**
id - int
question - varchar/text
**Table: answers**
id - int
question_id - int (foreign key questions.id)
answer - varchar/text
is_correct_anser - enum (y, n)
**Table: test_master**
id - int
test_name - varchar/text
pass_score - int (minimum score required to pass the test)
**Table: test**
user_id - int (foreign key to users.id)
test_id - int (foreign key to test_master.id)
question_id - int (foreign key to questions.id)
answer_id - int (foreign key to answers.id)
Based on above tables, you can store all required information in in normalized way. Please note that, normalization will not always help and it depends on your application's requirement. It is also a better ideal to store total score in a separate table along with user_id and test_id once test is completed as this will avoid joins on multiple table.
"Neat" should make you think "normalization".
Start thinking about the relationships and don't worry about tables for now. You've already articulated some of them:
Once you have those sorted out the tables are relatively easy to describe.
create one table question in this you have question_id,question and choice. in the ohter table you need to save only the question_id,choice,username,score,testname and so on. the question_id will be the primary key questions table.