I am trying to grab the newest records from my table. I want all of the records that happened in the past 7 days. Here is what I have so far to start with.
$query = "SELECT * FROM mlg_logattempts AS a WHERE a.ts = (SELECT MAX(ts) FROM mlg_logattempts AS b WHERE b.ts > NOW() - INTERVAL 5 MINUTE AND b.name = a.name)";
I have used intervals in the past but an unsure how to make this work now. Can someone show me the proper way to request the past 7 days records? I do have a timestamp field.
UPDATE
Unfortunately I realized the command I shared with you. I do not have any of the above fields. The only date field I have is "date". no a or ts.
You could use mysql date_diff() for dates http://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html#function_datediff
but since you use timestamps, the interval is a good solution:
b.ts > unix_timestamp(CURDATE()-INTERVAL 7 DAY)
Supposing that the date of login attempt is b.ts and it's formatted like 2013-08-20 03:08:
$past7days = date("Y-m-d H:i:s",strtotime("-7day"));
$query = "SELECT * FROM mlg_logattempts AS a WHERE a.ts = (SELECT MAX(ts) FROM mlg_logattempts AS b WHERE date >= '$past7days' AND b.name = a.name)";