Get authorized user now mysql

Question

I have table structure:

I need get last authorized user with status 'Signed in' without last status 'Signed off'. How I can do it?

I have a query:

SELECT * FROM vtiger_loginhistory WHERE status = 'Signed in' ORDER BY login_time DESC LIMIT 1

But this get me last authorized user admin, why? If he is Signed off. I need get Igor, because he is Signed in last.

Can you please provide an example of what should be the output manually? — Harshwardhan Sharma, Feb 27 '19 at 09:47

score 0 · Answer 1 · answered Feb 27 '19 at 08:47

0

You could check for username that is not IN the list for signed off too

SELECT * 
FROM vtiger_loginhistory 
WHERE status = 'Signed in' 
AND username NOT IN (
  select username 
  from vtiger_loginhistory 
  where  status = 'Signed OFF' 
)
ORDER BY login_time DESC LIMIT 1

answered Feb 27 '19 at 08:47

ScaisEdge

131,976
10
91
107

This is not working :(. Igor have a `Singed off` status down... I need get singed in only last and without status `singed off` – Dronaxer Feb 27 '19 at 09:07

Zaynul Abadin Tuhin · Answer 2 · 2019-02-27T09:23:01.753

0

you can try below way by using not exists

 select t1.* from vtiger_loginhistory t1
 where 
  status = 'Signed in'
 and not exists ( select 1 from vtiger_loginhistory t2 
                  where t2.user_name=t1.user_name 
                  and status = 'Signed off') 
 order by STR_TO_DATE(login_time,'%Y%m%d %h%i') DESC LIMIT 1

i think your login_time column is not datetime

edited Feb 27 '19 at 09:23

answered Feb 27 '19 at 09:01

Zaynul Abadin Tuhin

31,407
5
33
63

No. I don't need pass `admin`) – Dronaxer Feb 27 '19 at 09:05
I get this result: Array ( [login_id] => 285 [user_name] => test [user_ip] => 192.168.88.67 [logout_time] => 0000-00-00 00:00:00 [login_time] => 2019-01-03 17:48:11 [status] => Signed in ) Why I get old singed user? – Dronaxer Feb 27 '19 at 09:15
@Dronaxer does login column datetime or varchar? – Zaynul Abadin Tuhin Feb 27 '19 at 09:19
login_time have a `datetime` column type – Dronaxer Feb 27 '19 at 09:22
Let us [continue this discussion in chat](https://chat.stackoverflow.com/rooms/189113/discussion-between-zaynul-abadin-tuhin-and-dronaxer). – Zaynul Abadin Tuhin Feb 27 '19 at 09:23

Kaddath · Accepted Answer · 2019-02-27T12:37:55.310

0

You can try that: first select more recent row for each username and use JOIN or another SELECT to see if it's a "in" or "off"

EDIT: the concept is the same but corrected thanks to this answer

Version i don't fully understand: https://sqltest.net/#464377

SELECT *
FROM vtiger_loginhistory v_l
LEFT JOIN vtiger_loginhistory tmp
    ON (v_l.user_name = tmp.user_name AND v_l.login_time < tmp.login_time)
WHERE tmp.login_time IS NULL AND v_l.status = 'Signed in'
ORDER BY v_l.login_time DESC LIMIT 1

Similar result with a subquery as I described (seems slower as explained in linked answer): https://sqltest.net/#464385

SELECT *
FROM vtiger_loginhistory v_l
INNER JOIN (SELECT user_name, MAX(tmp.login_time) AS maxlogintime
            FROM vtiger_loginhistory tmp GROUP BY user_name
            ORDER BY login_time DESC) tmp2
    ON (v_l.login_time = tmp2.maxlogintime)
WHERE status = 'Signed in' 
ORDER BY login_time DESC LIMIT 1

EDITED ORIGINAL NOT WORKING VERSION: (because GROUP BY keeps the first row met, so the oldest dates)

SELECT * 
FROM (
    SELECT *
    FROM vtiger_loginhistory 
    GROUP BY user_name
    ORDER BY login_time DESC
) as temp
WHERE status = 'Signed in' 
ORDER BY login_time DESC LIMIT 1

edited Feb 27 '19 at 12:37

answered Feb 27 '19 at 09:16

Kaddath

5,933
1
9
23

I get this result: Array ( [login_id] => 285 [user_name] => test [user_ip] => 192.168.88.67 [logout_time] => 0000-00-00 00:00:00 [login_time] => 2019-01-03 17:48:11 [status] => Signed in ) Why I get old singed user? – Dronaxer Feb 27 '19 at 09:38
this is strange, I included this row into the test and still get "Igor": [see updated](https://sqltest.net/#464078). Is it the last updated version you tried? – Kaddath Feb 27 '19 at 09:44
Noo, see please screenshots, I use: user_name – Dronaxer Feb 27 '19 at 09:57
that's what I say, I used wrong field name, i'm updating the answer and examples – Kaddath Feb 27 '19 at 09:58
answer and examples are updated (it's not supposed to be important if field `login_ip` is missing in the examples) – Kaddath Feb 27 '19 at 10:01
what do you get if you run only inner query like in [this example](https://sqltest.net/#464119)? – Kaddath Feb 27 '19 at 10:04
That's right, it's because `GROUP BY` keeps the first row met for each user_name, keeping the oldest one. I updated the tests to reflect that (oldest dates inserted first) and updated the answer – Kaddath Feb 27 '19 at 12:38
Worked! Thanks! – Dronaxer Feb 27 '19 at 13:20

Ryuk Lee · Answer 4 · 2019-02-27T09:47:34.923

There are many Signed in and Signed off user, the admin has signed off but the last signed in is more recent than Igor.

There are 2 solutions for you :

Renew the flow, when user Signed off just update the status instead of create new record
Create top1signedoff temporary table first and select the other not in top1signedoff

CREATE TEMPORARY TABLE top1signedoff SELECT username FROM vtiger_loginhistory WHERE status = 'Signed off' ORDER BY login_time DESC LIMIT 1;

SELECT * FROM vtiger_loginhistory
WHERE status = 'Signed in' and username Not IN ( SELECT username FROM top1signedoff )
ORDER BY login_time DESC LIMIT 1

score 0 · Answer 5 · answered Feb 27 '19 at 12:17

Following query should give you the desired result

SELECT t.login_id,t.user_name, t.logout_time,t.login_time,t.status
FROM vtiger_loginhistory t
WHERE t.login_id = (
            SELECT MAX(t2.login_id)
            FROM vtiger_loginhistory t2
            WHERE t2.user_name = t.user_name
            GROUP BY t2.user_name
        )
    AND t.status = 'Signed in';

What we are doing here..

in sub-query we are fetching last login_id for user

and in main query we are checking if the status for that login_id is 'Signed in'

Get authorized user now mysql

5 Answers5