0

I am new to databases , I am using Mysql to create a simple quiz generator, my question is I need to store in mysql database the question entered and its choices ..so what I need is a column for the question and column for the choices (array maybe) ..how I can do that, I read about relational databases but I didnt get how I can do it with my case.

NASHEN
  • 53
  • 5
  • Don't do arrays in one column. Do something like [Junction Tables](http://stackoverflow.com/questions/32619895/how-to-store-multiple-options-in-a-single-table/32620163#32620163) for more complex things such as many-to-many, For simpler ones, you would have less tables – Drew Nov 16 '15 at 20:55

2 Answers2

1

You can create one table for questions and other for possible answers.

questions:

id | question
---+---------------------------------
1  | What kind of music do you like?
2  | Favourite movies?
3  | Your car?

answers:

id | question_id | answer
---+-------------+-------------
1  | 1           | pop
2  | 1           | rock
3  | 1           | disco
3  | 2           | james bond
3  | 2           | pulp fiction
3  | 3           | seat
3  | 3           | bmw
3  | 3           | honda

and then you can get question by query:

SELECT * FROM questions WHERE id = 2

and get possible answers for this question by query:

SELECT * FROM answers WHERE question_id = 2

You can also read about relations in database. It will accelerate your application and give some other profits. But if you just want to have questions and answers for small application you just can use my example.

Kamil P
  • 782
  • 1
  • 12
  • 29
0

You want two tables - one for your quiz data (question) and one for the choices. Your choices would join to your quiz table via a foreign key. That way you can have as many choices (rows) as you'd like.

For example:

CREATE TABLE quiz (
    quiz_id INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    question VARCHAR(250) NOT NULL
);

+---------+-----------------------------+
| quiz_id |          question           |
+---------+-----------------------------+
|       1 | What time is it?            |
|       2 | Where should we eat dinner? |
+---------+-----------------------------+

CREATE TABLE quiz_choices (
    choice_id INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    quiz_id INT(6) REFERENCES quiz (quiz_id)
    choice_name VARCHAR(30) NOT NULL
);

+-----------+---------+-------------+
| choice_id | quiz_id | choice_name |
+-----------+---------+-------------+
|         1 |       1 | 6:00        |
|         2 |       1 | 7:00        |
|         3 |       2 | Place A     |
|         4 |       2 | Place B     |
+-----------+---------+-------------+
Will
  • 2,790
  • 19
  • 22