本文目录导读：
我来详细介绍PHP问卷表单后端开发的完整流程和最佳实践。
数据库设计
问卷表结构
-- 问卷主表
CREATE TABLE surveys (
id INT PRIMARY KEY AUTO_INCREMENT,VARCHAR(200) NOT NULL,
description TEXT,
status ENUM('draft', 'published', 'closed') DEFAULT 'draft',
start_time DATETIME,
end_time DATETIME,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);
-- 问题表
CREATE TABLE questions (
id INT PRIMARY KEY AUTO_INCREMENT,
survey_id INT NOT NULL,
question_text TEXT NOT NULL,
question_type ENUM('single', 'multiple', 'text', 'rating', 'dropdown') NOT NULL,
is_required BOOLEAN DEFAULT TRUE,
sort_order INT DEFAULT 0,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
FOREIGN KEY (survey_id) REFERENCES surveys(id) ON DELETE CASCADE
);
-- 选项表（针对选择题）
CREATE TABLE options (
id INT PRIMARY KEY AUTO_INCREMENT,
question_id INT NOT NULL,
option_text VARCHAR(500) NOT NULL,
sort_order INT DEFAULT 0,
FOREIGN KEY (question_id) REFERENCES questions(id) ON DELETE CASCADE
);
-- 答卷表
CREATE TABLE responses (
id INT PRIMARY KEY AUTO_INCREMENT,
survey_id INT NOT NULL,
respondent_ip VARCHAR(45),
respondent_ua TEXT,
submitted_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
FOREIGN KEY (survey_id) REFERENCES surveys(id) ON DELETE CASCADE
);
-- 答案表
CREATE TABLE answers (
id INT PRIMARY KEY AUTO_INCREMENT,
response_id INT NOT NULL,
question_id INT NOT NULL,
answer_text TEXT,
option_id INT,
FOREIGN KEY (response_id) REFERENCES responses(id) ON DELETE CASCADE,
FOREIGN KEY (question_id) REFERENCES questions(id) ON DELETE CASCADE,
FOREIGN KEY (option_id) REFERENCES options(id) ON DELETE SET NULL
);
后端核心代码
配置文件和数据库连接
<?php
// config.php
class Database {
private $host = 'localhost';
private $db_name = 'survey_system';
private $username = 'root';
private $password = '';
private $conn;
public function getConnection() {
$this->conn = null;
try {
$this->conn = new PDO(
"mysql:host={$this->host};dbname={$this->db_name};charset=utf8mb4",
$this->username,
$this->password
);
$this->conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$this->conn->setAttribute(PDO::ATTR_DEFAULT_FETCH_MODE, PDO::FETCH_ASSOC);
} catch(PDOException $e) {
echo "Connection error: " . $e->getMessage();
}
return $this->conn;
}
}
?>
获取问卷API
<?php
// api/get_survey.php
require_once '../config.php';
header('Content-Type: application/json; charset=utf-8');
$database = new Database();
$db = $database->getConnection();
$surveyId = isset($_GET['id']) ? intval($_GET['id']) : 0;
try {
// 获取问卷基本信息
$stmt = $db->prepare("SELECT * FROM surveys WHERE id = ?");
$stmt->execute([$surveyId]);
$survey = $stmt->fetch();
if (!$survey) {
http_response_code(404);
echo json_encode(['error' => '问卷不存在']);
exit;
}
// 获取问题和选项
$stmt = $db->prepare("
SELECT q.*, o.id as option_id, o.option_text as option_text
FROM questions q
LEFT JOIN options o ON q.id = o.question_id
WHERE q.survey_id = ?
ORDER BY q.sort_order, o.sort_order
");
$stmt->execute([$surveyId]);
$rows = $stmt->fetchAll();
// 整理数据结构
$questions = [];
foreach ($rows as $row) {
$questionId = $row['id'];
if (!isset($questions[$questionId])) {
$questions[$questionId] = [
'id' => $questionId,
'question_text' => $row['question_text'],
'question_type' => $row['question_type'],
'is_required' => $row['is_required'],
'options' => []
];
}
if ($row['option_id']) {
$questions[$questionId]['options'][] = [
'id' => $row['option_id'],
'text' => $row['option_text']
];
}
}
$survey['questions'] = array_values($questions);
echo json_encode([
'success' => true,
'data' => $survey
]);
} catch (PDOException $e) {
http_response_code(500);
echo json_encode(['error' => '服务器错误']);
}
?>
提交答卷API
<?php
// api/submit_response.php
require_once '../config.php';
header('Content-Type: application/json; charset=utf-8');
// 验证请求方法
if ($_SERVER['REQUEST_METHOD'] !== 'POST') {
http_response_code(405);
echo json_encode(['error' => '仅支持POST请求']);
exit;
}
// 获取POST数据
$input = json_decode(file_get_contents('php://input'), true);
if (!$input || !isset($input['survey_id']) || !isset($input['answers'])) {
http_response_code(400);
echo json_encode(['error' => '缺少必要参数']);
exit;
}
$database = new Database();
$db = $database->getConnection();
try {
$db->beginTransaction();
// 1. 验证问卷是否存在
$stmt = $db->prepare("SELECT id FROM surveys WHERE id = ? AND status = 'published'");
$stmt->execute([$input['survey_id']]);
$survey = $stmt->fetch();
if (!$survey) {
http_response_code(404);
echo json_encode(['error' => '问卷不存在或已关闭']);
exit;
}
// 2. 创建答卷记录
$stmt = $db->prepare("INSERT INTO responses (survey_id, respondent_ip, respondent_ua) VALUES (?, ?, ?)");
$stmt->execute([
$input['survey_id'],
$_SERVER['REMOTE_ADDR'],
$_SERVER['HTTP_USER_AGENT']
]);
$responseId = $db->lastInsertId();
// 3. 验证和处理答案
foreach ($input['answers'] as $answer) {
// 验证问题存在
$stmt = $db->prepare("
SELECT q.*, s.id as survey_id
FROM questions q
JOIN surveys s ON q.survey_id = s.id
WHERE q.id = ? AND q.survey_id = ?
");
$stmt->execute([$answer['question_id'], $input['survey_id']]);
$question = $stmt->fetch();
if (!$question) {
throw new Exception("问题ID {$answer['question_id']} 不存在");
}
// 验证必填项
if ($question['is_required'] && empty($answer['value'])) {
throw new Exception("问题 '{$question['question_text']}' 是必填项");
}
// 根据题型处理
switch ($question['question_type']) {
case 'single':
// 单选：验证选项是否存在
$stmt = $db->prepare("SELECT id FROM options WHERE id = ? AND question_id = ?");
$stmt->execute([$answer['value'], $answer['question_id']]);
if (!$stmt->fetch()) {
throw new Exception("无效的选项");
}
$stmt = $db->prepare("INSERT INTO answers (response_id, question_id, option_id) VALUES (?, ?, ?)");
$stmt->execute([$responseId, $answer['question_id'], $answer['value']]);
break;
case 'multiple':
// 多选：验证每个选项
if (is_array($answer['value'])) {
foreach ($answer['value'] as $optionId) {
$stmt = $db->prepare("SELECT id FROM options WHERE id = ? AND question_id = ?");
$stmt->execute([$optionId, $answer['question_id']]);
if (!$stmt->fetch()) {
throw new Exception("无效的选项");
}
$stmt = $db->prepare("INSERT INTO answers (response_id, question_id, option_id) VALUES (?, ?, ?)");
$stmt->execute([$responseId, $answer['question_id'], $optionId]);
}
}
break;
case 'text':
// 文本：直接保存
$stmt = $db->prepare("INSERT INTO answers (response_id, question_id, answer_text) VALUES (?, ?, ?)");
$stmt->execute([$responseId, $answer['question_id'], $answer['value']]);
break;
case 'rating':
// 评分：验证范围
$rating = intval($answer['value']);
if ($rating < 1 || $rating > 5) {
throw new Exception("评分范围必须在1-5之间");
}
$stmt = $db->prepare("INSERT INTO answers (response_id, question_id, answer_text) VALUES (?, ?, ?)");
$stmt->execute([$responseId, $answer['question_id'], $rating]);
break;
case 'dropdown':
// 下拉选择：验证选项
$stmt = $db->prepare("SELECT id FROM options WHERE id = ? AND question_id = ?");
$stmt->execute([$answer['value'], $answer['question_id']]);
if (!$stmt->fetch()) {
throw new Exception("无效的选项");
}
$stmt = $db->prepare("INSERT INTO answers (response_id, question_id, option_id) VALUES (?, ?, ?)");
$stmt->execute([$responseId, $answer['question_id'], $answer['value']]);
break;
default:
throw new Exception("不支持的问题类型");
}
}
$db->commit();
echo json_encode([
'success' => true,
'message' => '提交成功',
'response_id' => $responseId
]);
} catch (Exception $e) {
$db->rollBack();
http_response_code(400);
echo json_encode(['error' => $e->getMessage()]);
}
?>
安全措施
CSRF保护（简化版）
<?php
// secure/csrf.php
session_start();
class CSRF {
public static function generateToken() {
if (empty($_SESSION['csrf_token'])) {
$_SESSION['csrf_token'] = bin2hex(random_bytes(32));
}
return $_SESSION['csrf_token'];
}
public static function verifyToken($token) {
if (!isset($_SESSION['csrf_token'])) {
return false;
}
return hash_equals($_SESSION['csrf_token'], $token);
}
}
?>
XSS过滤
<?php
// secure/sanitize.php
class Sanitizer {
public static function sanitize($input) {
if (is_array($input)) {
return array_map([self::class, 'sanitize'], $input);
}
return htmlspecialchars(trim($input), ENT_QUOTES, 'UTF-8');
}
}
?>
前端验证示例
// submit_survey.js
async function submitSurvey(surveyId) {
const form = document.querySelector('#surveyForm');
const formData = new FormData(form);
// 收集答案
const answers = [];
form.querySelectorAll('[data-question-id]').forEach(question => {
const questionId = question.dataset.questionId;
const type = question.dataset.type;
switch(type) {
case 'single':
const selected = question.querySelector('input[type="radio"]:checked');
if (selected) {
answers.push({
question_id: questionId,
value: selected.value
});
}
break;
case 'multiple':
const checked = question.querySelectorAll('input[type="checkbox"]:checked');
if (checked.length > 0) {
answers.push({
question_id: questionId,
value: Array.from(checked).map(cb => cb.value)
});
}
break;
case 'text':
const textarea = question.querySelector('textarea');
if (textarea.value.trim()) {
answers.push({
question_id: questionId,
value: textarea.value.trim()
});
}
break;
case 'rating':
const rating = question.querySelector('input[type="range"]');
if (rating) {
answers.push({
question_id: questionId,
value: rating.value
});
}
break;
case 'dropdown':
const select = question.querySelector('select');
if (select.value) {
answers.push({
question_id: questionId,
value: select.value
});
}
break;
}
});
// 提交数据
try {
const csrfToken = document.querySelector('meta[name="csrf-token"]').content;
const response = await fetch('/api/submit_response.php', {
method: 'POST',
headers: {
'Content-Type': 'application/json',
'X-CSRF-Token': csrfToken
},
body: JSON.stringify({
survey_id: surveyId,
answers: answers
})
});
const result = await response.json();
if (result.success) {
alert('提交成功！');
window.location.href = '/thank-you.html';
} else {
alert('提交失败: ' + result.error);
}
} catch (error) {
console.error('Error:', error);
alert('提交失败，请稍后重试');
}
}
数据分析示例
<?php
// api/survey_analysis.php
class SurveyAnalysis {
private $db;
public function __construct($db) {
$this->db = $db;
}
public function getQuestionStats($surveyId, $questionId) {
$stats = [];
// 获取问题类型
$stmt = $this->db->prepare("SELECT question_type FROM questions WHERE id = ?");
$stmt->execute([$questionId]);
$question = $stmt->fetch();
switch($question['question_type']) {
case 'single':
case 'dropdown':
$stmt = $this->db->prepare("
SELECT o.option_text, COUNT(a.id) as count
FROM options o
LEFT JOIN answers a ON o.id = a.option_id
WHERE o.question_id = ?
GROUP BY o.id, o.option_text
ORDER BY count DESC
");
$stmt->execute([$questionId]);
$stats = $stmt->fetchAll();
break;
case 'text':
$stmt = $this->db->prepare("
SELECT answer_text, COUNT(*) as count
FROM answers
WHERE question_id = ?
GROUP BY answer_text
ORDER BY count DESC
LIMIT 20
");
$stmt->execute([$questionId]);
$stats = $stmt->fetchAll();
break;
case 'rating':
$stmt = $this->db->prepare("
SELECT answer_text, COUNT(*) as count
FROM answers
WHERE question_id = ?
GROUP BY answer_text
ORDER BY answer_text
");
$stmt->execute([$questionId]);
$stats = $stmt->fetchAll();
// 计算平均值
$avgStmt = $this->db->prepare("
SELECT AVG(CAST(answer_text AS DECIMAL)) as average
FROM answers
WHERE question_id = ?
");
$avgStmt->execute([$questionId]);
$stats['average'] = $avgStmt->fetch()['average'];
break;
}
return $stats;
}
}
?>
部署建议
- 配置环境变量：敏感信息不要硬编码
- 使用HTTPS：确保数据传输加密
- 设置CORS：如果需要跨域访问
- 限制提交频率：防止恶意刷票
- 备份数据：定期备份数据库
- 错误日志：记录错误信息到日志文件
这个完整的后端解决方案涵盖了问卷系统的核心功能,包括数据验证、安全防护和数据分析。
