Solved

mySQL Managing a login table

Posted on 2011-03-14
6
271 Views
Last Modified: 2012-05-11
Hi,

I'm building a scheduled task in mySQL that will, every night, run through a table of users who are logged into the system and automatically logoff anyone who's been logged in for 2 weeks.

My current draft runs through the Login table, identifying the users logged in for over 2 weeks and puts their details into a temp table. I then loop through the temp table running the stored proc that does the log-off for each temp table entry.

Can anyone think of a simpler way of doing this? Especially one that doesn't involve using a loop?

Thanks,
John.
0
Comment
Question by:GroganJ
  • 3
  • 3
6 Comments
 
LVL 3

Expert Comment

by:sbickerstaff
ID: 35130310
Is the app using PHP? if so, why not use sessions and set the expiry time to two weeks?
0
 

Author Comment

by:GroganJ
ID: 35130515
I thought about using session timeout, but there are a couple of other events in the DB that are triggered by a user logout. For that reason, I want to control / track it within the DB rather than from php.
0
 
LVL 3

Expert Comment

by:sbickerstaff
ID: 35130622
how are you maintaining / checking that someone is logged in?

1) are you setting the last login time when they log in?
2) have you a field to set that user is active each time they visit a page or do you just use last login time? or how do you check if user is active?

as someone can close the browser without logging out, then obviously they're not active anymore, so you could be forcing a log off that is an un-necessary overhead.

can you give an example of these 'other events' triggered by a log out?
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 

Author Comment

by:GroganJ
ID: 35130670
I am tracking when they login and when there is activity. After a predetermined period of inactivity, I want to log them out (for security reasons). At the moment, this is set to 2 weeks, but if when we change that, it's very easy to just update a stored proc and let it run.

The other events that get triggered don't really matter, but it's important that they get run when a user logs out (or is logged out by the system).
0
 
LVL 3

Accepted Solution

by:
sbickerstaff earned 500 total points
ID: 35130770
From what you've been saying then, in my opinion, whats you're doing at the minute sounds like the only way with one possible ammendment:

instead of putting all records out into a temp table and then processing them again, why not just process these records on the first loop through the database?
0
 

Author Closing Comment

by:GroganJ
ID: 35176723
Thanks for your input on this.
0

Featured Post

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Join & Write a Comment

More Fun with XML and MySQL – Parsing Delimited String with a Single SQL Statement Are you ready for another of my SQL tidbits?  Hopefully so, as in this adventure, I will be covering a topic that comes up a lot which is parsing a comma (or other…
As a database administrator, you may need to audit your table(s) to determine whether the data types are optimal for your real-world data needs.  This Article is intended to be a resource for such a task. Preface The other day, I was involved …
This video gives you a great overview about bandwidth monitoring with SNMP and WMI with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're looking for how to monitor bandwidth using netflow or packet s…
You have products, that come in variants and want to set different prices for them? Watch this micro tutorial that describes how to configure prices for Magento super attributes. Assigning simple products to configurable: We assigned simple products…

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

24 Experts available now in Live!

Get 1:1 Help Now