CODEWITHSUNDEEP
phpmysql
mattew
mattew

Reputation: 203

Showing in a table the last different users

I'm working on a MySQL database like this:

 ----------------------------------------------
 | ID |  Time  |    Typeop     |   Operator   |
 ----------------------------------------------
 |  1 |  10:01 |  withdrawal   |     John     |
 |  2 |  10:01 |  deposit      |     Mike     |
 |  3 |  10:01 |  deposit      |     Andrew   |
 |  4 |  10:02 |  check        |     John     |
 |  5 |  10:02 |  withdrawal   |     Simon    |
 |  6 |  10:03 |  withdrawal   |     Dorothy  |

The database is populated by each "operator" during his operations and shows an hypotetic bank operations database.

My question is: How can i organize a mysql query to show the activity of each different operator in the last 3 minutes? In the case of this example the query must show:

 -------------------------
 | Operator |   Typeop   |
 -------------------------
 | Dorothy  | withdrawal |
 |  Simon   | withdrawal |
 |   John   | check      |
 |  Andrew  | deposit    |
 |  Mike    | deposit    |
 -------------------------

Notice that John made two operations in the last 3 minutes but it is considered only the last one. Records in this table are ordered from the last to the older operation within 3-min timeslot.

May I show also a picture at the side of each operator's name? (john.jpg, Simon.jpg...) in the php table generated from the query?

Upvotes: 1

Views: 92

Answers (3)

Shiplu Mokaddim
Shiplu Mokaddim

Reputation: 57650

This query would do it. 

SELECT * 
FROM   (SELECT Operator, 
               Typeop 
        FROM   tbl 
        WHERE  `Time` > ( Now() - INTERVAL 3 minute ) 
        ORDER  BY `Time` DESC) AS `a` 
GROUP  BY `Operator`

Upvotes: 3

Kahlil N
Kahlil N

Reputation: 91

I haven't tested this, but something like this could also work. 

SELECT * FROM tableName WHERE Time BETWEEN (CURTIME() AND ADDTIME(CURTIME(), '00:03:00'))

Upvotes: 0

bipen
bipen

Reputation: 36531

use this... 

Select Operator,Typeop from YourTableName where Time > date_sub(now(), interval 3 minute)  ORDER BY `Time` desc group by Operator;

Upvotes: 0

Related Questions