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?

I can't run it, cause I am using `$h_date_time` to get the time with correct timezone that matches the entries in the database. If I use `now()` it will be incorrect... @AbhikChakraborty — Nikk, May 25 '15 at 14:30
@9997 I get all the entries in the DB, when it should show `0`; — Nikk, May 25 '15 at 14:33
@Boris check out this answer: http://stackoverflow.com/questions/3401551/mysql-interval-mins You need to restructure your query. — 9997, May 25 '15 at 14:36
I think it should work better; also look at the top comment and what other comments he's made. I think if you restructure your query, it would work perfectly. — 9997, May 25 '15 at 14:38
The **mysql** interface is *deprecated*. New development should use either **mysqli** or **PDO**. Potentially unsafe values included in SQL text *must* be properly escaped. A better pattern is to use a **prepared statement** with **bind placeholders**. And your code needs to check the return from database interface calls, rather than putting it's pinky finger to the corner of its mouth, Dr. Evil style, and saying "I'm just gonna assume it all went to plan. What?" — spencer7593, May 25 '15 at 15:14
@spencer7593 I am aware it is. This is for a school project; — Nikk, May 25 '15 at 15:16
If you *know* the dynamically generated SQL statement is working perfectly, then disregard the recommendations in my answer for 1) avoiding dynamically generated SQL, 2) patterns for debugging dynamically generated SQL. While you "know" what statement is being submitted to the database, no one else looking at your question can "know" what SQL statement is being submitted. And if you know the statement is working perfectly, then obviously the SQL statement isn't causing the problem. The problem is in some other code that isn't shown. — spencer7593, May 25 '15 at 15:24

score 1 · Answer 1 · answered May 25 '15 at 16:05

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)

I'm just wondering if it wouldn't it be more efficent to replace `FROM email_blast x` (in the last statement) with a reference to a table or inline view that returns one row. e.g. `FROM (SELECT 1) x`. (I'm not sure how MySQL processes that exactly, but the LIMIT 1 gets evaluated *last* in the execution plan; if `email_blast` is a large table, that may be (unnecessarily) materializing a very large intermediate resultset. I'd be inclined to write `SELECT x.* FROM (SELECT 0 AS send_status, 1 as member_status, 'user@domain.tld' AS user_email, ...) x` — spencer7593, May 25 '15 at 17:04
aside from that one minor quibble with performance (and the obscure corner case where `email_blast` table is empty), this is an approach I've successfully used numerous times. With a `UNION ALL` in the inline view, I can generate multiple rows, and then I can reference columns returned by the inline view in the join predicate. e.g. rather than a literal reference the column, i.e instead of **`AND y.email = 'literal'`** we can use **`AND y.email = x.email`**. That's what I've done before. — spencer7593, May 25 '15 at 18:20

score 0 · Answer 2 · answered May 25 '15 at 14:41

0

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

answered May 25 '15 at 14:41

9997

1,187
1
9
14

Doesn't work...if the entry has been added it shouldn't add it again in the next five minutes. But it does. – Nikk May 25 '15 at 14:47
@Boris is your query still displaying everything? Could you maybe edit your question with your table structure? I can try and reproduce it then. – 9997 May 25 '15 at 14:49
Added it, check my question – Nikk May 25 '15 at 14:57
As helpful(?) as this code suggestion might be, this doesn't explain what the issue is in OP code, or how a re-write of a predicate in the query "solves" the issue. But **kudos** for pointing out that both mysqli and PDO interfaces support **prepared statement** with **bind placeholders**, which the deprecated mysql interface doesn't. – spencer7593 May 25 '15 at 15:29

spencer7593 · Answer 3 · 2015-05-25T15:31:44.343

0

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

edited May 25 '15 at 15:31

answered May 25 '15 at 14:54

spencer7593

106,611
15
112
140

It contains the following: `$h_date_time = date('y-m-d H:i:s', time());` – Nikk May 25 '15 at 15:14
Thats not the problem, as I use it for another time based query...it works perfectly – Nikk May 25 '15 at 15:15
If you *know* the dynamically generated SQL statement is working perfectly, then disregard the recommendations in my answer for 1) avoiding dynamically generated SQL, 2) patterns for debugging dynamically generated SQL. While you "know" what statement is being submitted to the database, no one else looking at your question can "know" what SQL statement is being submitted. And if you know the statement is working perfectly, then obviously the SQL statement isn't causing the problem. The problem is in some other code that isn't shown. – spencer7593 May 25 '15 at 15:24
One of the most *critically important* skills you need to learn in programming is **how to debug a program**. Posting code that isn't working on StackOverflow, and asking why it isn't working is *not* a substitute for learning that skill. – spencer7593 May 25 '15 at 15:52

Query to check if entry exists within 5 minutes

3 Answers3