One of the things I like about phpBB is the way they handle polls. The fact that you voted is recorded. How you voted is recorded. But there is no association of how you voted with your user account. Every poll vote is anonymous. That means that moderators, administrators, even board owners with access to their database cannot tell how you voted in a particular poll. I think that’s good. But some folks would like to be able to change their votes in a poll, or even be able to determine how someone else has voted. That requires a MOD.
I’ve written this MOD but never released it. It’s not too hard, actually. But the first thing you have to do is understand how the database tables related to the polling system work. Once you understand that, then you can start changing the process. There are three tables directly related to polling; each is detailed in this blog entry.
The first table associated with polling is the table that links the poll to the topic and records the poll question. It also records the poll duration, which is the number of days or – if left zero – infinite. It stores the poll start date which is required to calculate whether the poll has expired or not. There is one row in this table per poll. The unique (primary) key for this table is the vote_id, and that’s the column used to tie the rest of the poll information back to the poll master record stored in this table.
When you enter a poll you enter a poll question. That value is stored in the vote_text column in this table. You also enter the duration in days… that value is stored in the vote_length column. The poll duration is taken from the vote_start (a column automatically filled with the current date/time when the poll is saved) and adding the number of days that you entered for the duration. Unless the duration is zero, at which point the poll is active forever.
This table contains the text for each poll option (stored in vote_option_text) and an accumulated value for the total votes for each of the options (stored in vote_result). This is the reason why you can’t change your vote (or see what someone else voted). Votes are not stored individually, they are stored as an aggregate. More on that in a bit.
The column that identifies the unique row for the poll option is the vote_option_id column. There is also a vote_id column which is used to tie back (join) to the phpbb_vote_desc table discussed above. This is another example of a Primary Key / Foreign Key relationship I discussed earlier.
This table contains a record of who voted. Not what they voted, it just stores the fact that they did actually vote. It records the vote user_id and the IP address they were using when they voted. I’m not sure why; nothing in phpBB allows you to view the IP address of the vote. At least not that I am aware of.
The rows in the phpbb_vote_voters table are also tied to the phpbb_vote_desc table using the vote_id column. There is no relationship between the phpbb_vote_results table and the phpbb_vote_voters table. The unique key for this table is the combination of the vote_id and the vote_option_id values.
There is at least one released MOD that shows who voted in a poll. That is a fairly simple thing to do, as all you have to do is link the vote_voters table to the users table using the vote_user_id and user_id columns. Since each user can vote exactly once in each poll (in phpBB2) there will be only one match.
Storing Poll Results
Imagine a poll with 3 options to pick from. That would mean that there are three rows in the phpbb_vote_results table. The data might look like this:
+---------+----------------+------------------+-------------+ | vote_id | vote_option_id | vote_option_text | vote_result | +---------+----------------+------------------+-------------+ | 1 | 1 | Option 1 | 0 | | 1 | 2 | Option 2 | 0 | | 1 | 3 | Option 3 | 0 | +---------+----------------+------------------+-------------+
This shows the three options and their total accumulated votes… all zero at this point. Now let’s imagine that 5 users have voted. Three picked 1, two picked 2, and nobody has selected option 3 yet. There would be five rows in the phpbb_vote_voters table, one for each user. And the phpbb_vote_results table would look like this:
+---------+----------------+------------------+-------------+ | vote_id | vote_option_id | vote_option_text | vote_result | +---------+----------------+------------------+-------------+ | 1 | 1 | Option 1 | 3 | | 1 | 2 | Option 2 | 2 | | 1 | 3 | Option 3 | 0 | +---------+----------------+------------------+-------------+
How can I tell who voted? By checking the phpbb_vote_voters table using the vote_id value of 1. How can I tell what they voted? I can’t. The votes totals are incremented each time someon casts a vote; no individual voting values are stored.
So now that we understand how the data is stored, and why guests cannot vote, how do we go about setting things up so that I can allow registered members to change their vote? And perhaps allow appropriate folks to be able to see what a user has voted?
One More Column
All it takes is a little bit of coding and one extra column in the database.
alter table phpbb_vote_voters add vote_option_id tinyint(4) unsigned default 0;
Recall that the vote_option_id was the column in the phpbb_vote_results table that identified a specific choice in the poll. By adding that value to the phpbb_vote_voters table we have established a link between the user and the option on the poll that they selected. That’s all we have to do as far as database changes.
There are, of course, a few steps that need to be handled in the php code. First, this new column has to be populated when a user votes. If you only want to record what a user has voted you can stop right there and have a very simple MOD. If the poll is ever edited and a choice is removed you can now remove the users that voted for that option, allowing them to vote again. That would require some extra code too.
If you want to allow a user to change their vote then there are a few extra steps beyond that. phpBB already detects if a user has voted or not and displays the poll results or the poll entry form based on that value. When I wrote this MOD I added a link that read “Change Vote” on the poll results if the user had voted already. If they clicked that link, the poll went back to the voting form. This time when the user votes their vote is removed from their original choice and added to their new choice when they submit. I can do this because I can update the row in the phpbb_vote_voters table with the new choice, but just before I do the update I can obtain their current choice and reduce the votes for that item by one before adding one to the new choice.
This is an example of where we really need a transactional database. We need the entire transaction to succeed or fail together. We can’t have a partial update take place, as the poll could be left in an invalid state. You may have seen certain areas in the phpBB code where a BEGIN_TRANSACTION tag was added to a query… one of those is in includes/usercp_register.php where to properly create a user requires three successful inserts, not just one. (Depending on your table type MySQL may or may not support that concept.) At the end of the chain of SQL commands there is an END_TRANSACTION tag passed to the database processor. In theory these commands tell the database that every part of this transaction must be successful, and if not, undo the entire thing.
Even with these changes, guests still can’t vote. I have been asked, but I will never attempt to write an option that allows guests to vote in polls because I cannot guarantee that the poll results would be accurate (meaning limit of one vote per guest).