[SOLVED] Is this the proper way to do this(??)

[deepson2]

Hello,

 

I wanted to build up quiz for a site. there are 2 number of quiz

1)true and false

2)fill in the blank

 

We are getting the data through csv file(question and answers)

So my questions are

1)Can we create single row for one quiz?

like this

 

Quizid                  question                                                answers

  1                      Red,blue,green                                      apple,sky,grass

             

  2                    white,yellow,brown                                      5,3,7

 

 

then user's answers ll be store like this, (if userid =1)

id              userid                answers                quizid

  1                  1            apple,dress,grass            1

             

  2                  1                5,10,7                        2

 

 

so i can match the array values according to their positions to check how many answers are correct. then finally i ll have marks table, and it would be like this

 

id        userid          quizid    points

1          1                1        2

2          1                2        2

 

so ultimately user ll have 2 points for true and false quiz and 2 point for fill in the blank quiz.

 

2)Is this the proper method?

 

3) or i ll have to create different rows or every question?

 

why i want to do something like this because i want to keep quizid as unique id.So can anyone please tell me is this the proper method or i can do this in different way?

 

Sorry for the long description. I hope it is clear to understand.

 

Please help me.

 

Thanks in advance.

[kickstart]

Hi

 

You would be better splitting the questions / answers into seperate rows

 

Like this:-

 

QuestionId	Quizid	question	answers
1	1	Red	apple
2	1	blue	sky
3	1	green	grass
4	2	white	5
5	2	yellow	3
6	2	brown	7

 

Have a seperate table for the Quiz containing the quizID

 

All the best

 

Keith

[deepson2]

Thanks for your reply keith,

 

But then how can we differentiate that Red's answers is blue and sky's answer is blue?

 

well, in brief I would like to tell you what i want is as follows

 

if Monday is allotted for fill in the blanks quiz, and there are two question

Ex- (quizid=FB01)

Question

 

Spleen is situated in the --- side in the abdomen

 

Liver is situated in the --- side in the abdomen

 

answers

Left

Right

 

Because it is going to play on the same day i want to keep quizid unique for both the questions. like FB01, so when next Monday comes i can put two new question with quizid FB02.something like that.

 

So my question is, can i put both the question as well as answers in one column so i can keep one unique id or ll have to keep both the question in different row?

[kickstart]

Hi

 

Bit confused by what you think is the problem.

 

Have one table of quizes.

 

One table of questions per quiz, with the correct answers.

 

Another table listing attempts at a quiz (ie, AttemptId, UserId, QuizId, Date)

 

Have another table of attempts at a quiz with individuals answers (ie, AnswerId, AttemptId, QuestionId, EnteredAnswer).

 

Does that make sense?

 

All the best

 

Keith

[deepson2]

Yes that makes a sense.

 

So i can desgin my table structure like this.

 

1)Table - TrueFalse

QuestionId      Quizid        question  answers

1                  TF01          Red        apple

2                  TF01          blue      sky

3                  TF01          green      grass

4                  TF02          white      5

5                  TF02          yellow    3

6                  TF02            brown    7

                 

and

2)table -fillintheblack

 

QuestionId      Quizid        question            answers

  1            FB01                abc            cbd

 

 

and finally for the user's table

4)table- User

 

 

  id  userid  questionid    quizid        ansers    points

  1      1      1              TF01          banana      0

  2      1      5              TF01            3        1

  3      1      6              TF02          black      0

  4      2      1              FB01          CBD        1

  5      2      1              MC01          avd        1

 

 

Do you think this is the proper way to do different quiz and calculate all the points at the same time?

 

And could you please give me the reason why we cant we store every quiz in one column (TF01) and match their answers in array of the answers given by the user?

[kickstart]

H

 

Pretty much, although I am not sure there is a reason to split the 2 question tables. Only real difference seems to be how you present the question, so just have a column for how to present it (makes it easier when you present the question in a 3rd way in the future).

 

All the best

 

Keith

[deepson2]

Ok,

 

but could you please answer my following question.

 

why we cant store every quiz in one column (TF01) and match their answers in array of the answers given by the user?

[kickstart]

Hi

 

Expanding a column into an array in SQL is a pain. Would make it difficult to extract a score from the database for a person with a single SQL statement. You would probably have to extract the column, EXPLODE it in php into an array and then loop round checking each answer (I did find a way to do something similar to this in SQL, but it was horrible to read and pretty pointless).

 

All the best

 

Keith

[deepson2]

You are absolute right Kieth,

 

I was confused with the idea because someone suggested  us to do this way. but i guess this was not the proper way.

 

I just wanted opinion from someone who is genius in SQL

 

And i guess i got my answer. thank a lot!