WEB ajax mysql poll question

Discussion in 'OT Technology' started by White Stormy, May 15, 2008.

  1. White Stormy

    White Stormy Take that, subspace!

    Joined:
    Sep 17, 2002
    Messages:
    85,489
    Likes Received:
    70
    Location:
    Sparkopolis
    worked this up yesterday.. http://www.halefx.com/temp/simplePoll.html
    the poll results are stored in a mysql table but the question itself isn't.. I don't think it will be that hard to fix that, though.


    if I want to prevent a user from voting more than once.. what's the best way to do that for each of the following situations?
    1. if you must be a registered user to vote at all. how do I structure a table for that? what if I want to save not only whether or not a user has voted, but WHAT they voted?
    2. if anyone can vote. save IPs? MACs? or just use cookies?
    appreciate the help. this was my first venture into mysql or databases at all. if there's already a thread on this, link? :x:
     
  2. Limp_Brisket

    Limp_Brisket New Member

    Joined:
    Jan 2, 2006
    Messages:
    48,422
    Likes Received:
    0
    Location:
    Utah
    saving IP's would be the most effective if anyone can vote. how do you plan on getting someone's MAC address?

    also it's kind of interesting that you had the php page that the ajax called return the actual HTML. usually you would just return the values and have the original page handle the HTML formatting.
     
  3. jdw

    jdw New Member

    Joined:
    Dec 31, 2005
    Messages:
    4,429
    Likes Received:
    0
    Location:
    ND
    oh the ironing... I just finished one for our networks (went 'AJAX' to get around our homepage caching).

    You're going to want multiple tables for this obviously...

    Poll Question with an ID and a question (and set any criteria for it being active or not, dates, etc).
    Poll Answers with an ID and a reference to question.ID.
    Poll Votes with a reference field to both question.ID and answers.ID. You'll also store some unique user data be it an IP address or another reference ID to a registered user table.


    The only 'REAL' way to make every vote unique to a user is to require registration.
     
  4. jdw

    jdw New Member

    Joined:
    Dec 31, 2005
    Messages:
    4,429
    Likes Received:
    0
    Location:
    ND
    It's so much easier this way...

    It's not 'technically' ajax simply because it's not returning XML...
     
  5. White Stormy

    White Stormy Take that, subspace!

    Joined:
    Sep 17, 2002
    Messages:
    85,489
    Likes Received:
    70
    Location:
    Sparkopolis
    why separate the questions and answers into two tables? why not have it all on one row in one table?
     
  6. White Stormy

    White Stormy Take that, subspace!

    Joined:
    Sep 17, 2002
    Messages:
    85,489
    Likes Received:
    70
    Location:
    Sparkopolis
    I wouldn't have thought to do it that way, honestly, but that's how the w3schools example did it so I went with it
     
  7. White Stormy

    White Stormy Take that, subspace!

    Joined:
    Sep 17, 2002
    Messages:
    85,489
    Likes Received:
    70
    Location:
    Sparkopolis
    :dunno: just throwin out ideas
     
  8. White Stormy

    White Stormy Take that, subspace!

    Joined:
    Sep 17, 2002
    Messages:
    85,489
    Likes Received:
    70
    Location:
    Sparkopolis
    I'm a database noob. I'm wondering how I should actually structure the stuff..

    say I required user registration, and I wanted to store every user's response to every poll.. how large can a table be before it gets inefficient? I mean I could just have a column (or "field", right?) for every question and a row for every user and store every answer as like 1 byte.. I doubt you'd ever have more than 8 options for one question.. that can add up really quickly. with a million users and 10,000 questions.. that's 10GB.

    I could split it up into multiple tables.. say one table for each letter of the alphabet that usernames start with, etc.. blah, blah, blah..

    or is there a more efficient way to store the results that would still efficiently report back a user's previous results
     
  9. jdw

    jdw New Member

    Joined:
    Dec 31, 2005
    Messages:
    4,429
    Likes Received:
    0
    Location:
    ND
    Because what if you have 4 answers for one poll and 2 answers for another? Sure you can build 4 fields into the database and not use some, but what if you want to do 10 answers down the road.




    (more when I get home from work)
     
  10. Josh

    Josh Guest

    You could also just have one table and one row for each poll. Just have a field for the question, then a full text field for the answers. Use some separator between the answers that a user wont be able to input. Then use the explode function to retrieve them. You can do this for the voting tallies as well and even keep the entire thing in a single row.
     
  11. RaginBajin

    RaginBajin Have you punched a donkey today?

    Joined:
    Dec 24, 2001
    Messages:
    8,740
    Likes Received:
    0
    Location:
    NoVA
    It takes more time to have to explode those results every time. If you want to change the answers in the poll, you have to go through pull it out look for the specific part, put it back together, and save it. Instead its a simple query to update the table.

    Also, if you want to do counts on a specific answer, it would take you a lot longer than letting the database do that for you.
     
  12. White Stormy

    White Stormy Take that, subspace!

    Joined:
    Sep 17, 2002
    Messages:
    85,489
    Likes Received:
    70
    Location:
    Sparkopolis
    right, so at what size (big or small) does the table become inefficient?

    I've been surprised already by how well it does certain things.. like retrieving a row that contains a certain value in a certain column/field

    seems like it does that instantaneously but it isn't even capable of counting through the rows without an ID field. I wasn't expecting that
     
  13. White Stormy

    White Stormy Take that, subspace!

    Joined:
    Sep 17, 2002
    Messages:
    85,489
    Likes Received:
    70
    Location:
    Sparkopolis
    but if you have 1000 poll questions.. you could end up with 1000 tables just to hold the possible answers

    would that really be faster than keeping them all in one table and exploding to separate? assume that I would NEVER want to change the answers for a poll.
     
  14. White Stormy

    White Stormy Take that, subspace!

    Joined:
    Sep 17, 2002
    Messages:
    85,489
    Likes Received:
    70
    Location:
    Sparkopolis
    or I could group poll questions into different tables based on how many answers they have.. have a 2-answer table, 3-answer table, etc.. then a lookup table to tell me which one each is in. lookup table would be 2 columns and even for like 10,000 poll questions, it would be under 100KB
     
  15. White Stormy

    White Stormy Take that, subspace!

    Joined:
    Sep 17, 2002
    Messages:
    85,489
    Likes Received:
    70
    Location:
    Sparkopolis
    you guys know any good sites with this kinda info so I don't have to keep bugging you? :o
     
  16. Josh

    Josh Guest


    I actually don't know the exact answer on this. I always try to limit the queries down though. So If I can get all of the data in one query I feel better about it. I don't know if this means it is the best or not, but I couldn't see why not. I don't think any sort of poll could possibly have too much data, even with 50 options to select.
     
  17. Josh

    Josh Guest


    Not exactly.. If you have 2 tables (I have built a poll site so I know this very well) you just have 1 table for all of the questions with an INT assigned ID. Another table for all of the answers, each answer has the POLL ID linked to it. Then you just select from answers where pollID = the id to the question.


    Exploding may take longer to code, and that is why I didn't use it when I coded my site. I think it could possibly be more efficient, but not for certain.
     
  18. White Stormy

    White Stormy Take that, subspace!

    Joined:
    Sep 17, 2002
    Messages:
    85,489
    Likes Received:
    70
    Location:
    Sparkopolis
    yeah but the other guy was saying that if you have some polls with 2 answers and some with say 5 answers.. you end up with 5 answer columns for ALL questions regardless of however many are needed for a specific question

    exploding answers from a single cell would allow you to lock in the size of the table so that no cells are even null

    there's gotta be a big O notation for this.. I always sucked at that :o
     
  19. Limp_Brisket

    Limp_Brisket New Member

    Joined:
    Jan 2, 2006
    Messages:
    48,422
    Likes Received:
    0
    Location:
    Utah
    no, you'd only have 2 tables. one row in the first table for each question and one row in the second table for each answer. they'd both have an ID to link them together so when you want one question and all it's answers you'd just do something like

    Code:
    select * from questions a inner join answers b on a.id = b.id
    
    that way you can have as many answers as you like. you should really avoid trying to stuff array like data in one column in the database; even though it would work it's bad database practice and will limit you in the end what you can do with the data; not to mention it's less efficient.
     
  20. Josh

    Josh Guest

    No only 2 columns for each answers. The ID and the answer. So in the table with the questions you'd have 1 row for each question. If you had 50 polls with 10 answers each, you'd have 500 rows in the answers table.
     
  21. White Stormy

    White Stormy Take that, subspace!

    Joined:
    Sep 17, 2002
    Messages:
    85,489
    Likes Received:
    70
    Location:
    Sparkopolis
    I just now understood what you meant. thanks

    so would you assign each answer a unique ID and use a separate table to store the results? or store the results in a third column of the answers table?
     
  22. Supergeek

    Supergeek New Member

    Joined:
    Jan 23, 2007
    Messages:
    1,855
    Likes Received:
    0
    Location:
    Colorado
    You'd want to do something like this for single choice polls:

    Table 1: Polls
    ==============
    ID (unique identifier for this poll)
    Description (like a title, or a short description for doing a listing of polls)
    Text (full length of the question)

    Table 2: Poll_Options
    =================
    ID (unique identifier for this option)
    Poll ID (what poll does this option belong to)
    Text

    Table 3: Responses
    ================
    ID (unique identifier for this response)
    Poll ID (what poll)
    Option ID (what option did the user choose)
    User ID/IP address/whatever unique identifier you use


    If you want to allow more than one choice, fill in the blank responses, etc, it gets a little more complicated. You could make each Response have an integer response and a varchar response, and make a Poll Type field in the Poll table to pull out which type of answer to allow.

    You *could* encode all of the questions and responses into just varchar fields, but then if you wanted to do any statistics, you'd kill the database trying to grind all that text.

    For more information about how to design database tables, look up "normalization."
     

Share This Page