jeudi 30 juin 2016

How can I select all rows which have been inserted in the last day?

I have a table like this:

// reset_password_emails
+----+----------+--------------------+-------------+
| id | id_user  |        token       | unix_time   |
+----+----------+--------------------+-------------+
| 1  | 2353     | 0c274nhdc62b9dc... | 1339412843  |
| 2  | 2353     | 0934jkf34098joi... | 1339412864  |
| 3  | 5462     | 3408ujf34o9gfvr... | 1339412894  |
| 4  | 3422     | 2309jrgv0435gff... | 1339412899  |
| 5  | 3422     | 34oihfc3lpot4gv... | 1339412906  |
| 6  | 2353     | 3498hfjp34gv4r3... | 1339412906  |
| 16 | 2353     | asdf3rf3409kv39... | 1466272801  |
| 7  | 7785     | 123dcoj34f43kie... | 1339412951  |
| 9  | 5462     | 3fcewloui493e4r... | 1339413621  |
| 13 | 8007     | 56gvb45cf3454g3... | 1339424860  |
| 14 | 7785     | vg4er5y2f4f45v4... | 1339424822  |
+----+----------+--------------------+-------------+

Each row is an email. Now I'm trying to implement a limitation for sending-reset-password email. I mean an user can achieve 3 emails per day (not more).

So I need an query to check user's history for the number of emails:

SELECT count(1) FROM reset_password_emails WHERE token = :token AND {from not until last day}

How can I implement this:

. . . {from now until last day}

Actually I can do that like: NOW() <= (unix_time + 86400) .. But I guess there is a better approach by using interval. Can anybody tell me what's that?

Aucun commentaire:

Enregistrer un commentaire