mysql - 查询5分钟内是否存在条目

Question

我无法让这个查询正常工作。当我提交表单时，它应该检查电子邮件地址是否已在 5 分钟内预先提交到表格中。

如果有，则需要跳过写入该表的 if 语句。

$db_check = mysql_query("

   SELECT Send_Status, User_Email, Usage_Date_Time 
   FROM Email_Blast 
   WHERE (Send_Status = '0' OR Send_Status = '1')
   AND (Usage_Date_Time  >= '$h_date_time' - INTERVAL 5 MINUTE)
   AND (User_Email = '$h_user_email')

");

if (mysql_num_rows($db_check) == 0) {}

但是，它不会按照设置的方式向表中写入任何内容。

我尝试使用(mysql_num_rows($db_check) > 0);但不管怎样它还是继续写。

+-------------+---------------+---------------------+---------------------+
| Send_Status | Member_Status | User_Email          | Usage_Date_Time     |
+-------------+---------------+---------------------+---------------------+
| 0           | 0             | user@domain.tld     | 2015-05-25 16:45:55 |
| 0           | 0             | name@domain.tld     | 2015-05-25 16:44:42 |
| 0           | 1             | jeff@domain.tld     | 2015-05-25 16:16:34 |
| 0           | 1             | john@gmail.com      | 2015-05-25 16:15:09 |
| 0           | 0             | peter@hotmail.com   | 2015-05-25 16:13:04 |
| 0           | 1             | server@domain.tld   | 2015-05-25 16:11:48 |
+-------------+---------------+---------------------+---------------------+

<小时/>

I am using the $h_date_time = date('y-m-d H:i:s', time()); to match the exact time zone of the entries in the database.

我做错了什么吗？

Answer 1

考虑以下...

DROP TABLE IF EXISTS email_blast;

CREATE TABLE email_blast
(Send_Status TINYINT NOT NULL DEFAULT 0
,Member_Status TINYINT NOT NULL DEFAULT 0
,User_Email VARCHAR(30) NOT NULL
,Usage_Date_Time DATETIME NOT NULL
,PRIMARY KEY(user_email,usage_date_time)
);

INSERT INTO email_blast VALUES
(0,0,'user@domain.tld','2015-05-25 16:45:55'),
(0,0,'name@domain.tld','2015-05-25 16:44:42'),
(0,1,'jeff@domain.tld','2015-05-25 16:16:34'),
(0,1,'john@gmail.com','2015-05-25 16:15:09'),
(0,0,'peter@hotmail.com','2015-05-25 16:13:04'),
(0,1,'server@domain.tld','2015-05-25 16:11:48');

SELECT * FROM email_blast;
+-------------+---------------+-------------------+---------------------+
| Send_Status | Member_Status | User_Email        | Usage_Date_Time     |
+-------------+---------------+-------------------+---------------------+
|           0 |             1 | jeff@domain.tld   | 2015-05-25 16:16:34 |
|           0 |             1 | john@gmail.com    | 2015-05-25 16:15:09 |
|           0 |             0 | name@domain.tld   | 2015-05-25 16:44:42 |
|           0 |             0 | peter@hotmail.com | 2015-05-25 16:13:04 |
|           0 |             1 | server@domain.tld | 2015-05-25 16:11:48 |
|           0 |             0 | user@domain.tld   | 2015-05-25 16:45:55 |
+-------------+---------------+-------------------+---------------------+

SELECT NOW();
+---------------------+
| NOW()               |
+---------------------+
| 2015-05-25 16:42:03 |
+---------------------+

INSERT INTO email_blast 
(Send_Status
,member_status
,user_email
,usage_date_time
)
SELECT 0
     , 1
     , 'user@domain.tld'
     , NOW() 
  FROM email_blast x 
  LEFT JOIN email_blast y 
    ON y.user_email = 'user@domain.tld' 
   AND y.send_status IN (0,1)
   AND y.usage_date_time > NOW() - INTERVAL 5 MINUTE 
 WHERE y.user_email IS NULL 
 LIMIT 1;
Query OK, 0 rows affected (0.01 sec)

SELECT * FROM email_blast;
+-------------+---------------+-------------------+---------------------+
| Send_Status | Member_Status | User_Email        | Usage_Date_Time     |
+-------------+---------------+-------------------+---------------------+
|           0 |             1 | jeff@domain.tld   | 2015-05-25 16:16:34 |
|           0 |             1 | john@gmail.com    | 2015-05-25 16:15:09 |
|           0 |             0 | name@domain.tld   | 2015-05-25 16:44:42 |
|           0 |             0 | peter@hotmail.com | 2015-05-25 16:13:04 |
|           0 |             1 | server@domain.tld | 2015-05-25 16:11:48 |
|           0 |             0 | user@domain.tld   | 2015-05-25 16:45:55 |
+-------------+---------------+-------------------+---------------------+
6 rows in set (0.00 sec)

几分钟后...

SELECT NOW();
+---------------------+
| NOW()               |
+---------------------+
| 2015-05-25 17:03:17 |
+---------------------+
1 row in set (0.00 sec)

INSERT INTO email_blast
(Send_Status
,member_status
,user_email
,usage_date_time
)
SELECT 0
     , 1
     , 'user@domain.tld'
     , NOW()
  FROM email_blast x
  LEFT JOIN email_blast y
    ON y.user_email = 'user@domain.tld'
   AND y.send_status IN (0,1)
   AND y.usage_date_time > NOW() - INTERVAL 5 MINUTE
 WHERE y.user_email IS NULL
 LIMIT 1;

SELECT * FROM email_blast;
+-------------+---------------+-------------------+---------------------+
| Send_Status | Member_Status | User_Email        | Usage_Date_Time     |
+-------------+---------------+-------------------+---------------------+
|           0 |             1 | jeff@domain.tld   | 2015-05-25 16:16:34 |
|           0 |             1 | john@gmail.com    | 2015-05-25 16:15:09 |
|           0 |             0 | name@domain.tld   | 2015-05-25 16:44:42 |
|           0 |             0 | peter@hotmail.com | 2015-05-25 16:13:04 |
|           0 |             1 | server@domain.tld | 2015-05-25 16:11:48 |
|           0 |             0 | user@domain.tld   | 2015-05-25 16:45:55 |
|           0 |             1 | user@domain.tld   | 2015-05-25 17:03:33 |
+-------------+---------------+-------------------+---------------------+
7 rows in set (0.00 sec)