Collating results from MySQL vote

[infoxicated]

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

[FireFly]

I understand you want to sum all numbers in vote_1, vote_2, etc., right?
Use MySQL's SUM() function, like this:

SELECT SUM(vote_1) AS total_1,SUM(vote_2) AS total_2,[...] FROM voting;

[infoxicated]

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

[infoxicated]

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