Query to check if entry exists within 5 minutes

Question

I cannot get this query to work properly. When I submit the form, it is supposed to check and see if the email address has already been priorly submitted to the table within 5 minutes.

If it has, it needs to skip the if statement that writes to that table.

$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) {}

However it won't write anything to the table the way it's set up.

I tried using (mysql_num_rows($db_check) > 0); but then it kept writing regardless.

+-------------+---------------+---------------------+---------------------+
| 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.

Am I doing something wrong?

Answer 1

Consider the following...

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)

Some minutes later...

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)

Answer 2

For debugging this, output the actual SQL text you are submitting to the database. Do this in two separate steps. One step to dynamically prepare the SQL text into a single string variable, and a second step to submit that to the database. Between those two steps, echo or vardump the string containing the SQL statement, so you can see what's being submitted to the database, and take it to another client to test.

$sql = " SELECT ... " . $some_variable . " ... ";
echo "SQL=" . $sql;   //for debugging

We suspect that the SQL statement that you are submitting to the database isn't doing what you think it's doing. From the code shown, we don't have a clue what the variable $h_date_time contains.

For all we know it could contain something like:

$h_date_time = "1999-01-01' OR NOW() > '1999-01-01"; 

Or

$h_date_time = "0"; 

How in the plastic is anyone going to debug an issue with a SQL statement when they don't have the actual SQL statement that's being submitted to the database?

---

If you have to supply a values in the SQL statement, a better pattern is to use prepared statement with bind placeholders. If you supply potentially unsafe values in the actual SQL text (not through a bind placeholder), those values must be properly escaped (e.g. mysqli_real_escape_string.)

Answer 3

$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`  >= DATE_SUB(NOW(), INTERVAL 5 MINUTE))
AND (`User_Email` = '$h_user_email')

");

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

You can replace NOW() with your date variable too.

Just an FYI, mysql_* functions have been deprecated now. Please considering using Mysqli_* or PDO to use prepared statements.

Let me know if the above code works.