Making the final poll

The SQL translation of that relationship diagram is, as you can imagine, largely a modification of our previous version, with the primary difference being that storage of votes cast is now a table in its own right, and that we can track each individual member's voting history. For the sake of completion I have added a field to store when each vote was sent in, which allows further information to be extracted to examine voting date distribution. This could be finished off by having each question stamped with a date when it was made public so that questions like "what percentage of voters voted within a day of the poll being public?"

Here is the SQL to use:

CREATE TABLE pollquestions (ID INT NOT NULL AUTO_INCREMENT PRIMARY KEY, Question CHAR(255));
CREATE TABLE pollanswers (ID INT NOT NULL AUTO_INCREMENT PRIMARY KEY, Question INT, Answer CHAR(255));
CREATE TABLE pollvotes (ID INT NOT NULL AUTO_INCREMENT PRIMARY KEY, Question INT, Answer INT, UserID INT, DateVoted INT);

The UserID field in pollvotes will, of course, always be 22, but I have included it here so that you can more easily switch over to a full user management system.

The code for poll.php is unchanged from the previous example because our answers are in the same place as before. Storing the votes cast and also calculating the results is different, though, so you will need to read through the code for results.php again. Because the votes are split from the answers, I have used quite a complex query to calculate the results - don't worry if you have to go back to the Databases chapter to recap before continuing!

Still with me? Here is the code for results.php:

<?php
    $db = mysqli_connect("localhost", "phpuser", "alm65z", "phpdb");
    
    if (isset($_POST['vote'])) {
        $votenum = $_POST['vote'];
        $pollnum = $_POST['poll'];
        $result = mysqli_query($db, "SELECT ID FROM pollvotes WHERE UserID = 22 AND Question = $pollnum;");
    
        if (mysqli_num_rows($result)) { // voted before!
            echo "You've voted in this poll already!<br /><br />";
        } else {
            $CurrentTime = time();
            $result = mysqli_query($db, "INSERT INTO pollvotes (Question, Answer, UserID, DateVoted) VALUES ($pollnum, $votenum, 22, $CurrentTime);");
        
            if ($result) {
                echo "Thanks for voting!<br /><br />";
            } else {
                echo "There was an error with your vote; please contact the webmaster.<br /><br />";
            }
        }
    }
    
    echo "<strong>Results of poll:</strong><br />";
    $result = mysqli_query($db, "SELECT Answer FROM pollanswers WHERE Question = $pollnum;");
    
    while ($row = mysqli_fetch_assoc($result)) {
        $allanswers[] = $row['Answer'];
    }
    
    // here's the monster query
    $result = mysqli_query($db, "SELECT v.Answer, COUNT(v.ID) AS NumVotes, a.Answer FROM pollvotes v, pollanswers a WHERE v.question = $pollnum AND a.ID = v.answer GROUP BY v.answer ORDER BY NumVotes DESC;");
    echo "<ul>";
    
    $votedvotes = array();
    while ($row = mysqli_fetch_assoc($result)) {
        extract($row, EXTR_PREFIX_ALL, 'poll');
        $votedvotes[] = $poll_Answer;
        echo "<li>$poll_Answer: $poll_NumVotes</li>";
    }
    
    $notvotedanswers = array_diff($allanswers, $votedvotes);
    foreach($notvotedanswers as $answer) {
        echo "<li>$answer: 0</li>";
    }
    
    echo "</ul>";
?>

Yes, that is probably one of the longest code listings in the book, but it is well worth it because there is quite a bit of interest going on in there. Note how the script checks whether a vote has been cast by this person already - when the query checking the User ID and question number returns a row count, we can check whether that is 0 (not voted) or 1 (voted) with mysqli_num_rows().

Secondly, note that we use an array, $allanswers, to store the list of all possible answers, then intersect that against $votedvotes, a list of all answers that have been voted for at least once. This is done because our pollvotes table only contains answers that have been voted for at least once - if we did not do this, the results would not mention answers that have no votes.

Finally, there is the long query just after the halfway point in the script, which creates the poll result list. The reason it is so long is because it combines the pollvotes table with the pollanswers table and orders it by the number of votes cast.

Go ahead and update your code to these latest versions and give it a try - this level of poll is usually powerful enough for most purposes, and it is a good skill to keep to hand.

Copyright ©2015 Paul Hudson. Follow me: @twostraws.