well, I would probably modify some poll software to fit my needs but meh.
you'll probably want a table for each survey. It must include an ID and name at minimal. You could also include information like a description, who can vote, etc, but we'll start simply. So here's the first table's schema.
SURVEY(id, title)
Next we need a table to store questions. Each question needs to be associated with a survey, have it's own ID, and a title (or question).
QUESTION(id, survey_id, scale_id, title)
Next we need answer scales to associate with questions (for example, delighted/satisfied/disappointed). These will need an ID and description at the least.
SCALE(id, title)
Now we need a table for possible answers. Each answer should store it's scale id and an id, as well as a title and probably an order or value indicator (to say delighted is better than satisfied for example).
SCALE_VALUES(id, scale_id, title, value)
Now we need a table to associate questions with answer scales. it should have a question id and a scale id.
QUESTION_SCALE(question_id, scale_id)
Since each survey can have multiple questions, we need to group the answers into 'result' groups. this allows us to associate answers with each other. We need an id, a survey_id, and the submitter's IP (could also be username if you've got a login interface).
RESULT(id, survey_id, IP)
Now we need a place to store answers. this should have an answer id, question id and scale value (what the actual answer was). We also need to store what result group this answer is in.
ANSWER(id, result_id, question_id, value)
So that outlines a very basic schema for your database. Now here are a couple of SQL queries you'd use to do things with the DB.
add a scale called 'satisfaction'
Code:
INSERT INTO SCALE(id, title)
VALUES(1, 'satisfaction')
add values to scale 'satisfaction'
Code:
INSERT INTO SCALE_VALUE(id, scale_id, title, value)
VALUES(1, 1, 'delighted',1);
INSERT INTO SCALE_VALUE(id, scale_id, title, value)
VALUES(1, 2, 'delighted',2);
INSERT INTO SCALE_VALUE(id, scale_id, title, value)
VALUES(1, 3, 'delighted',3);
create a survey called 'survey1'
Code:
INSERT INTO SURVEY(id, title)
VALUES(1, 'survey1')
add a question called 'satisfaction level' to 'survey1'
Code:
INSERT INTO QUESTION(id, survey_id, scale_id, title)
VALUES(1, 1, 1, 'satisfaction level'
add a survey result/answer set (here we're answering 'delighted'.
Code:
INSERT INTO RESULT(id, survey_id, IP)
VALUES(1, 1, 127.0.0.1);
INSERT INTO ANSWER(id, result_id, question_id, value)
VALUES(1, 1, 1, 1)
note that we could have multiple questions in a survey, so we might need to run the second part of that query multiple times.
Now to view the results of the survey you could run several queries.
how many submissions to survey1?
Code:
SELECT count(RESULT.id)
FROM SURVEY JOIN RESULT ON SURVEY.id = RESULT.survey_id
WHERE SURVEY.title = 'survey1'
how many people gave each answer in 'survey1'
Code:
SELECT count(ANSWER.id), QUESTION.title, SCALE_VALUE.title
FROM SURVEY JOIN RESULT ON SURVEY.id = RESULT.survey_id JOIN ANSWER ON RESULT.id = ANSWER.result_id JOIN QUESTION ON ANSWER.question_id = QUESTION.id JOIN SCALE_VALUE ON ANSWER.value = SCALE_VALUE.id
WHERE SURVEY.id = 'survey1'
GROUP BY RESULT.id, QUESTION.title, SCALE_VALUE.title
This outlines the database structure you're likely to need. Of course you'll need a bit of PHP still to pull this off, but with a good data structure beneath, you should have a much easier time.
I have to go now, but if I get a chance later I'll come back to outline some of what you'll need to do on the PHP side.
[note: SQL code totally untested. should work, but no garantees
]
