PHP与SQL结合：实现基于数据库日期功能的用户操作频率限制

SELECT DATEDIFF(CURDATE(), name_changed) AS days_since_last_change
FROM user_db
WHERE email = 'user@example.com';

<form action="change_name.php" method="POST">
    <label for="new_name">新姓名:</label>
    <input type="text" id="new_name" name="name" required minlength="2" maxlength="20">
    <button type="submit" name="change-name">修改姓名</button>
</form>

<?php
session_start(); // 确保会话已启动

// 数据库连接配置 (请根据实际情况修改)
define('DB_SERVER', 'localhost');
define('DB_USERNAME', 'your_db_user');
define('DB_PASSWORD', 'your_db_password');
define('DB_NAME', 'your_database');

// 使用MySQLi或PDO进行数据库连接，这里以MySQLi为例
$con = mysqli_connect(DB_SERVER, DB_USERNAME, DB_PASSWORD, DB_NAME);

// 检查连接
if ($con === false) {
    die("ERROR: Could not connect. " . mysqli_connect_error());
}

$errors = []; // 存储错误信息

if (isset($_POST['change-name']) && !empty($_POST['name'])) {
    $newName = trim($_POST['name']);
    $userEmail = $_SESSION['email'] ?? null; // 从会话中获取用户邮箱

    // 1. 基本输入验证
    if (strlen($newName) < 2 || strlen($newName) > 20) {
        $errors[] = "姓名长度必须在2到20个字符之间。";
    }

    if (empty($userEmail)) {
        $errors[] = "用户未登录或会话已过期。";
    }

    if (empty($errors)) {
        // 2. 查询上次修改日期和距今天数
        $sqlSelect = "SELECT name_changed FROM user_db WHERE email = ?";
        if ($stmt = mysqli_prepare($con, $sqlSelect)) {
            mysqli_stmt_bind_param($stmt, "s", $userEmail);
            mysqli_stmt_execute($stmt);
            mysqli_stmt_bind_result($stmt, $nameChangedDb);
            mysqli_stmt_fetch($stmt);
            mysqli_stmt_close($stmt);

            $allowChange = false;
            if ($nameChangedDb === '0000-00-00' || $nameChangedDb === null) {
                // 首次修改，允许
                $allowChange = true;
            } else {
                // 计算距今天数
                $sqlDiff = "SELECT DATEDIFF(CURDATE(), ?) AS days_since_last_change";
                if ($stmtDiff = mysqli_prepare($con, $sqlDiff)) {
                    mysqli_stmt_bind_param($stmtDiff, "s", $nameChangedDb);
                    mysqli_stmt_execute($stmtDiff);
                    mysqli_stmt_bind_result($stmtDiff, $daysSinceLastChange);
                    mysqli_stmt_fetch($stmtDiff);
                    mysqli_stmt_close($stmtDiff);

                    // 设定限制：30天
                    $monthlyLimitDays = 30;
                    if ($daysSinceLastChange >= $monthlyLimitDays) {
                        $allowChange = true;
                    } else {
                        $errors[] = "您只能每月修改一次姓名。距离上次修改还有 " . ($monthlyLimitDays - $daysSinceLastChange) . " 天。";
                    }
                } else {
                    $errors[] = "数据库查询日期差失败。";
                }
            }

            // 3. 执行更新操作
            if ($allowChange) {
                $sqlUpdate = "UPDATE user_db SET name = ?, name_changed = CURDATE() WHERE email = ?";
                if ($stmtUpdate = mysqli_prepare($con, $sqlUpdate)) {
                    mysqli_stmt_bind_param($stmtUpdate, "ss", $newName, $userEmail);
                    if (mysqli_stmt_execute($stmtUpdate)) {
                        // 更新成功，可以重定向或显示成功消息
                        $_SESSION['message'] = "姓名修改成功！";
                        header("location: profile.php"); // 重定向到用户资料页
                        exit();
                    } else {
                        $errors[] = "姓名更新失败: " . mysqli_error($con);
                    }
                    mysqli_stmt_close($stmtUpdate);
                } else {
                    $errors[] = "数据库更新语句准备失败: " . mysqli_error($con);
                }
            }
        } else {
            $errors[] = "数据库查询语句准备失败: " . mysqli_error($con);
        }
    }
}

// 显示错误信息
if (!empty($errors)) {
    foreach ($errors as $error) {
        echo "<p style='color: red;'>错误: " . htmlspecialchars($error) . "</p>";
    }
}

// 关闭数据库连接
mysqli_close($con);
?>