Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Cleaning Up Ghost Entries in MySQL on PHP Session Destroy/End

Posted on 2007-10-19
15
Medium Priority
?
358 Views
Last Modified: 2013-12-13
I'm working on a chatroom that uses PHP and MySQL and PHP Sessions in the backend.

It works great except for a side effect of HTTP connectionless nature.

Lets use this table: "Users_In_Chatroom"
ChatroomId (int), UserId (int), DateJoined (datetime)

When a user enters the room:
$insert_query=sprintf("INSERT INTO Users_In_Chatroom(ChatroomId, UserId, DateJoined) values(%s, %s, '%s')",
                        $_POST['RoomNumber'],
                        $_SESSION['UserId'],
                        date("Y-m-d H:i:s"));

When a user leaves the room
$delete_query=sprintf("DELETE FROM Users_In_Chatroom where ChatroomId=%s AND UserId=%s",
                        $_SESSION['RoomId'],
                        $_SESSION['UserId']);


So Far so good.....but when a user simply CLOSES their web browser without purposfully "LEAVE"ing the chatroom, they are left sitting there, forever.

I'd thought about some onClose() javascript, but if the browser crashes or the internet connection breaks, that code won't execute.

I can have the browser automatically update some field with a datetime that says "I'm still alive" and then respond somehow to (now()-"I'm still alive")>30 minutes, or some timeout, but how would I do that??

I'm on a public server which means no server-side code can be continuously running.

I am however successfully using PHP sessions, that timeout on there own after 30 minutes of inactivity.  Perhaps I can some code that executes on session destroy??

I hope someone can help me with this :) Thanks!!
0
Comment
Question by:dban00b
  • 8
  • 7
15 Comments
 
LVL 21

Expert Comment

by:nizsmo
ID: 20113091
Here is an idea:

You can have an extra field in your database table, called say lastAlive and stores a timestamp.

You can modify your code so that every 30min some php code executes to update this lastAlive field to the current time using the now() function my mysql.

If the user closes the browser, the lastAtlive field will be left unupdated, then upon the user logging in again next time, you would clean up by deleting all entries with lastAlive which is greater than 30minutes ago.

I hope you get the general idea of what I am trying to propose.
0
 
LVL 1

Author Comment

by:dban00b
ID: 20113484
Well....that's on the right track, but the real problem I want to address, a user being show as in a chatroom when they are NOT there, still remains.

The lastActive timestamp is easily done.  But If a user gets disconnected, and decides not to reconnect for say, a week....during that whole week the user is still shown in the chatroom(s)

Now I can have each LIVE user's actions run a cleanup.....but that would slow down everything.

function cleanup(){
      $now=time();
      
      $inactive_users_query="SELECT UserId from Site_Users WHERE lastActive<'".gmdate("Y-m-d H:i:s")."'";
      $inactive_users_result = mysql_query($inactive_users_query, $mysqllink) or die(mysql_error());
      $inactive_users_count=$mysql_num_rows($inactive_users_result);
      $inactive_users_list="";
      
      for($i=0;$i<$inactive_users_count;$i++){
            if($i>0) $inactive_user_list.=",";
            $result_row=mysql_fetch_assoc($inactive_users_result);
            $inactive_user_list.="".$result_row['UserId'];
      }
      $cleanup_query="DELETE FROM Users_In_Chatroom WHERE UserId IN(".$inactive_user_list.")";
      $cleanup_result = mysql_query($cleaup_query,$mysqllink) or die(mysql_error());
      
      
}

The trick is getting this cleanup() cunction to execute often enough to be of use, but not so often its actually inefficient. (ie: alongside every chat message would be ridiculous)

I'd say 30 minutes is not unreasonable for a user to be "stuck" in the system. But they can't stay there until the next time they login, what if the user never logs in again?

Thanks for your help so far, we're on the right track.
0
 
LVL 1

Author Comment

by:dban00b
ID: 20113487
Imagine that select query modified for LastActive+30 minutes<gmdate();
.......as I wrote it, everyone would ALWAYS get kicked off :)...but you get what I meant
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 21

Expert Comment

by:nizsmo
ID: 20113501
Yes I see the problem. I assume you wrote your own chatroom am i correct?

If so, how easy/difficult would it be to check the timestamp at the same time as when you are checking the session variable to see who is in the chatroom if you know what I mean? So where your chatroom code checks for the session variable of the person logged in, it also checks for the timestamp and marks the user inactive and also deletes the session variable when it detects this.

I'm not too sure if this is feasable?
0
 
LVL 1

Author Comment

by:dban00b
ID: 20114855
An AJAX object is constantly requesting all new messages since the last grabbed Message Timestamp, as well as a list of people in the room. These are PHP pages that output results of mysql queries.  There is no "active" program because I'm on a public server and can't run a custom server-side program.  Finding out what new messages there are and  what people have entered the room are not "pushed", they are only given to the client-side browser when the browser tries to "pull" the data.


It IS possible to make these PHP pages that output mysql results to run the cleanup function everytime they are requested, but I really think there's got to be a better way......
0
 
LVL 21

Expert Comment

by:nizsmo
ID: 20115828
You could use javascript to detect people closing the chat window, and have a ajax script which dynamically queries the database and deletes the session variable entries off the database.

The only downfall with this is that you have to detect the person has javascript!

Here is some useful information on this, I guess it is a more efficient way than executing some script every minute...:

http://www.codingforums.com/showthread.php?t=37279&page=2

Let me know how you go.
0
 
LVL 1

Author Comment

by:dban00b
ID: 20116082
That indeed is what I'll have to do until I can find a more elegant idea, I mentioned the javascript option in my original question but was looking for another solution because the javascript method has three major pitfalls:
1)If the user disables javascript it won't work
2)If the user's browser crashes, the javascript won't run
3)If the user's internet connection breaks, the AJAX script won't be able to communicate with the server



0
 
LVL 21

Accepted Solution

by:
nizsmo earned 2000 total points
ID: 20116163
Ahh I see sorry for proposing a repeated solution.

Do you think you can utlilze session_cache_expire() function in PHP?

I think it would be quite suitable, however you may have to modify how your code works a little bit in order to cater for different session variable handling:

http://nz2.php.net/manual/en/function.session-cache-expire.php

Let me know.
0
 
LVL 1

Author Comment

by:dban00b
ID: 20116277
Indeed I currently use that function to make sessions expire in 30 minutes, I would love it if I could get something to run when a cache expires!! Then that cleaup() function I wrote could even be simpler, it would merely take care of the user who expired.....But I have no clue how to....

0
 
LVL 1

Author Comment

by:dban00b
ID: 20131810
I tried rewording the question more specifically only to get an unsavory response

http://www.experts-exchange.com/Web_Development/Web_Languages-Standards/PHP/Q_22910296.html

If we could solve this, I think we'll be millionaires.  I can't be the only person who doesn't have access to server-side executables that wants the server to be responsive...
0
 
LVL 21

Expert Comment

by:nizsmo
ID: 20133166
I think the best way has been described already in this opened question, it is not a pretty solution, but it is a solution. as the user has closed their browser window all client-side scripts will FAIL, so you rely on server-side scripting by polling of some kind to detect whether a user is active or not.
0
 
LVL 1

Author Comment

by:dban00b
ID: 20135867
I'm thinking that I could make a stupid little MySQL table Cleanup, with one field, LastCleanupTimeStamp,
and on the "new message" php file that a user accesses, look at LastCleanup, if >30 minutes, run the cleanup() function, which in addition to my previous cleanup code will update, the LastCleanupTimeStamp.  Then only once a half hour, only one user gets a little hiccup.

It still won't work if there happen to be ZERO users online, but if there are no users online, who cares how sloppy things are!? :)

I'll work on this and see if it works.
0
 
LVL 21

Expert Comment

by:nizsmo
ID: 20135957
Its a good idea, still relies on the logic of polling for users using the last cleanup, however I don't see anything wrong with it if it works!
0
 
LVL 1

Author Comment

by:dban00b
ID: 20150205
YAY victory!, slight change, I have it setup whenever a user joins a room, it issues a cleanup on that room, I think it's a good balance, thanks for your help.
0
 
LVL 21

Expert Comment

by:nizsmo
ID: 20150273
Well done, for sure I will probably implement this in the future so thanks for sharing! And sorry I could not assist you furthermore.
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article discusses how to implement server side field validation and display customized error messages to the client.
In this article, we’ll look at how to deploy ProxySQL.
Learn how to match and substitute tagged data using PHP regular expressions. Demonstrated on Windows 7, but also applies to other operating systems. Demonstrated technique applies to PHP (all versions) and Firefox, but very similar techniques will w…
The viewer will learn how to dynamically set the form action using jQuery.
Suggested Courses

564 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