Development: creating the simplest poll

Starting with the SQL, we can create the table containing poll questions simply by fleshing out the schema already shown. As per usual we will need an ID primary key so that we can work with rows easily, but we should also set the default value of the vote counters to 0.

Here is how that looks in SQL:

CREATE TABLE pollquestions (ID INT NOT NULL AUTO_INCREMENT PRIMARY KEY, Question CHAR(255), Answer1 CHAR(255), Answer2 CHAR(255), Answer3 CHAR(255), Answer1_Votes INT DEFAULT 0, Answer2_Votes INT DEFAULT 0, Answer3_Votes INT DEFAULT 0);

To get us started off, here's some SQL to add the first poll question:

INSERT INTO pollquestions (Question, Answer1, Answer2, Answer3) VALUES ('Favourite film?', 'Casablanca', 'Aliens', 'Indiana Jones');

The first page needs to read the latest poll question and its answers, and present it all as an HTML form. Save this next script as poll.php:

<?php
    $db = mysqli_connect("localhost", "phpuser", "alm65z", "phpdb");
    
    $result = mysqli_query($db, "SELECT ID, Question, Answer1, Answer2, Answer3 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 />";
    echo "<input type=\"radio\" name=\"vote\" value=\"1\">$poll_Answer1</option><br />";
    echo "<input type=\"radio\" name=\"vote\" value=\"2\">$poll_Answer2</option><br />";
    echo "<input type=\"radio\" name=\"vote\" value=\"3\">$poll_Answer3</option><br />";
    echo "<input type=\"submit\" value=\"Vote\" />";
    echo "<input type=\"hidden\" name=\"poll\" value=\"$poll_ID\" />";
    echo "</form>";
?>

Note that the SQL query uses "ORDER BY ID DESC LIMIT 1" to pick out the most recently added poll, and that the ACTION attribute of the form is set to point to results.php, which is the second file.

Here is the code for results.php:

<?php
    $db = mysqli_connect("localhost", "phpuser", "alm65z", "phpdb");
    
    if (isset($_POST['vote'])) {
        $votenum = $_POST['vote'];
    
        switch($votenum) {
            case 1: $votechange = "Answer1_Votes"; break;
            case 2: $votechange = "Answer2_Votes"; break;
            case 3: $votechange = "Answer3_Votes"; break;
            default: exit;
        }

        $pollID = mysqli_real_escape_string($db, $_POST['poll']);
    
        $result = mysqli_query($db, "UPDATE pollquestions SET $votechange = $votechange + 1 WHERE ID =
        $pollID;");
        
        if ($result) {
            echo "Thanks for voting!<br /><br />";
        }
    }
    
    echo "<strong>Results of poll:</strong><br />";
    
    $result = mysqli_query($db, "SELECT Answer1, Answer2, Answer3, Answer1_Votes, Answer2_Votes, Answer3_Votes
    FROM pollquestions ORDER BY ID DESC LIMIT 1;");
    extract(mysqli_fetch_assoc($result), EXTR_PREFIX_ALL, 'poll');
    
    echo "<ul>";
    echo "<li>$poll_Answer1: $poll_Answer1_Votes</li>";
    echo "<li>$poll_Answer2: $poll_Answer2_Votes</li>";
    echo "<li>$poll_Answer3: $poll_Answer3_Votes</li>";
    echo "</ul>";
?>

That is quite a long script, but much of it should be fairly straightforward. The switch/case statement block is there to turn the vote numbers into the name of the relevant field in the table. This could have been done by setting the field name as the answer number in poll.php, but that would basically have allowed malicious users to pass any variable into our script for change in the table - not a smart move. The code above validates that a choice of either 1, 2, or 3 has been passed in, as anything else will make the script exit.

Those scripts work fine - go ahead and test them out.

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