SitePoint Sponsor

User Tag List

Results 1 to 4 of 4
  1. #1
    SitePoint Zealot infoxicated's Avatar
    Join Date
    Jun 2001
    Location
    UK
    Posts
    140
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Lightbulb Collating results from MySQL vote

    Howdy,

    Can anyone think of a good way of collating results gathered during a voting process I've set up?

    The way I've done it so far is that the user is presented with a drop down list of all the documents they can vote for. They select their five favourite docs from this list and submit their vote.

    The entry in the voting table takes the form of this:

    id, firstname, secondname, vote_1, vote_2, vote_3, vote_4, vote_5

    With the ID number of each document being registered into each of the five vote fields.

    So, that was the easy part. What I need to do now is collate these votes and display them in a meaningful way. I'm not asking anyone to write the page for me, I'm just asking if anyone has done something similar and knows of a good way of getting the results and presenting them in a decent fashion.

    Thanks in advance

  2. #2
    Custom User Title v1.0 FireFly's Avatar
    Join Date
    Aug 2001
    Posts
    363
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I understand you want to sum all numbers in vote_1, vote_2, etc., right?
    Use MySQL's SUM() function, like this:
    Code:
    SELECT SUM(vote_1) AS total_1,SUM(vote_2) AS total_2,[...] FROM voting;

  3. #3
    SitePoint Zealot infoxicated's Avatar
    Join Date
    Jun 2001
    Location
    UK
    Posts
    140
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Sort of...

    I need to get the count of the number of votes that each document has! Tricky one!

    select vote_1, count(*) as count from votes group by vote_1

    is what I've come up with so far, but that only does one column. But I figured it was a step in the right direction, as it's giving me the total votes for each document. As in:

    ID - votes
    14 - 1
    16 - 1
    18 - 1
    19 - 2
    20 - 1
    21 - 2
    26 - 1
    30 - 1
    32 - 2
    41 - 1
    62 - 2

    So, from this I can probably do the same for all the columns as soon as I work out the syntax. However, it's collating the five columns that will be tricky.

    Thanks for your input, though
    Last edited by infoxicated; Mar 4, 2002 at 08:09.

  4. #4
    SitePoint Zealot infoxicated's Avatar
    Join Date
    Jun 2001
    Location
    UK
    Posts
    140
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Smile Done it!

    Ok, as I didn't receive much feedback, I can imagine that I chose a totally daft way of collecting the data in the first place. However, I did manage to solve it.

    What I done was to create a temporary table from all of the columns of votes in the first table. Then I ran a count(*) query on the temporary table to add up the amount of times the id's for each document appeared.

    This gave me the result I was looking for (or rather, the result that my boss was looking for! ) without constructing a ridiculously complicated query.

    Anyhow - I just thought I'd post how I solved it in case anyone else ends up stuck with the same monster - we all create them but not all of us are fortunate enough to tame them


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •