php:Mysql: Question bank database design

i have a problem in my database designing for a php project of QUestion bank in which

user can create a questions and assign to it unlimited number of answers.

the problem is i currently store the questions as follows :

ID, Question , Answer

1 , "hood color?" , "green=hgreen,yellow=yellow"

i store all Answers with , as a seperatore and an = to separate the real value from the value that should be shown to user !.

so above example would be

<span>Hood color</span&g开发者_JS百科t;
<input type=checkbox value=hyellow>yellow
<input type=checkbox value=hgreen>green

any better ideas ?

---

Yes, create a different table for the answers:

Table Questions:
QuestionId
Question

Table Answers:
AnswerId
QuestionId
Answer (real answer)
Caption (caption to be shown to the user)

edit:

Selecting questions and their answers. Returns nothing for questions without answers.

select
  q.QuestionId,
  q.Question,
  a.AnswerId,
  a.Answer,
  a.Caption
from
  Questions q
  inner join Answers a on a.QuestionId = q.QuestionId

Change inner join to left join to get a single row for questions without answers. a.* will be NULL in those rows.

Other solution, query a list of questions first and get the answer for each question:

select
  a.AnswerId,
  a.Answer,
  a.Caption
from
  Answers a
where 
  a.QuestionId = <QuestionId you retrieved before>

---

PHP has some pretty good JSON support, you could save it as a JSON string ({"green": "hgreen", "yellow": "hyellow"})

---

table users, and a table answers (with 2 or more columns like "right_answer" and "wrong_answer" ...) maybe you can use mysql workbench for creating the databse structure =)