I have a following table:
+------------+-----------------------------------------------------------------------------------+
| Field | Type |
+------------+-----------------------------------------------------------------------------------+
| id | int(10) unsigned |
| type | enum('REGISTER','ACTIVATE','LOGIN_SUCCESS','LOGIN_FAIL','LOGOUT','LOCK','UNLOCK') |
| user_id | int(10) unsigned |
| mod_id | int(10) unsigned |
| date | timestamp |
| ip | int(10) unsigned |
| user_agent | text |
+------------+-----------------------------------------------------------------------------------+
I am trying to determine, in the simplest possible way (preferably just using MySQL), if there are 3 or more consecutive records with type = LOGIN_FAIL since the last type = LOGIN_SUCCESS or since start of the table.
For example
+----+---------------+---------+--------+---------------------+----+------------+
| id | type | user_id | mod_id | date | ip | user_agent |
+----+---------------+---------+--------+---------------------+----+------------+
| 6 | LOGIN_SUCCESS | 3 | NULL | 2012-07-21 14:08:32 | 0 | agent |
| 7 | LOGIN_FAIL | 3 | NULL | 2012-07-21 14:08:32 | 0 | agent |
| 8 | LOGIN_FAIL | 3 | NULL | 2012-07-21 14:08:32 | 0 | agent |
| 9 | LOGIN_FAIL | 3 | NULL | 2012-07-21 14:08:32 | 0 | agent |
+----+---------------+---------+--------+---------------------+----+------------+
would return TRUE while
+----+---------------+---------+--------+---------------------+----+------------+
| id | type | user_id | mod_id | date | ip | user_agent |
+----+---------------+---------+--------+---------------------+----+------------+
| 6 | LOGIN_FAIL | 3 | NULL | 2012-07-21 14:08:32 | 0 | agent |
| 7 | LOGIN_FAIL | 3 | NULL | 2012-07-21 14:08:32 | 0 | agent |
| 8 | LOGIN_SUCCESS | 3 | NULL | 2012-07-21 14:08:32 | 0 | agent |
| 9 | LOGIN_FAIL | 3 | NULL | 2012-07-21 14:08:32 | 0 | agent |
+----+---------------+---------+--------+---------------------+----+------------+
would return FALSE. Is it possible to do this with a simple query or do I need to implement this check in some script language?
EDIT: I forgot to mention that this query would have to be limited to a certain user_id but I assume this wouldn't be a problem.
Otherwise, or even better, would it be possible to count how much records fit this criteria (i.e. how many consecutive type = LOGIN_FAILED records exist since last type=LOGIN_SUCCESS)