php: Mysql Database Design and Workflow, need more creativity !

i was wondering if any one can help me with my php-mysql design

my current app. (is a more or less survey app) it let users store questions about targeting specific features in other products also saved in other table in database !

for example , a user can post a car: and then ask users about there opion in safty elements of his car.

car db : Id,brand,safety

brand = Fast

saftety = ABS=ABS (Anti lock braking System),DriverAirBag=Air bags

questions db: ID,Question,Answer,Target,type

eg of data:

Question:safety options you like

Answer:ABS=ABS (Anti lock braking System),DriverAirBag=Air bags"

target:saftey

type=checkbox

problem is that to display questions stored, i have to .

1) loop through all questions, echo Question and echo target in hidden input,

2) explode Answer field twice(1st w/ "," to get each answer and other with "=" to differ > between whats inside the database[0] and a user friendly te开发者_如何学Pythonxt[1]

3) check type to chose display type (3 options checkbox,select,text)

4) set this display type of [0] and show [1] for user !!! (stupid i know:()

eg:

< checkbox value=$expolde[0]>$explode[1]

All these steps make it very hard to maintain, not flexable by any mean cause display is embeded in code :(,

any ideas :) ?

---

I would separate the tables into a one-to-many type design like:

 CarTable
       ID
       Brand
       Model

 CarInfo
      CarID      # Foreign key to Car Table
      Category   # Optional: Safety, Performance, Looks, etc...
      Value      # Specific Info Value: ABS, Air Bags, etc...

In this design you can have 0 to many CarInfo records for each Car making it easier to add/remove info records for a car without having to parse a potentially complex field like in your original design.

Your question table design could be similar depending on what your ultimate goal is:

 Question
    ID
    Description

 QuestionInfo
    QuestionID
    Category
    Value

Some other things you should be considering and questions you should be asking yourself:

1. How are you handling custom user inputs? If user1 enters "Air Bags" and user2 requests "Driver Side AirBag" how are you going to match the two?
2. Make sure you understand the problem before you attempt to solve it. It was not clear to me from your question what you are trying to do (which could be just me or limited size of the question here).
3. Be careful when outputting raw database values (like the type field in your question table). This is fine as long as the database values cannot be input by the user or are properly sanitized. Search for "SQL Injection" if you are not familiar with it.

---

If you want a survey PHP application, I suppose, to be clear, that you need something where:

1. one user can add a subject (a car in your example).
2. there can be an arbitrary number of questions attached to a subject by that user
3. each question can accept several types of answers: yes/no (checkbox input), a number (text input, or say 10 radiobuttons with values 1 to 10 attached etc), single or multiple choice (select with or without multi attribute), arbitrary data (textarea). Moreover, some questions may accept comments / "other, please explain" field.
4. any other user can answer all the questions, and all of them are stored.

A more sophisticated version will require different sets of questions based on what was replied previously, but it's out of the scope of this question, I hope.

For that I think you need several tables:

Subjects id int pri_key and anything that can come to mind: brand, type etc.

Questions id int pri_key, text varchar, subject int f_key, type int/enum/?

QuestionOptions id int pri_key, question int f_key, option varchar

Users id int pri_key + whatever your authentication structure is

UserReplies user int f_key, question int f_key, answer varchar, comments varchar

The user-creator sets up a subject and attaches several questions to it. Each question knows which type it is - the field 'type' may be an integer, or an enum value, I myself prefer storing such data as integer and defining constants in php, using something like QTYPE_MULTISELECT or QTYPE_BOOLEAN for readability.

For single/multiselect questions a user-creator also populates the QuestionOptions table, where the options for select-tag are stored.

To display all the questions there'll be something like

SELECT Questions.id, Questions.text, Questions.type, GROUP_CONCAT(CONCAT(questionOptions.id, questionOptions.option)) AS options FROM Questions LEFT JOIN QuestionsOptions ON (Questions.type = $select AND Questions.id = QuestionsOptions.question) WHERE Questions.subject = $subject GROUP BY Questions.id

The GROUP_CONCAT and CONCAT here should be modified to return something like 5:Option_One;6:Option_Two etc, so that exploding the data won't be much hassle.

I realize this is not the cleanest approach in terms of performance and optimization, but It should do for a non-large-scale project.

There is also a drawback in in the above design in that the answers to the "multiple answer question" are stored imploded in the "answer" field of the UserReplies table. Better add another table, where every record holds an option value the user selected for this or that question. That way there will be no unnecessary denormalization in the database and queries for statistics will be much easier (i.e. querying which options were most popular in a single question)