3 Steps Simple Poll With PHP MYSQL (Free Download)

Welcome to a tutorial on how to create a simple poll system in PHP and MYSQL. So you are interested in getting the opinions of your visitors, maybe tickle their brains a little. So here it is, let us walk through an example of a polling system in this guide – Read on!

 

 

TABLE OF CONTENTS

 

 

EXAMPLE CODE DOWNLOAD

Source code on GitHub Gist

Just click on “download zip” or do a git clone. I have released it under the MIT license, so feel free to build on top of it or use it in your own project.

 

SORRY FOR THE ADS...

But someone has to pay the bills, and sponsors are paying for it. I insist on not turning Code Boxx into a "paid scripts" business, and I don't "block people with Adblock". Every little bit of support helps.

Buy Me A Coffee Code Boxx eBooks

 

 

 

STEP 1) POLL DATABASE TABLES

1A) POLL QUESTIONS

-- (A) POLL QUESTIONS
CREATE TABLE `poll_questions` (
  `poll_id` bigint(20) NOT NULL,
  `poll_question` text NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

ALTER TABLE `poll_questions`
  ADD PRIMARY KEY (`poll_id`);

ALTER TABLE `poll_questions`
  MODIFY `poll_id` bigint(20) NOT NULL AUTO_INCREMENT;

Field	Description
poll_id	Primary key, poll ID.
poll_question	The poll question.

 

 

1B) POLL OPTIONS & VOTES

-- (B) POLL OPTIONS
CREATE TABLE `poll_options` (
  `poll_id` bigint(20) NOT NULL,
  `option_id` bigint(20) NOT NULL,
  `option_text` varchar(255) NOT NULL,
  `option_votes` bigint(20) NOT NULL DEFAULT 0
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
 
ALTER TABLE `poll_options`
  ADD PRIMARY KEY (`poll_id`,`option_id`);

Field	Description
poll_id	Composite primary key and foreign key.
option_id	Composite primary key.
option_text	Poll option.
option_votes	The number of votes.

 

1C) DUMMY EXAMPLE DATA

-- (C) DUMMY DATA
INSERT INTO `poll_questions` (`poll_id`, `poll_question`) VALUES
  (1, 'What is your favorite meme animal?');
 
INSERT INTO `poll_options` (`poll_id`, `option_id`, `option_text`, `option_votes`) VALUES
  (1, 1, 'Doge', 99),
  (1, 2, 'Cate', 88),
  (1, 3, 'Birb', 77);

Some people may still struggle with the database structure, so here’s a quick and simple example.

 

 

STEP 2) PHP POLL LIBRARY

<?php
class Poll {
  // (A) CONSTRUCTOR - CONNECT TO DATABASE
  private $pdo = null;
  private $stmt = null;
  public $error = "";
  function __construct () {
    $this->pdo = new PDO(
      "mysql:host=".DB_HOST.";dbname=".DB_NAME.";charset=".DB_CHARSET,
      DB_USER, DB_PASSWORD, [
      PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
      PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC
    ]);
  }

  // (B) DESTRUCTOR - CLOSE DATABASE CONNECTION
  function __destruct () {
    if ($this->stmt!==null) { $this->stmt = null; }
    if ($this->pdo!==null) { $this->pdo = null; }
  }
 
  // (C) EXECUTE SQL QUERY
  function query ($sql, $data=null) : void {
    $this->stmt = $this->pdo->prepare($sql);
    $this->stmt->execute($data);
  }
 
  // (D) GET POLL & VOTES
  function get ($pid) {
    // (D1) GET POLL QUESTION
    $this->query("SELECT `poll_question` FROM `poll_questions` WHERE `poll_id`=?", [$pid]);
    $question = $this->stmt->fetchColumn();
    if ($question == false) { return false; }
 
    // (D2) GET POLL OPTIONS & VOTES
    $this->query("SELECT * FROM `poll_options` WHERE `poll_id`=?", [$pid]);
    $options = [];
    while ($row = $this->stmt->fetch()) {
      $options[$row["option_id"]] = [
        "t" => $row["option_text"],
        "v" => $row["option_votes"]
      ];
    }
 
    // (D3) RESULT
    return ["q"=>$question, "o"=>$options];
  }
 
  // (E) SAVE VOTE
  function save ($pid, $up=null, $down=null) {
    // (E1) GET CURRENT VOTES
    $votes = $this->get($pid);
    if ($votes==false) {
      $this->error = "Invalid poll";
      return false;
    }
 
    // (E2) UPDATE POLL
    $sql = "UPDATE `poll_options` SET `option_votes`=? WHERE `poll_id`=? AND `option_id`=?";
    if (is_numeric($up)) {
      $this->query($sql, [$votes["o"][$up]["v"]+1, $pid, $up]);
    }
    if (is_numeric($down)) {
      $this->query($sql, [$votes["o"][$down]["v"]-1, $pid, $down]);
    }
    return true;
  }
}
 
// (F) DATABASE SETTINGS - CHANGE TO YOUR OWN!
define("DB_HOST", "localhost");
define("DB_NAME", "test");
define("DB_CHARSET", "utf8mb4");
define("DB_USER", "root");
define("DB_PASSWORD", "");

// (G) START SESSION & NEW POLL OBJECT
session_start();
$POLL = new Poll();

Right. This script looks massive at first, but keep calm and look carefully.

• (A, B, G) When$POLL = new Poll() is created, the constructor will automatically connect to the database. The destructor will close the connection.
• (C) query() is a helper function to run an SQL query.
• (D & E) There are only 2 functions here!
  • get() Get the question, options, and votes for the given poll ID. In the format of ["q"=>QUESTION, "o"=>OPTION ID=>["t"=>TEXT, "v"=>VOTES]]
  • save() Save and update the vote count. Very easy, just specify the poll ID, which option ID to increment, and which option ID to decrement.
• (F) Remember to change the settings to your own.

 

 

STEP 3) HTML POLL PAGE

<?php
// (A) INIT
$pid = 1; // FIXED TO POLL ID 1 FOR THIS DEMO
require "2-lib-poll.php"; // LOAD POLL LIBRARY
if (!isset($_SESSION["poll"])) { $_SESSION["poll"] = []; } // TO TRACK USER VOTE
if (!isset($_SESSION["poll"][$pid])) { $_SESSION["poll"][$pid] = null; }
 
// (B) UPDATE POLL VOTE
if (count($_POST)!=0) { if ($_POST["vote"] != $_SESSION["poll"][$pid]) {
  $up = $_POST["vote"];
  $down = $_SESSION["poll"][$pid]!=$up ? $_SESSION["poll"][$pid] : null ;
  if ($POLL->save($pid, $up, $down)===false) { echo $POLL->error; }
  $_SESSION["poll"][$pid] = $up;
}}
 
// (C) GET & SHOW THE POLL
$poll = $POLL->get($pid); ?>
<form method="post" class="poll-docket">
  <div class="poll-question"><?=$poll["q"]?></div>
  <?php foreach ($poll["o"] as $oid=>$o) { ?>
  <label class="poll-option">
    <input type="radio" name="vote" value="<?=$oid?>"
    <?=$oid==$_SESSION["poll"][$pid]?" checked":""?>>
    <span class="poll-text"><?=$o["t"]?></span>
    <span class="poll-votes">(<?=$o["v"]?>)</span>
  </label>
  <?php } ?>
  <input type="submit" class="poll-go" value="Go!">
</form>

This can still be a little confusing, so follow up:

1. Load the poll library and define the poll ID. Notice how we use the session to track the previous vote here – $_SESSION["poll"][POLL ID] = OPTION ID.
2. When the poll form is submitted, we use $POLL->save() to do an update.
3. Just using $POLL->get() to generate the HTML poll itself.

 

 

 

POSSIBLE SPAM WARNING

Yep, this simple poll system does not require users to be registered to poll – We are using $_SESSION to track the user. This can be a problem if the user does not have cookies enabled, and quite possibly invite spam too. You decide if you want a public poll.

 

WHERE ARE THE ADMIN FUNCTIONS!?

There’s none to keep this tutorial simple. Feel free to expand on the library yourself, and build your own admin panel. If not, just use PHPMyAdmin or MySQL Workbench to directly manage the database.

 

LINKS & REFERENCES

• Simple User Registration Form In PHP MYSQL – Code Boxx
• Simple AJAX User Login Page With PHP MySQL – Code Boxx
• Simple PHP Admin Panel – Code Boxx