Ter inspiratie.
SQL
CREATE TABLE poll_polls (
pol_id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
pol_active BOOL NOT NULL DEFAULT 0,
pol_name VARCHAR(255) NOT NULL,
pol_description TEXT NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE TABLE poll_options (
pop_id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
pop_poll_id INT UNSIGNED NOT NULL,
pop_description VARCHAR(255) NOT NULL,
pop_order INT UNSIGNED NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
ALTER TABLE poll_options ADD FOREIGN KEY (pop_poll_id) REFERENCES poll_polls (pol_id) ON DELETE CASCADE;
CREATE TABLE poll_votes (
pvo_poll_id INT UNSIGNED NOT NULL,
pvo_option_id INT UNSIGNED NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
ALTER TABLE poll_votes ADD FOREIGN KEY (pvo_poll_id) REFERENCES poll_polls (pol_id) ON DELETE CASCADE;
ALTER TABLE poll_votes ADD FOREIGN KEY (pvo_option_id) REFERENCES poll_options (pop_id) ON DELETE CASCADE;
CREATE TABLE poll_participants (
ppa_id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
ppa_poll_id INT UNSIGNED NOT NULL,
ppa_email VARCHAR(255) NOT NULL,
ppa_token VARCHAR(255) NOT NULL,
ppa_voted BOOL NOT NULL DEFAULT 0
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
ALTER TABLE poll_participants ADD FOREIGN KEY (ppa_poll_id) REFERENCES poll_polls (pol_id) ON DELETE CASCADE;
test data
insert into poll_polls (pol_active, pol_name, pol_description) values (1, 'Test poll', 'This is a test poll. To be able to cast votes you need to create records in the poll_participants table with generated tokens and then distribute these amongst the participants.');
insert into poll_options (pop_poll_id, pop_description, pop_order) values (1, 'the first option', 1), (1, 'the second', 2), (1, 'and the third', 3);
insert into poll_participants (ppa_poll_id, ppa_email, ppa_token, ppa_voted) values
(1, '[email protected]', '46108d84a11db44b2a95190a2d0dba1570a7321b', 0),
(1, '[email protected]', '60671281ffcb0bf0e6493fed8c2a12965e84610c', 0),
(1, '[email protected]', '07ad975599881d2f61b7b55a290772983f444a0a', 0);
insert into poll_polls (pol_active, pol_name, pol_description) values (1, 'Another test poll', 'This is another test poll.');
insert into poll_options (pop_poll_id, pop_description, pop_order) values (2, 'apples', 1), (2, 'oranges', 2);
insert into poll_participants (ppa_poll_id, ppa_email, ppa_token, ppa_voted) values
(2, '[email protected]', '400f2f57f30c7eb18ddf770d7644d403cfe94748', 0),
(2, '[email protected]', '0c27e4a6598c8b870c3d88aa37e8e2e6ebb9e538', 0);
/pagetype/poll.php
<?php
require_once './pagetype.php';
class Pagetype_Poll extends Pagetype
{
protected function init() {
// define the actions that do not produce output
if (in_array($this->action, array(
'voteProcess',
))) {
$this->hasOutput = false;
}
}
protected function actionDefault() {
$this->title = 'Poll overview';
?><div class="wrapper">
<h1>Poll overview</h1><?php
$res = $this->db->query(
'SELECT pol_id AS id, pol_name AS name, pol_description AS description
FROM poll_polls
WHERE pol_active = 1
ORDER BY pol_id DESC'
);
if ($res->num_rows > 0) {
while ($pollData = $res->fetch_object()) {
?><h2><?php echo $this->escape($pollData->name); ?></h2>
<p><?php echo $this->escape($pollData->description); ?></p>
<p><a href="?action=show&poll=<?php echo $this->escape($pollData->id); ?>">> show details</a><?php
}
} else {
?><p>There currently are no active polls.</p><?php
}
$res->free();
?></div><?php
} // actionDefault
protected function actionShow() {
if (isset($_GET['poll']) && Helper::isIndex($_GET['poll'])) {
$pollId = $_GET['poll'];
} else {
throw new Exception('Poll.show: required poll id not found or not valid');
}
$this->title = 'Poll details';
?><div class="wrapper">
<h1>Poll details</h1><?php
// get all the poll data
$res = $this->db->query(
"SELECT pol_active AS active, pol_name AS name, pol_description AS description
FROM poll_polls
WHERE pol_id = '".$this->db->real_escape_string($pollId)."'"
);
if ($res->num_rows == 1) {
$pollData = $res->fetch_object();
if ($pollData->active == 1) {
?><h2><?php echo $this->escape($pollData->name); ?></h2>
<p><?php echo $this->escape($pollData->description); ?></p><?php
// vote data
?><h2>Votes</h2><?php
$res2 = $this->db->query(
"SELECT pop_description AS description, COUNT(pvo_option_id) AS votes
FROM poll_options
LEFT JOIN poll_votes ON (pvo_option_id = pop_id)
WHERE pop_poll_id = '".$this->db->real_escape_string($pollId)."'
GROUP BY pop_id
ORDER BY pop_order"
);
?><table border="1">
<thead>
<tr>
<th>option</th>
<th>votes</th>
</tr>
</thead>
<tbody><?php
while ($optionData = $res2->fetch_object()) {
?><tr>
<td><?php echo $this->escape($optionData->description); ?></td>
<td align="right"><?php echo $this->escape($optionData->votes); ?></td>
</tr><?php
}
?></tbody>
</table>
<p>If you have received a voting token, copypaste it here to cast a vote:</p>
<form action="<?php echo $this->escape($_SERVER['SCRIPT_NAME']) ?>" method="get" accept-charset="UTF-8">
<input type="hidden" name="action" value="vote">
<input type="hidden" name="poll" value="<?php echo $this->escape($pollId); ?>">
<input type="text" name="token" size="50" autocomplete="off"> <button type="submit">to the voting page</button>
</form><?php
} else {
// inactive poll
?><h2>Poll inactive</h2>
<p>The poll you are trying to look up is no longer active.</p><?php
}
} else {
// non existing poll
?><h2>Poll not found</h2>
<p>The poll you are trying to look up does not exist (anymore).</p><?php
}
$res->free();
?><p><a href="<?php echo $this->escape($_SERVER['SCRIPT_NAME']) ?>">< back to overview</a></p>
</div><?php
} // actionShow
protected function actionVote() {
// this action requires at least a valid (correctly formatted) poll id
if (isset($_GET['poll']) && Helper::isIndex($_GET['poll'])) {
$pollId = $_GET['poll'];
} else {
throw new Exception('Poll.vote: required id not found or not valid');
}
// check token - later on you could enforce more strict checking, for example with a function like Helper::checkToken($token)
$votingToken = false;
if (isset($_GET['token'])) {
$votingToken = $_GET['token'];
}
$this->title = 'Poll vote';
?><div class="wrapper">
<h1>Poll vote</h1><?php
if (isset($_GET['errors'])) {
?><div class="error">Something went wrong. Please review and resubmit your data.</div><?php
}
// check if poll exists
$res = $this->db->query(
"SELECT pol_active AS active, pol_name AS name, pol_description AS description
FROM poll_polls
WHERE pol_id = '".$this->db->real_escape_string($pollId)."'"
);
if ($res->num_rows == 1) {
$pollData = $res->fetch_object();
if ($pollData->active == 1) {
// check if token is valid
$res2 = $this->db->query(
"SELECT ppa_id AS id, ppa_voted AS voted
FROM poll_participants
WHERE ppa_poll_id = '".$this->db->real_escape_string($pollId)."'
AND ppa_token = '".$this->db->real_escape_string($votingToken)."'"
);
if ($res2->num_rows == 1) {
$tokenData = $res2->fetch_object();
if ($tokenData->voted == 1) {
?><h2>Invalid token</h2>
<p>The token you supplied has already been used for voting.</p><?php
} else {
// we finally made it to the part where you can actually vote - whee~
$res3 = $this->db->query(
"SELECT pop_id AS id, pop_description AS description
FROM poll_options
WHERE pop_poll_id = '".$this->db->real_escape_string($pollId)."'
ORDER BY pop_order"
);
?><h2><?php echo $this->escape($pollData->name); ?></h2>
<p><?php echo $this->escape($pollData->description); ?></p>
<p>Submit your vote with the form below:</p>
<form action="<?php echo $this->escape($_SERVER['SCRIPT_NAME']); ?>?action=voteProcess" method="POST" onsubmit="return confirm('Since this is an anonymous vote, this vote cannot be undone.\nAre you sure?');">
<input type="hidden" name="poll" value="<?php echo $this->escape($pollId); ?>">
<input type="hidden" name="token" value="<?php echo $this->escape($votingToken); ?>">
<table border="1">
<tbody><?php
while ($optionData = $res3->fetch_object()) {
$fieldId = 'vote_'.$optionData->id;
?><tr>
<td>
<input type="radio" name="vote" id="<?php echo $this->escape($fieldId); ?>" value="<?php echo $this->escape($optionData->id); ?>">
<label for="<?php echo $this->escape($fieldId); ?>" style="padding-right: 50px;"><?php echo $this->escape($optionData->description); ?></label>
</td>
</tr><?php
}
?></tbody>
</table>
<p><button type="submit">Cast vote</button></p>
</form><?php
$res3->free();
}
} else {
// invalid token
?><h2>Invalid token</h2>
<p>The token you supplied is not valid.</p><?php
}
$res2->free();
} else {
// inactive poll
?><h2>Poll inactive</h2>
<p>The poll you are trying to look up is no longer active.</p><?php
}
}
$res->free();
?><p>
<a href="<?php echo $this->escape($_SERVER['SCRIPT_NAME']) ?>?action=show&poll=<?php echo $this->escape($pollId); ?>">< back to poll</a><br>
<a href="<?php echo $this->escape($_SERVER['SCRIPT_NAME']) ?>">< back to overview</a>
</p>
</div><?php
} // actionVote
protected function actionVoteProcess() {
if ($_SERVER['REQUEST_METHOD'] == 'POST') {
// Helper::dump($_POST);
// exit;
// some checks
if (isset($_POST['poll']) && Helper::isIndex($_POST['poll'])) {
$pollId = $_POST['poll'];
} else {
throw new Exception('Poll.voteProcess: missing poll id');
}
if (isset($_POST['token'])) {
$votingToken = $_POST['token'];
} else {
throw new Exception('Poll.voteProcess: missing token');
}
if (isset($_POST['vote']) && Helper::isIndex($_POST['vote'])) {
$voteId = $_POST['vote'];
} else {
// oh dear, someone forgot to vote - if we got this far, just send them back to the form...
$this->redirect($_SERVER['SCRIPT_NAME'].'?action=vote&poll='.$pollId.'&token='.$votingToken.'&errors=1');
}
// now we need to do some final checks during which nothing may be modified
// this is why we need transactions: to guarantee that all these checks are performed as one atomic (undivisible) operation
$this->db->autocommit(false); // effectively starts a transaction
// first check if the poll is active
$res = $this->db->query(
"SELECT pol_id
FROM poll_polls
WHERE pol_id = '".$this->db->real_escape_string($pollId)."'
AND pol_active = 1
FOR UPDATE"
);
if ($res->num_rows == 1) {
// next check if the token is active
$res2 = $this->db->query(
"SELECT ppa_id
FROM poll_participants
WHERE ppa_poll_id = '".$this->db->real_escape_string($pollId)."'
AND ppa_token = '".$this->db->real_escape_string($votingToken)."'
AND ppa_voted = 0
FOR UPDATE"
);
if ($res2->num_rows == 1) {
// finally check if the vote was valid
$res3 = $this->db->query(
"SELECT pop_id
FROM poll_options
WHERE pop_id = '".$this->db->real_escape_string($voteId)."'
AND pop_poll_id = '".$this->db->real_escape_string($pollId)."'
FOR UPDATE"
);
if ($res3->num_rows == 1) {
// the stars align - cast the vote and update the participants table
$this->db->query(
"INSERT INTO poll_votes (
pvo_poll_id,
pvo_option_id
) VALUES (
'".$this->db->real_escape_string($pollId)."',
'".$this->db->real_escape_string($voteId)."'
)"
);
$this->db->query(
"UPDATE poll_participants
SET ppa_voted = 1
WHERE ppa_poll_id = '".$this->db->real_escape_string($pollId)."'
AND ppa_token = '".$this->db->real_escape_string($votingToken)."'"
);
} else {
// invalid vote - we are done
$this->db->rollback();
throw new Exception('Poll.voteProcess: invalid option');
}
} else {
// not an active token - we are done
$this->db->rollback();
throw new Exception('Poll.voteProcess: inactive or invalid token');
}
} else {
// not an active poll - we are done
$this->db->rollback();
throw new Exception('Poll.voteProcess: inactive poll');
}
$this->db->autocommit(true); // commit transaction
$this->redirect($_SERVER['SCRIPT_NAME'].'?action=voteComplete&poll='.$pollId);
}
} // actionVoteProcess
protected function actionVoteComplete() {
$pollId = false;
if (isset($_GET['poll']) && Helper::isIndex($_GET['poll'])) {
$pollId = $_GET['poll'];
}
$this->title = 'Vote cast!';
?><div class="wrapper">
<h1>Vote cast!</h1>
<p>Your vote has been successfully cast.</p>
<p>
<a href="<?php echo $this->escape($_SERVER['SCRIPT_NAME']) ?>?action=show&poll=<?php echo $this->escape($pollId); ?>">< back to poll</a><br>
<a href="<?php echo $this->escape($_SERVER['SCRIPT_NAME']) ?>">< back to overview</a>
</p>
</div><?php
} // actionVoteComplete
protected function actionGenerateTokens() {
?><pre><?php
for ($i=0; $i < 10; $i++) {
echo Helper::getRandomToken()."\n";
}
?></pre><?php
} // actionGenerateTokens
} // Pagetype_Poll
?>
/debug.php
<?php
$development = true; // true for development, false for production
if ($development) {
// write errors directly to output
error_reporting(E_ALL);
ini_set('display_startup_errors', true);
ini_set('display_errors', 'stdout');
// log_errors: whatever is default
// error_log: whatever is default
} else {
// write errors to separate logfile
error_reporting(E_ALL);
ini_set('display_startup_errors', false);
ini_set('display_errors', false);
ini_set('log_errors', true);
ini_set('error_log', '/absolute/path/to/poll.log');
}
?>
/helper.php
<?php
class Helper
{
public static function escape($in) {
return htmlspecialchars($in, ENT_QUOTES, 'UTF-8');
}
public static function dump($in) {
if (is_array($in)) {
$in = print_r($in, true);
}
echo '<pre>'.self::escape($in).'</pre>';
}
public static function isIndex($in) {
return preg_match('#^[1-9][0-9]*$#', $in) == 1;
}
public static function getRandomToken() {
if (function_exists('openssl_random_pseudo_bytes')) {
return bin2hex(openssl_random_pseudo_bytes(20, $strong));
} else {
return sha1(uniqid(mt_rand(), true));
}
}
}
?>
/index.php
<?php
require_once './debug.php';
require_once './helper.php';
require_once './pagetype/poll.php';
try {
$poll = new Pagetype_Poll();
$poll->execute();
} catch (Exception $e) {
echo 'Exception: '.$e->getMessage();
// echo '<br><pre>'.$e->getTraceAsString().'</pre>'; // extra info
}
?>
/pagetype.php
<?php
abstract class PageType
{
protected $action;
protected $hasOutput;
protected $title;
protected $db;
public function __construct() {
$this->action = 'default';
if (isset($_GET['action']) && method_exists($this, 'action'.$_GET['action'])) {
$this->action = $_GET['action'];
}
$this->hasOutput = true;
$this->title = '';
$this->db = new mysqli('127.0.0.1', 'test', 'test', 'test');
$this->db->set_charset('utf8');
}
protected function init() {}
public function execute() {
$this->init();
$action = 'action'.$this->action;
if ($this->hasOutput) {
ob_start();
$this->$action();
$contents = ob_get_clean();
$this->__printHeader();
echo $contents;
$this->__printFooter();
} else {
$this->$action();
}
}
abstract protected function actionDefault();
protected function __printHeader() {
?><!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title><?php echo $this->escape($this->title); ?></title>
<style type="text/css">
div.wrapper { font-family: sans-serif; }
div.error { width: 500px; background-color: #ffcccc; padding: 10px; margin: 10px 0px; text-align: center; }
</style>
</head><?php
}
protected function __printFooter() {
?><body>
</body>
</html><?php
}
// shorthand
protected function escape($in) {
return Helper::escape($in);
}
public function redirect($link) {
header($_SERVER['SERVER_PROTOCOL'].' 303 See Other');
header('Location: '.$link);
exit;
}
}
?>
Uiteraard even de database credentials in /pagetype.php aanpassen, alsmede het pad naar je logfile in /debug.php.
