PHP 投票システムの設計原則と実践

CREATE TABLE `user` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `username` varchar(255) NOT NULL,
  `password` varchar(255) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `vote` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `title` varchar(255) NOT NULL,
  `created_at` datetime NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `option` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `content` varchar(255) NOT NULL,
  `vote_id` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  FOREIGN KEY (`vote_id`) REFERENCES `vote`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `record` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) NOT NULL,
  `vote_id` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  FOREIGN KEY (`user_id`) REFERENCES `user`(`id`) ON DELETE CASCADE,
  FOREIGN KEY (`vote_id`) REFERENCES `vote`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

// 登录验证
session_start();
if (!isset($_SESSION['user'])) {
    header('Location: login.php');
    exit;
}

// 投票操作
if ($_SERVER['REQUEST_METHOD'] === 'POST') {
    $userId = $_SESSION['user']['id'];
    $voteId = $_POST['voteId'];
    $optionId = $_POST['optionId'];

    // 检查是否已经投过票
    $record = $db->query("SELECT * FROM record WHERE user_id = $userId AND vote_id = $voteId")->fetch(PDO::FETCH_ASSOC);
    if (!$record) {
        // 插入投票记录
        $db->query("INSERT INTO record (user_id, vote_id) VALUES ($userId, $voteId)");
        // 更新选项的票数
        $db->query("UPDATE `option` SET count = count + 1 WHERE id = $optionId");
        echo '投票成功！';
    } else {
        echo '您已经投过票了！';
    }
}

// 前端代码
$('.vote-btn').click(function() {
    var voteId = $(this).data('vote-id');
    var optionId = $(this).data('option-id');
    $.ajax({
        url: 'vote.php',
        type: 'POST',
        dataType: 'json',
        data: {voteId: voteId, optionId: optionId},
        success: function(data) {
            alert(data.message);
            // 刷新页面
            location.reload();
        }
    });
});

// 后端代码（vote.php）
// 处理投票逻辑
// ...
$response = ['message' => '投票成功！'];
echo json_encode($response);