php - MySQL查询，比较日期时间

Question

在我的页面中，用户可以使用电子邮件注册并订阅新闻。我将电子邮件、IP 和时间戳放入我的数据库中。我想确保一个 IP 每天只能收到 5 封电子邮件，并且我不希望我的数据库中有相同的电子邮件地址。

我试过了，但没用，我想发多少就发多少。

if(isset($_POST["send"]))
{
    $email = test_input($_POST['email']);
    if (!filter_var($email, FILTER_VALIDATE_EMAIL) === false) {
    $query = 'INSERT INTO subscriber (data, ip, email) (SELECT CURRENT_TIMESTAMP, "'.$_SERVER['REMOTE_ADDR'].'", "'.$email.'" FROM dual where NOT EXISTS ( Select count(email) as c  FROM (
    SELECT email FROM subscriber  WHERE ip="'.$_SERVER['REMOTE_ADDR'].'" and DATE(`data`) = CURDATE() group by email) as a having c>5))';
    $result =  mysqli_query($dbc, $query) or die(mysqli_error($dbc));
    $msg="E-mail sent successfully";
} else
{
    $msg="Wrong e-mail address";
}

而不是写相同的电子邮件，我应该在插入后使用类似的东西吗？

来自双 哪里不存在( 从订阅者中选择 * WHERE email="'.$email.'" )';

Answer 1

你为什么不把查询分开。首先检查

SELECT count(id) FROM subscriber  
WHERE ip="'.$_SERVER['REMOTE_ADDR'].'" 
and DATE(`data`) = CURDATE()

如果结果 < 5 则执行插入。它将简单易读。

完整代码:

if(isset($_POST["send"])){

$email = test_input($_POST['email']);
if (!filter_var($email, FILTER_VALIDATE_EMAIL) === false) {
    $query = 'SELECT count(id) FROM subscriber  
              WHERE ip="'.$_SERVER['REMOTE_ADDR'].'" 
              and DATE(`data`) = CURDATE()';
    $result =  mysqli_query($dbc, $query);
    $count  = mysqli_fetch_field($result);

    if($count < 5) {
        $query = "SELECT id FROM subscriber  
                  WHERE email='".$email."'
                  LIMIT 1";
        $result =  mysqli_query($dbc, $query);
        $mailExist = mysqli_num_rows($result);

        if($mailExist > 0) {
            $msg="E-mail is already existing in the DB";
        }
        else {
            $query = "INSERT INTO subscriber (data, ip, email) VALUES (CURDATE(), '".$_SERVER['REMOTE_ADDR']."', '".$email."')";
            mysqli_query($dbc, $query);
            $msg="E-mail sent successfully";
        }
    }
    else {
        $msg="5 emails today";
    }
}
else {
    $msg="Wrong e-mail address";
}

如果你在数据库中的这个字段上放置索引会很好。但我会建议你使用 PDO 而不是 mysqli。您可以在这里阅读更多相关信息 http://php.net/manual/en/book.pdo.php