i need help regarding polls in php

[turkman]

Help from everything like how to set up the table and implement it.

 

The main problem im having is that users will create the polls. This means that one table would need to store every poll created, also that each poll might have a different number of options i.e some with 3 some with 5 some with 10,11,15,20 etc etc.

 

I've no idea how to account for that or how to layout the page for them to design it! Or how to get info from the options the have filled in etc etc!! i know that you use $_POST to get info, but if they are adding x amount of options, how do you know the names of them.

[btherl]

You can store the poll structure with a table having user id, poll id, poll name, option number, answer.  Then you can store one row for each possible answer in the poll.  Answers to the poll would be stored in another table having user id (of the answerer, not the poll owner), poll id, option number.

 

If you want to make the interface for designing the poll simple to code, you can give them a fixed number of text boxes, one per answer, say 10 or 20 boxes.  You can name them "poll_answer[]" for example, and php will give you an array called $_POST['poll_answer'].  It's the "[]" in the variable name that tells php to make it into an array.

[phpeter]

You can also store the poll options/answers in one table cell, separated by a comma and (if you want) a space. Then when you want to retrieve the data use explode().

[gizmola]

Help from everything like how to set up the table and implement it.

 

The main problem im having is that users will create the polls. This means that one table would need to store every poll created, also that each poll might have a different number of options i.e some with 3 some with 5 some with 10,11,15,20 etc etc.

 

I've no idea how to account for that or how to layout the page for them to design it! Or how to get info from the options the have filled in etc etc!! i know that you use $_POST to get info, but if they are adding x amount of options, how do you know the names of them.

 

Some years ago I developed a survey product with some other people, and started a company around it.  You can really get a complicated design for a tool like that.  I'm not going to go to that extreme, but I'll give you a simplified db design that is typical of these apps. 

 

 

# ---------------------------------------------------------------------- #
# Tables                                                                 #
# ---------------------------------------------------------------------- #

# ---------------------------------------------------------------------- #
# Add table "poll"                                                       #
# ---------------------------------------------------------------------- #

CREATE TABLE poll (
    poll_id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
    creator_id INTEGER,
    question VARCHAR(255),
    description TEXT,
    createdOn TIMESTAMP,
    CONSTRAINT PK_poll PRIMARY KEY (poll_id)
);

# ---------------------------------------------------------------------- #
# Add table "pollChoice"                                                 #
# ---------------------------------------------------------------------- #

CREATE TABLE pollChoice (
    poll_id SMALLINT UNSIGNED NOT NULL,
    pollChoice_id MEDIUMINT UNSIGNED NOT NULL AUTO_INCREMENT,
    choice VARCHAR(255),
    CONSTRAINT PK_pollChoice PRIMARY KEY (poll_id, pollChoice_id)
);

# ---------------------------------------------------------------------- #
# Add table "pollAnswer"                                                 #
# ---------------------------------------------------------------------- #

CREATE TABLE pollAnswer (
    poll_id SMALLINT UNSIGNED NOT NULL,
    pollChoice_id MEDIUMINT UNSIGNED NOT NULL,
    user_id INTEGER UNSIGNED NOT NULL,
    answeredOn TIMESTAMP,
    CONSTRAINT PK_pollAnswer PRIMARY KEY (poll_id, pollChoice_id, user_id)
);

# ---------------------------------------------------------------------- #
# Foreign key constraints                                                #
# ---------------------------------------------------------------------- #

ALTER TABLE pollChoice ADD CONSTRAINT poll_pollChoice 
    FOREIGN KEY (poll_id) REFERENCES poll (poll_id);

ALTER TABLE pollAnswer ADD CONSTRAINT pollChoice_pollAnswer 
    FOREIGN KEY (poll_id, pollChoice_id) REFERENCES pollChoice (poll_id,pollChoice_id);

 

If you're using myisam the constraints aren't going to matter, and can be stripped out.  This will perform well for most queries because there are covering indexes.  I made the relationships defining, which offers the convenience of not having to join the tables together in many situations.  You would probably need an index in the pollAnswer table that is on user_id.  Of course the existence of user_id assumes you have a user system and you might need to tweak or change those columns to fit what you have.  The important thing is that you have to link in those tables to the user who creates the poll and the one who answers it.

[gizmola]

Here's the ERD diagram for the tables:

 

[turkman]

thanks for the replies, the last one seems kinda complex for me personnaly to implement, the first solution seems good. So i'll probably go with something like that, thanks for your help.

[gizmola]

thanks for the replies, the last one seems kinda complex for me personnaly to implement, the first solution seems good. So i'll probably go with something like that, thanks for your help.

 

Huh?  What I provided was the actual implementation details you would need to do what was described in btherl's reply. 

[btherl]

thanks for the replies, the last one seems kinda complex for me personnaly to implement, the first solution seems good. So i'll probably go with something like that, thanks for your help.

 

Huh?  What I provided was the actual implementation details you would need to do what was described in btherl's reply. 

 

Yours was considerably scarier   Even though it is the same thing.  It actually reminds me of this: http://www.ariel.com.au/jokes/The_Evolution_of_a_Programmer.html

[gizmola]

Haha, well my kids agree that I'm scary, especially when I'm chasing them around with an ERD diagram in hand.