1

I have 3 tables in mySQL database.

questions_table with these fields:

  1. question_id (unique ID for the question)
  2. question_name (the name of the question)
  3. question_positionID (the position of the question, I need this because the admins are able to change the positions of the questions in the html form in the frontend. I use this to display the questions in the form appropriately)

questions_users_relationship_table with these fields:

  1. questions_answers (this is the field where it stores what the users answered in the frontend form)
  2. user_id (this is used to connect to the users table, when loading the specific user's answers to the form)
  3. question_id (this is used to reference the question in the questions_table)

users_table with these fields:

  1. user_id (unique id)
  2. username (as the name implies, username)
  3. password (as the name implies, password)

So, erm.. The reason why have these 3 tables in the database is because I have to build an admin section on the website which allows the administrators to switch and change the position of the questions in a form. Therefore the reason for the question_positionID field in the questions_table. I have no problem in listing down the questions and its respective answers in the admin section for the administrators to do the position change ( i used ajax drag n drop to update the mysql table on the question_positionID field)..

My problem lies on the frontend html form itself. Because In the database, I only stored the questions and also its answers in its respective tables.. and did not store what type of questions it is (for example: a gender question might use radio buttons for its answers (Male/Female) or maybe address field which has the Country field with answers in the form of a drop down box).. So, in the frontend html form, I am not sure How to list down the questions and its answers for the respective user_id sorted based on the question_positionID and still would properly display the correct form elements (example: questions with dropdown with list the value in the dropdown boxes, questions with answers in radio buttons would be properly checked in the respective radio buttons, and etc etc)..

I believe many people would love to know how to do it.. I not an expert in PHP and mySQL and slowly learning it through examples and online tutorials.. So I would appreciate it if you guys could shed some light on this question of mine..

Improve this question
4
  • What about storing type of questions in the database? Commented Dec 28, 2010 at 7:54
  • Hi Col. Shrapnel.. Thanks for reading through my long questions.. Hope I did not bore you >.<.. Are you suggesting that I should add a field in the questions_table and then store the type of questions? and then I will be able to list down the proper question/answer elements in the frontend form? Commented Dec 28, 2010 at 7:55
  • Do not forget that you will have to store the possible answers,too. Commented Dec 28, 2010 at 8:00
  • Hi Oliver A, thanks for your input.. I think I sort of caught the idea/concept that both you and Shrapnel suggested but.. If you guys are able to provide a simple example.. using the tables I listed in my question as reference, it would help me to understand and form my own solutions :) Commented Dec 28, 2010 at 8:01

1 Answer 1

Reset to default
2

You should add another table with answers:

Answer

  • answer_id
  • question_id
  • answer_text

If there are multiple correct answers you will have to add a "is_correct" column to the table as well.

A simple approach to decide how to display the possible answers in the frontend to add a column allow_multiple_answers to your questions_table and decide whether to use dropdown or radio buttons depending on the number of possible answers.

$question//the question
$answers //an array with the possible answers
$maxAnswersForRadio//maximum numbers of oprions to be displayed with radio buttons 
if($question['allow_multiple_answers'){
  //use checkboxes to allow mulltiple answers.
  $html = generateCheckboxes($answers);
}
else{
   if (count($answers<=$maxAnswersForRadio){
      $html= generateRadioButtons($answers);
   }
   else{
      $html=generateDropDownList($answers);
   }
}

This is just a example. The sollution depends on the framework/template engine you use.

Improve this answer
Sign up to request clarification or add additional context in comments.

3 Comments

Hi Oliver.. Thanks for the quick answer :) Appreciate it.. From what you have suggested, is it alright if I do it this way: I add a new column called "question_type" to my questions_table with the data being enum type with 'text','radio','checkbox','select','textarea'. And when I am displaying the questions in the frontend html, I would use either a FOR or WHILE loop (after getting my sql query) and then within each FOR/WHILE loop, I would use a SWITCH Case or IF/ELSE to detect what question type it is and write the respective answer there.. I hope I'm making sense~
Yes this should work. However: using a template engine would make things a lot easier. Have look at phptal.org
Thanks Oliver for the template engine suggestion.. I think im still quite new to template engines so I'll stick to just PHP first.. :)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.