Determining Who Voted for Which Option in an SMF Poll

SELECT smf_members.realName, smf_poll_choices.label
FROM smf_log_polls
INNER JOIN smf_members ON smf_log_polls.ID_MEMBER = smf_members.ID_MEMBER
INNER JOIN smf_poll_choices ON smf_log_polls.ID_CHOICE = smf_poll_choices.ID_CHOICE
AND smf_log_polls.ID_POLL = smf_poll_choices.ID_POLL
WHERE smf_log_polls.ID_POLL = 42

He suggested one should "Replace ID_POLL = 42 with the ID of the poll you want to check."

When someone else asked "How do you know what poll it is, the number?", another person responded "The topic number that the poll is in. Hover you [sic] mouse over a link to a topic to see the number in the url. (e.g. http://www.yoursite.com/community/index.php?topic=384.0)" However, I found that information was incorrect and that the topic number is not necessarily the same as the poll ID.

To obtain the poll ID through phpMyAdmin, log into phpMyAdmin and take the steps listed below. Note: these instructions were written for phpMyAdmin 2.11.10.

1. Select the appropriate database, which you should see listed on the left side of the webpage you get when you log into phpMyAdmin. E.g., if the database was named jdoe_smf, you would click on it to select it.
2. With the database selected, you will see a list of tables and above those a row of tabs labeled Structure, SQL, Search, Query, etc. Click on the one labeled SQL.
3. You can now type an SQL query in the field. Type the code below and then click on the Go button.

   select ID_POLL, question from smf_polls;

   Note: the prefix used for the tables in your database may not be smf. It might be something else. You can tell what it is by looking at the list of tables. They will all begin with smf_ or something else. If it is something else you would use that prefix instead of smf. E.g., if they all began with abcd_, you would use select ID_POLL, question from abcd_polls;, instead.

4. You should then see a table with a column with a heading of ID_POLL and another column with a heading of question. Find the question for the poll you are interested in and note the ID_POLL number for it.
5. Now click on the SQL tab again to issue a new SQL query. Delete anything already in the query field and use the code that n3rve suggested, but substitute that ID_POLL number for the number 42. Also substitute your table prefix for smf_ anywhere you see smf_ in the SQL query, if you are using a different prefix. E.g., if your prefix is abcd_, you would use it instead, e.g. you would have abcd_members.realName instead of smf_members.realName.

   SELECT smf_members.realName, smf_poll_choices.label
   FROM smf_log_polls
   INNER JOIN smf_members ON smf_log_polls.ID_MEMBER = smf_members.ID_MEMBER
   INNER JOIN smf_poll_choices ON smf_log_polls.ID_CHOICE = smf_poll_choices.ID_CHOICE
   AND smf_log_polls.ID_POLL = smf_poll_choices.ID_POLL
   WHERE smf_log_polls.ID_POLL = 42;

   Click on the Go button when you've typed or pasted the query into the field. When you do, you should see a table with two columns, one of which is the member's realName and the other column, which is labelled label, is the option selected by the member when he or she voted.

If you have command line access on the account you are using, you can also just enter the command mysql -p to be prompted for the MySQL password for the account your are using. When you enter that password, you will see the MySQL prompt and can enter the commands needed to obtain the information.

mysql> use jdoe_smf;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> select ID_POLL, question from smf_polls;
+---------+--------------------------------------------------+
| ID_POLL | question                                         |
+---------+--------------------------------------------------+
|       1 | Do you support the community building a pavilion |
+---------+--------------------------------------------------+
1 row in set (0.00 sec)

mysql> SELECT smf_members.realName, smf_poll_choices.label
    -> FROM smf_log_polls
    -> INNER JOIN smf_members ON smf_log_polls.ID_MEMBER = smf_members.ID_MEMBER
    -> INNER JOIN smf_poll_choices ON smf_log_polls.ID_CHOICE = smf_poll_choices.ID_CHOICE
    -> AND smf_log_polls.ID_POLL = smf_poll_choices.ID_POLL
    -> WHERE smf_log_polls.ID_POLL = 1;
+----------+-------+
| realName | label |
+----------+-------+
| Jane     | Yes   |
| John     | Yes   |
+----------+-------+
2 rows in set (0.00 sec)

mysql>

Note: if you aren't familiar with MySQL the -> are just indications that information is continued on another line. You can paste the whole query in at once.

In the example above, there were only two votes cast in regards to the poll on "Do you support the community building a pavilion". Jane and John both voted "Yes".

Created: August 8, 2010