Using Array and For Loop in MySql query / procedure

[Dan Witten]

Hi, got a question regarding a MySQL database. The database is for tracking online votes. The table is simple:

VoteID
VoteDate
UserName (of person voting)
(Then 8 fields of candidates) if voted for field will contain a YES otherwise it's blank.

VoteSara
VoteDan
VoteTom
VoteJack
VoteJane
VoteMark
VoteCindy
VoteLori

I need to count the votes for each person. I can do:

SELECT count(VoteSara) FROM `AHOF_Vote_Tbl` WHERE VoteSara = 'YES'

Which works fine for that one person, I would like to be able to write ONE SQL Statement / Procedure that would count all the votes for each person. Any thoughts or suggestions? I am thinking an array and for loop, but not sure how to do that.

Also, you can only select up to 6 of the 8 candidates. So some rows have 6 YES, others only selected 1 or 2 people.

Thanks!

Dan

[Rabbit]

First of all, you table structure is bad. You have pivoted your data and therefore made it unnormalized. You will need to unpivot your and then do an aggregate query with a group by.

[Dan Witten]

I see what you are saying. Thanks. This was a "quick" one-time type setup that I did not put much thought into. I just needed a way to count votes, because the first method of using emails to send the vote count did work 100% of the time. Regardless, it is what it is. I will see what I can do to use the UNPIVOT method to try to get what I am looking for.

[Rabbit]

I don't know if MySQL has unpivot or not but if not you can use a union query to manually unpivot