Combine PHP session and MySQL for query login state

Question

My compeny current PHP website has users that are logging in using session. Keeping a field in the session $_SESSION['user_id'] and when logging out unset this field. The user data like Name, Address, Balance is saved in MySQL user table. Now I want to create a query that returns all the logged in user and Balance is over 500$.

How would you approach such task?

Consider that I have a lot of users so looping through all the sessions in session folder and than querying the DB and than matching the results in not really a possibility.

Second option is saving user login state in the user table. setting it to 1 when user log in and to 0 when log out. This is the simplest option to do with current code base and the company bureaucracy. But I can think problem with synchronization especially if the session expire

Third option is to transfer all the responsibility of the session to the DB with something like session_set_save_handler.

What do you think is the best practice?

Add a new field last_login (I believe it's more useful than just a boolean - logged or not field) and then you can define a "buffer time", let's say: Logged = last_login (unix timestamp) + 60*5 (5 minutes). Now you can use a simple SQL query. — Ofir Baruch
– Ofir Baruch, Commented May 12, 2015 at 8:15

Captain Crunch · Accepted Answer · 2015-05-12 08:40:47Z

(I'd like to add to what @Ofir_Baruch said, for avoiding multiple calls to the DB in order to update last user's loggin all the time)

Add a time-stamp "last login",in:

user's table in DB (lets call it: DB's time-stamp)
in user's session (lets call it: session's time-stamp)

(lets say a session lasts 15 minutes for example) Add this concept when you check if user's session is valid:

(pseudo code)

when user request a page:
    if session[user] is not valid:
        create new session
        session[user] = username
        session[last-login] = time-stamp
        update user's last login column in DB to current time-stamp
    else
        if ( current_time_stamp - session[last-login] > 15 )
             session[last-login] = time-stamp
             update user's last login column in DB to current time-stamp
        else
            do_nothing

this way, you don't have to update the DB's time-stamp each time your user does something (like requesting a page or refreshing), but only if 15 minutes have passed.

getting the all logged user's will be a simple query now, as @Ofir_Baruch described in comment.

Collectives™ on Stack Overflow

Combine PHP session and MySQL for query login state

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related