Putting Poll v2 into action

The new table structure basically splits the questions and answers in two, linked by the pollquestions primary key. Here is how that looks in SQL:

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), Votes INT DEFAULT 0);

For test purposes, I entered the following SQL data for my example question and answers:

INSERT INTO pollquestions (Question) VALUES ('Favourite film?');
INSERT INTO pollanswers (Question, Answer) VALUES (1, 'Casablanca');
INSERT INTO pollanswers (Question, Answer) VALUES (1, 'Aliens');
INSERT INTO pollanswers (Question, Answer) VALUES (1, 'Indiana Jones');
INSERT INTO pollanswers (Question, Answer) VALUES (1, 'Pride & Prejudice');
INSERT INTO pollanswers (Question, Answer) VALUES (1, 'The Matrix');
INSERT INTO pollanswers (Question, Answer) VALUES (1, 'Kill Bill');

The PHP code for this more difficult poll is shorter than before, but more complicated. Here is how poll.php looks once modified for use in the new schema:

<?php
    $db = mysqli_connect("localhost", "phpuser", "alm65z", "phpdb");
    
    $result = mysqli_query($db, "SELECT ID, Question FROM pollquestions ORDER BY ID DESC LIMIT 1;");
    extract(mysqli_fetch_assoc($result), EXTR_PREFIX_ALL, 'poll');
    
    echo "<form method=\"post\" action=\"results.php\">";
    echo "<strong>$poll_Question</strong><br />";
    
    $result = mysqli_query($db, "SELECT ID, Answer FROM pollanswers WHERE Question = $poll_ID");
    while ($row = mysqli_fetch_assoc($result)) {
        extract($row, EXTR_PREFIX_ALL, 'vote');
        echo "<input type=\"radio\" name=\"vote\" value=\"$vote_ID\">$vote_Answer</optin><br />";
    }
    
    echo "<input type=\"submit\" value=\"Vote\" />";
    echo "<input type=\"HIDDEN\" name=\"poll\" value=\"$poll_ID\" />";
    echo "</form>";
?>

The code above is almost exactly the same as before, except now we do two queries into the database for the question and answer information. This could have been done in one query using a join, and this would probably be faster, but it would have required more explanation than it was worth!

Here is the accompanying results.php file:

<?php
    $db = mysqli_connect("localhost", "phpuser", "alm65z", "phpdb");
    
    if (isset($_POST['vote'])) {
        $votenum = mysqli_real_escape_string($db, $_POST['vote']);
        $questionID = mysqli_real_escape_string($db, $_POST['poll']);

        $result = mysqli_query($db, "SELECT ID FROM pollanswers WHERE ID = '$votenum' AND Question = '$questionID';");
    
        if (mysqli_num_rows($result)) {
            $result = mysqli_query($db, "UPDATE pollanswers SET Votes = Votes + 1 WHERE ID = $votenum;");
            echo "Thanks for voting!<br /><br />";
        } else {
            exit;
        }
    }
    
    echo "<strong>Results of poll:</strong><br />";

    $questionID = mysqli_real_escape_string($db, $_POST['poll']);
    $result = mysqli_query($db, "SELECT Answer, Votes FROM pollanswers WHERE Question = $questionID ORDER BY Votes DESC;");
    echo "<ul>";
    
    while ($row = mysqli_fetch_assoc($result)) {
        extract($row, EXTR_PREFIX_ALL, 'poll');
        echo "<li>$poll_Answer: $poll_Votes</li>";
    }
    
    echo "</ul>";
?>

Again, this script is largely the same as its predecessor, except note that I have also modified the check whether a vote is valid - it now queries the database for the answer number, using quotes around the variable in case someone entered characters, and check whether there is a matching row. There is also a very minor tweak in the display code - now that we have separate rows for each answer we can order the results display by the number of votes for each option, which is much easier to read.

Go ahead and test out these scripts - try adding your own poll with a different number of answers to make sure it is all working properly. However, we're still not finished - what else can be done to improve our poll?

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