Page 1 of 1

Delete records older than a few minutes?

Posted: Wed Sep 08, 2004 12:15 am
by Stryks
Hi all,

I'm wanting to make a routine which updates a table in the database every time a page view is made so that I can track who is online. I have set this up so far, saving the users id and the time in a datetime field which is set with now().

How would I go about calling entries made in the last, say, 2 minutes?

Also, what would be the best procedure in terms of disposing of this data? Would it be best to delete all entries older than the 2 minutes and then requery to get what is left? Perhaps this could be achieved with one query?

Or would it be best to store all of this data for a set period of say, 1 month, so that it could be used to gather statistics.

Any ideas greatly appreciated.

Posted: Wed Sep 08, 2004 12:41 am
by feyd
generally.. the who's online list is generated by those sessions that were last accessed within X minutes. All sessions older than, say 2 hrs are deleted. This has been talked about before.. plenty.

Posted: Wed Sep 08, 2004 12:47 am
by Stryks
Thanks for the reply feyd, but this isnt really what I'm looking for.

I need it to be very much more accurate than that, within the space of a few minutes.

Then, if I am to use the sessions to do this, how do I do it keeping in mind that I am using a session handler to store my sessions in my database. If I pull this data from there, then I am effectively facing the same problem. How do I get the data from just the last few minutes.

Just a comment for your consideration, telling me that it has been talked about plenty doesnt really help anyone. If I had been able to find something in search then I wouldnt have posted this message. I do appreciate the response though.

Any further assistance would be great.

Posted: Wed Sep 08, 2004 12:49 am
by Stryks
Actually, further to that ... how can I associate a session with a particular user?

Posted: Wed Sep 08, 2004 12:59 am
by feyd

Code: Select all

session_table
------------
session_id (32 or 16 chars for md5's)
session_start (time type or int, the timestamp of when the session was started)
session_last (time type or int, the timestamp of when the session was last accessed) (updated on each page call)
session_ip (8 or 4 chars if ascii encoded, int if numericly encoded)
session_user_id (the exact same type of your user id's)
session_page (optional, whatever you type want, suggested is integer types to reference a pages table)
session_logged_in (optional, tinyint: 1 or 0)
session_data (optional)
  • updating all rows that match the current user_id and current ip with a new timestamp on each page access keeps those users that are browsing alive in the session table.
  • selecting all rows that have a session_last >= now - X minutes ago gives you the who's online list.
  • deleting all rows that have session_last < now - Y minutes/hours/whatever destroys the older sessions that you want to lose.
the update and delete queries are done on every page. The selection is done when the who's online list needs to be shown.

Posted: Wed Sep 08, 2004 1:01 am
by feyd
now would probably be a good time to search the forums for some of the previous topics about sessions and databases...

Posted: Wed Sep 08, 2004 1:16 am
by Stryks
I'm not really sure what to make of your instructions. I'm not sure if you are saying to save that information into the session or to change my session handler to store the data with the session.

This is my session handing code:

Code: Select all

<?php
/* Set up and start session handling using the database */
if (!$SESS_LIFE = get_cfg_var('session.gc_maxlifetime')) {
  $SESS_LIFE = 1440;
}

function _sess_open($save_path, $session_name) {
  return true;
}

function _sess_close() {
  return true;
}

function _sess_read($key) {
  $value_query = mysql_query("select value from Sessions where sesskey = '" . addslashes($key) . "' and expiry > '" . time() . "'")
  or die("Invalid query: " . mysql_error());
  $value = mysql_fetch_array($value_query);

  if (isset($value['value'])) {
    return $value['value'];
  }
    return false;
}

function _sess_write($key, $val) {
  global $SESS_LIFE;

  $expiry = time() + $SESS_LIFE;
  $value = $val;

  $check_query = mysql_query("select count(*) as total from sessions where sesskey = '" . addslashes($key) . "'");
  $check = mysql_fetch_array($check_query);

  if ($check['total'] > 0) {
    return mysql_query("update sessions set expiry = '" . addslashes($expiry) . "', value = '" . addslashes($value) . "' where sesskey = '" . addslashes($key) . "'");
  } else {
    return mysql_query("insert into sessions values ('" . addslashes($key) . "', '" . addslashes($expiry) . "', '" . addslashes($value) . "')");
  }
}

function _sess_destroy($key) {
  return mysql_query("delete from sessions where sesskey = '" . addslashes($key) . "'");
}

function _sess_gc($maxlifetime) {
  mysql_query("delete from sessions where expiry < '" . time() . "'");
  return true;
}

session_set_save_handler('_sess_open', '_sess_close', '_sess_read', '_sess_write', '_sess_destroy', '_sess_gc');
  
/* Start the session */
session_start();

/* If there is SESSION data, break it into friendly variables */
if(count($_SESSION) > 0){ extract($_SESSION, EXTR_SKIP); }
?>
Can you explain how you would go about storing the data you suggest?

Thanks
?>

Posted: Wed Sep 08, 2004 1:31 am
by feyd
your session handler looks fine... the information I was talking about is all stored in the database. to link the user to the session, you'll either need an field in the user table or a field in the session table that references into the other.

Posted: Wed Sep 08, 2004 2:42 am
by Stryks
Nope.

I've tried messing around with all kinds of things and I just cant for the life of me figure out what it is you are telling me I should do.

I'll have to try to find another way.

Thanks for the feedback

Posted: Wed Sep 08, 2004 8:29 am
by Stryks
I cant seem to come up with anything satisfactory. I've searched and searched but I cant seem to come up with anything relevant. Its hard to know what to search for. None of the obvious terms bring up anything anyhow.

Can I get some constructive help here guys?

At the start of each page I am calling that session script, whether the user is logged in or not. The session then contains some information, including the users IP and so on.

So what is it you are suggesting? Inserting the data into the session variable (which doesnt really make sense to me) or creating a table somewhere to link the session to a user, in which case arent I just creating a table separate to the sessions one in which to store the times which was my original idea?

Also ... it doesnt seem to work ... this whole now() - x minutes thing doesnt appear to have the desired effect when applied to a datetime field, and an int field doesnt make sense to me as it doesnt appear mathematically correct.

I'm really at a loss here. Any code or links to threads explaining the process would be a real help.

Thanks

Posted: Wed Sep 08, 2004 12:32 pm
by feyd
viewtopic.php?t=23781

the code there will need some fiddling to match your usage.. but it's an example of what I'm talking about.